mysql

connection

normal

package engine

import (
    "database/sql"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

var (
    dbCon *sql.DB
    err   error
)

func init() {
    dbCon, err = sql.Open("mysql", "username:pwd@tcp(host:port)/database?charset=utf8")
    if err != nil {
        log.Print(err.Error())
    }
}

但是这样硬编码，不仅麻烦还不安全，因此我们需要对代码进行优化，详情见优化后的代码

orm

package initialize

import (
    "fmt"
    "github.com/scott-x/grpc_start/06integrateServiceRegisterServiceFoundToGin/user-srv/global"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
    "log"
    "os"
    "time"
)

func InitDB() {
    var err error
    //配置logger
    //设置全局的logger，这个logger在我们执行每个sql语句的时候会打印每一行sql
    //sql才是最重要的，orm次之；本着这个原则让大家看到没个api背后的sql语句是什么
    newLogger := logger.New(
        log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
        logger.Config{
            SlowThreshold: time.Second, // Slow SQL threshold
            // Log level，有4个级别 （Silent, Error, Warn, Info），当设置为Info时会全部打印
            LogLevel:                  logger.Info,
            IgnoreRecordNotFoundError: true,  // Ignore ErrRecordNotFound error for logger
            Colorful:                  false, // Disable color
        },
    )

    // refer https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
    mysql_info := global.ServerConfig.MysqlInfo

    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
        mysql_info.User, mysql_info.Password, mysql_info.Host, mysql_info.Port, mysql_info.Name)

    global.DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })
    if err != nil {
        panic("failed to connect database")
    }
}

CURD

拿到dbCon就可以对mysql进行CURD操作了

/*
* @Author: apple
* @Date:   2022-11-24 21:55:34
* @Last Modified by:   apple
* @Last Modified time: 2022-11-24 23:20:19
 */
package db

import (
    "github.com/scott-x/mysql_curd_demo/model"
)

func trancate_table_user() error {
    _sql := "truncate table user"
    stmt, err := dbCon.Prepare(_sql)
    if err != nil {
        return err
    }
    defer stmt.Close()
    _, err = stmt.Exec()
    if err != nil {
        return err
    }
    return nil
}

func Insert(username, password string) error {
    _sql := "insert into user(username, password) values(?,?)"
    stmt, err := dbCon.Prepare(_sql)
    if err != nil {
        return err
    }
    defer stmt.Close()
    _, err = stmt.Exec(username, password)
    if err != nil {
        return err
    }
    return nil
}

func DeleteUserById(id int) error {
    _sql := "delete from user where id = ?"
    stmt, err := dbCon.Prepare(_sql)
    if err != nil {
        return err
    }
    defer stmt.Close()
    _, err = stmt.Exec(id)
    if err != nil {
        return err
    }
    return nil
}

func Update(u model.User) error {
    _sql := "update user set username=?, password =? where id =?"
    // _sql := "update user set username=? and password =? where id =?" 如果把,改成and mysql是不会更新的
    stmt, err := dbCon.Prepare(_sql)
    if err != nil {
        return err
    }
    defer stmt.Close()
    _, err = stmt.Exec(u.Username, u.Password, u.Id)
    if err != nil {
        return err
    }
    return nil
}

func FindUserById(id int) (*model.User, error) {
    _sql := "select username, password from user where id = ?"
    stmt, err := dbCon.Prepare(_sql)
    if err != nil {
        return nil, err
    }
    defer stmt.Close()

    user := model.User{}
    err = stmt.QueryRow(id).Scan(&user.Username, &user.Password)
    if err != nil {
        return nil, err
    }
    return &user, nil
}