基于mysql的数据库基本操作及底层原理的简单解析

本文通过介绍面向mysql数据库的基本操作，包括;建库建表以及最基本的增删查改，来简单探究database/sql以及github.com/go-sql-driver/mysql的实现原理。

1. 创建数据库

使用Open()初始化相关的资源和配置，在使用Open()时不需要指定数据库的名字(因为此时还没有数据库)，代码如下：

 代码块一
 1 import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
)
...
    var err error
    sqlStat := fmt.Sprintf("%s:%s@(%s)/", "root", "123", "127.0.0.1:3306")
    dbtmp, err := sql.Open("mysql", sqlStat)
    if err != nil {
        panic(err)
    }

那Open()在底层做了什么，让我们来一探究竟。

 代码块二
 1 func Open(driverName, dataSourceName string) (*DB, error) {
    driversMu.RLock()
    driveri, ok := drivers[driverName]
    driversMu.RUnlock()
    if !ok {
        return nil, fmt.Errorf("sql: unknown driver %q (forgotten import?)", driverName)
    }

    if driverCtx, ok := driveri.(driver.DriverContext); ok {
        connector, err := driverCtx.OpenConnector(dataSourceName)
        if err != nil {
            return nil, err
        }
        return OpenDB(connector), nil
    }

    return OpenDB(dsnConnector{dsn: dataSourceName, driver: driveri}), nil
}

如上，首先从数据库驱动缓存里面取出mysql对应的驱动器，读者可能会问到：此驱动缓存是何时将各种数据库的驱动添加进去的呢？

其实它不是由于sql包主动添加的，而是sql包对外提供了一个Register()接口，用于注册数据库驱动，代码如下：

 代码块三
 1 func Register(name string, driver driver.Driver) {
    driversMu.Lock()
    defer driversMu.Unlock()
    if driver == nil {
        panic("sql: Register driver is nil")
    }
    if _, dup := drivers[name]; dup {
        panic("sql: Register called twice for driver " + name)
    }
    drivers[name] = driver
}

那mysql是何时注册自己的数据库驱动的呢？我们看到代码块一的第5行，在包名之前添加下划线的作用是只执行该包的init方法，而不需要导入整个包。关于Go语言程序模块的初始化顺序，参见https://studygolang.com/articles/6464。

那我们再来看看mysql数据库驱动包中的init方法做了什么：

func init() {
    sql.Register("mysql", &MySQLDriver{})
}

果然如此，mysql数据库驱动包在初始化的时候向sql包注册了自己的驱动。

我们回到代码块二，在第9行，直接将driveri断言成了DriverContext接口类型，这里driveri虽然是Driver类型，但是由上述分析可知其动态值是MySQLDriver类型，根据Go语言特性，一个接口类型可直接断言成另外一个接口类型，那么其动态类型必须实现后者，所以我们看看MySQLDriver是否实现了DriverContext接口：

 代码块四
 1 // If a Driver implements DriverContext, then sql.DB will call
// OpenConnector to obtain a Connector and then invoke
// that Connector's Conn method to obtain each needed connection,
// instead of invoking the Driver's Open method for each connection.
// The two-step sequence allows drivers to parse the name just once
// and also provides access to per-Conn contexts.
type DriverContext interface {
    // OpenConnector must parse the name in the same format that Driver.Open
    // parses the name parameter.
    OpenConnector(name string) (Connector, error)
}

 代码块五
 1 // OpenConnector implements driver.DriverContext.
func (d MySQLDriver) OpenConnector(dsn string) (driver.Connector, error) {
    cfg, err := ParseDSN(dsn)
    if err != nil {
        return nil, err
    }
    return &connector{
        cfg: cfg,
    }, nil
}

答案YES。我们从代码块四的解释中可以大致知道DriverContext接口的作用是让不同数据库驱动包提供工厂方法(OpenConnector)来获取其实现的Connector接口实例。而Connector接口定义如下：

 代码块六
 1 // A Connector represents a driver in a fixed configuration
// and can create any number of equivalent Conns for use
// by multiple goroutines.
//
// A Connector can be passed to sql.OpenDB, to allow drivers
// to implement their own sql.DB constructors, or returned by
// DriverContext's OpenConnector method, to allow drivers
// access to context and to avoid repeated parsing of driver
// configuration.
type Connector interface {
    // Connect returns a connection to the database.
    // Connect may return a cached connection (one previously
    // closed), but doing so is unnecessary; the sql package
    // maintains a pool of idle connections for efficient re-use.
    //
    // The provided context.Context is for dialing purposes only
    // (see net.DialContext) and should not be stored or used for
    // other purposes.
    //
    // The returned connection is only used by one goroutine at a
    // time.
    Connect(context.Context) (Conn, error)

    // Driver returns the underlying Driver of the Connector,
    // mainly to maintain compatibility with the Driver method
    // on sql.DB.
    Driver() Driver
}

从解释中可以看出Connector接口主要用于与mysql server创建连接。而在代码块五中工厂方法OpenConnector确实返回了一个实现了Connector接口的结构体connector实例。

再次回到代码块二，接下来调用OpenDB()，如下：

func OpenDB(c driver.Connector) *DB {
    ctx, cancel := context.WithCancel(context.Background())
    db := &DB{
        connector:    c,
        openerCh:     make(chan struct{}, connectionRequestQueueSize),
        resetterCh:   make(chan *driverConn, 50),
        lastPut:      make(map[*driverConn]string),
        connRequests: make(map[uint64]chan connRequest),
        stop:         cancel,
    }

    go db.connectionOpener(ctx)
    go db.connectionResetter(ctx)

    return db
}

上述代码先是初始化配置和资源，然后启动一个goroutine调用connectionOpener()来监听新建连接的请求。

总结：所以Open()的作用主要是初始化配置和资源，并监听新建连接的请求。

调用了Open()之后，接着调用Exec()执行sql语句创建数据库，代码如下：

    sqlStat = fmt.Sprintf("CREATE DATABASE IF NOT EXISTS %s", "mydb")
    _, err = dbtmp.Exec(sqlStat)
    if err != nil {
        panic(err)
    } 

如果追踪下去，会发现实际上是从连接池选取一条连接或者新建一条的连接，将sql语句发送过去执行。

由于前面调用Open()时没有指定具体的数据库，因此底层与mysql server建立连接时并不会指定使用具体哪个数据库，所以应用程序后续进行数据库操作时(如CRUD)，必须首先执行如下语句，用于与mysql server建立指定具体数据库的连接：

    sql = fmt.Sprintf("USE %s", DBName)
    _, err = db.Exec(sql)

这样比较麻烦，所以我们在创建数据库之后，可以重新创建一个DB实例，同时指定刚刚创建的数据库的名字。代码如下：

    sqlStat = fmt.Sprintf("%s:%s@(%s)/%s", "root", "123", "127.0.0.1:3306", "mydb")
    db, err = sql.Open("mysql", sqlStat)
    if err != nil {
        panic(err)
    }

而返回的DB实例通常作为全局变量在程序的整个生命周期用于对数据库的操作。

2. CRUD

这一部分内容为对数据库表记录最基本的增删查改操作。

1）添加记录

/**
 * Insert single record of 'tableName'
 * @params: value is the record to Insert(field name and value pairs)
 * @return: error info
 */

func Insert(tableName string, value map[string]interface{}) error {
    sqlStr := "INSERT " + tableName + " SET"
    var args []interface{}
    for k, v := range value {
        sqlStr = sqlStr + " " + k + "=?,"
        args = append(args, v)
    }
    //remove the last ','
    sqlArr := []byte(sqlStr)
    sqlStr = string(sqlArr[:len(sqlArr)-1])

    // prepare
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        logger.Error("Prepare sql error: ", err.Error())
        return fmt.Errorf("Insert data failed.")
    }
    // exec
    _, err = stmt.Exec(args...)
    if err != nil {
        logger.Error("Exec sql error: ", err.Error())
        return fmt.Errorf("Insert data failed.")
    }

    return nil
}

2）删除记录

/**
 * Delete record of 'tableName'
 * @params: condition is used to find the records to be deleted, and is table field name-value pairs
 * @return: error info
 */

func Delete(tableName string, condition map[string]interface{}) error {
    // join sql statement
    sqlStr := "DELETE FROM " + tableName + " WHERE"
    var args []interface{}
    for k, v := range condition {
        sqlStr = sqlStr + " " + k + "=?,"
        args = append(args, v)
    }
    //remove the last ','
    sqlArr := []byte(sqlStr)
    sqlStr = string(sqlArr[:len(sqlArr)-1])

    // prepare
    stmt, err := db.Prepare(sqlStr)
    defer stmt.Close()
    if err != nil {
        logger.Error("Prepare sql error: ", err.Error())
        return fmt.Errorf("Delete data failed")
    }
    // exec
    _, err = stmt.Exec(args...)
    if err != nil {
        logger.Error("Exec sql error: ", err.Error())
        return fmt.Errorf("Delete data failed")
    }

    return nil
}

3）查询记录(可查多条记录)

/**
 * Query records of 'tableName'
 * @params: condition is used to find the records, and is table field name-value pairs
 * @return: query result records encapsulated into a map(for now, all values' type are string)
 *          error info
 */

func Query(tableName string, condition map[string]interface{}) ([]map[string]interface{}, error) {
    // join sql statement
    sqlStr := "SELECT * FROM " + tableName
    var args []interface{}
    if len(condition) != 0 {
        sqlStr += " WHERE"
        for k, v := range condition {
            sqlStr = sqlStr + " " + k + "=? AND"
            args = append(args, v)
        }
        //remove the last 'and'
        sqlArr := []byte(sqlStr)
        sqlStr = string(sqlArr[:len(sqlArr)-3])
    }
    // query
    rows, err := db.Query(sqlStr, args...)
    if err != nil {
        logger.Error("Query error: ", err.Error())
        return nil, fmt.Errorf("Query data failed")
    }
    defer rows.Close()
    cols, err := rows.Columns() //get table fields name
    if err != nil {
        logger.Error("Get columns error: ", err.Error())
        return nil, fmt.Errorf("Query data failed")
    }
    colsLen := len(cols)
    var queryResult []map[string]interface{} //query result that may include multiple records.
    for rows.Next() {
        beans := make([]interface{}, colsLen)
        for i := 0; i < colsLen; i++ {
            beans[i] = &sql.NullString{}
        }
        err := rows.Scan(beans...)
        if err != nil {
            logger.Error("Scan record  from table error: ", err.Error())
        }
        queryResult = append(queryResult, toMap(beans, cols))
    }

    return queryResult, nil
}

需要解释的是，我把查询出来的记录转化成了map切片，切片的每个元素为一条记录，map的key为数据库表中的字段名，value为值，这里有个缺陷就是value都转换成了string类型。

4）更改记录(只支持最简单的UPDATE语句，即为：UPDATE [table name] SET [fields name and value to be changed] WHERE [condition fileds name and value])

/**
 * update record of 'tableName'
 * @params: condition is used to find the records
 *          value includes names and values of the fields to be updated
 * @return: error info
 */

func Update(tableName string, condition, value map[string]interface{}) error {
    // join sql statement
    sqlStr := "UPDATE " + tableName + " SET"
    var args []interface{}
    for k, v := range value {
        sqlStr = sqlStr + " " + k + "=?,"
        args = append(args, v)
    }
    //remove the last ','
    sqlArr := []byte(sqlStr)
    sqlStr = string(sqlArr[:len(sqlArr)-1])
    sqlStr += " WHERE"
    for k, v := range condition {
        sqlStr = sqlStr + " " + k + "=? AND"
        args = append(args, v)
    }
    //remove the last 'and'
    sqlArr = []byte(sqlStr)
    sqlStr = string(sqlArr[:len(sqlArr)-3])
    // prepare
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        logger.Error("Prepare sql error: ", err.Error())
        return fmt.Errorf("Update data failed")
    }
    // exec
    _, err = stmt.Exec(args...)
    if err != nil {
        logger.Error("Exec sql error: ", err.Error())
        return fmt.Errorf("Update data failed")
    }

    return nil
}