Golang Connection MySQL Operation example additions and deletions change

This is a creation in Article, where the information may have evolved or changed.

The Golang itself does not provide a driver to connect to MySQL, but it defines a standard interface for third-party development drivers. Here to connect MySQL can use third-party libraries, third-party libraries recommend the use of Https://github.com/Go-SQL-Driver/MySQL this driver, update maintenance is better. The following shows the specific use, the complete code example can refer to the last.

Download driver

sudo go get github.com/go-sql-driver/mysql

If you are prompted with such a failure message: Cannot download, $GOPATH not set. For more details See:go help Gopath, you can use the following command to resolve

sudo env gopath=/users/chenjiebin/golang go get github.com/go-sql-driver/mysql

The value of the Gopath is replaced by its own environment.

Create a test table

Create a test table in the MySQL test library

CREATE TABLE IF not EXISTS ' test '. ' User ' (' user_id ' INT (one) UNSIGNED not NULL auto_increment COMMENT ' subscriber number ', ' user_name ' VARCHAR NOT null COMMENT ' user name ', ' User_age ' TINYINT (3) UNSIGNED not null DEFAULT 0 COMMENT ' user age ', ' User_sex ' TINYINT ( 3) UNSIGNED not NULL default 0 COMMENT ' user Gender ', PRIMARY KEY (' user_id ')) ENGINE = InnoDB auto_increment = 1 Default charact ER SET = UTF8 COLLATE = utf8_general_ci COMMENT = ' User table '

Database connection

Database connections are connected using the Datebase/sql open function

db, err := sql.Open("mysql", "user:password@tcp(localhost:5555)/dbname?charset=utf8")

The connection parameters can be in the following ways:

User@unix (/path/to/socket)/dbname?charset=utf8
User:password@tcp (localhost:5555)/dbname?charset=utf8
User:password@/dbname
USER:PASSWORD@TCP ([de:ad:be:ef::ca:fe]:80)/dbname

Usually we all use the second kind.

Insert operation

stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)checkErr(err)res, err := stmt.Exec("tony", 20, 1)checkErr(err)id, err := res.LastInsertId()checkErr(err)fmt.Println(id)

Using structured operations here, it is not recommended to use a method that directly stitching SQL statements.

Query operations

rows, err := db.Query("SELECT * FROM user")checkErr(err)for rows.Next() {    var userId int    var userName string    var userAge int    var userSex int    rows.Columns()    err = rows.Scan(&userId, &userName, &userAge, &userSex)    checkErr(err)    fmt.Println(userId)    fmt.Println(userName)    fmt.Println(userAge)    fmt.Println(userSex)}

The query here uses the declaration of 4 independent variables userid, UserName, Userage, usersex to save the values of each row queried. The operation of the database is typically encapsulated in real-world development, and queries such as this typically take into account the return dictionary type.

//构造scanArgs、values两个数组，scanArgs的每个值指向values相应值的地址columns, _ := rows.Columns()scanArgs := make([]interface{}, len(columns))values := make([]interface{}, len(columns))for i := range values {    scanArgs[i] = &values[i]}for rows.Next() {    //将行数据保存到record字典    err = rows.Scan(scanArgs...)    record := make(map[string]string)    for i, col := range values {        if col != nil {            record[columns[i]] = string(col.([]byte))        }    }    fmt.Println(record)}

Modify Operation

stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)checkErr(err)res, err := stmt.Exec(21, 2, 1)checkErr(err)num, err := res.RowsAffected()checkErr(err)fmt.Println(num)

Delete operation

stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)checkErr(err)res, err := stmt.Exec(1)checkErr(err)num, err := res.RowsAffected()checkErr(err)fmt.Println(num)

Both the modify and delete operations are simple, similar to inserting data, using only rowsaffected to get the number of rows affected.

Full code

Import (     "Database/sql"      "FMT"     _  " Github.com/go-sql-driver/mysql ") Func main () {    insert ()}//insert Demofunc Insert () {     DB, err: = SQL. Open ("MySQL",  "Root:@/test?charset=utf8")     checkerr (Err)      stmt, err: = db. Prepare (' INSERT user (User_name,user_age,user_sex) VALUES (?,?,?) ')     checkerr (err)     res, err: = stmt. Exec ("Tony",  20, 1)     checkerr (err)     id, err: = Res. Lastinsertid ()     checkerr (err)     fmt. Println (ID)}//queries demofunc query () {    db, err: = SQL. Open ("MySQL",  "Root:@/test?charset=utf8")     checkerr (Err)      Rows, err: = db. Query ("SELECT * from User")     checkerr (Err)     //General DEMO &NBSp;  //for rows. Next () {    //    var userId int    //    var UserName string    //    var userage int    //     var usersex int    //    rows. Columns ()     //    err = rows. Scan (&userid, &username, &userage, &usersex)     //    Checkerr ( ERR)     //    FMT. Println (userId)     //    FMT. Println (userName)     //    FMT. Println (userage)     //    FMT. Println (usersex)     //}    //dictionary type     //constructs Scanargs, Values two arrays, each value of Scanargs points to the address of values corresponding to     columns, _: = Rows. Columns ()     scanargs: = Make ([]interface{}, Len (columns))     values: = Make ([]interface{}, Len (columns))      For I: = range values {        scanargs[i] = &values[i]     }    for rows. Next () {        //saves row data to the record dictionary          err = rows. Scan (Scanargs ...)         record: = Make (map[string]string)          for I, col: = range values {             if col! = Nil {                 record[columns[i]] = string (col. ([]byte))              }        }         fmt. Println (Record)     }}//Update Data func update () {    db, err: = SQL. Open ("MySQL",  "Root:@/test?charset=utf8")     checkerr (Err)      stmt, err: = db. Prepare (' UPDATE user SET user_age=?,user_sex=? WHERE user_id=? ')     checkerr (err)     res, err: = stmt. Exec (21, 2, 1)     checkerr (err)     num, err: = Res. Rowsaffected ()     checkerr (err)     fmt. PRINTLN (num)}//Delete data func remove () {    db, err: = SQL. Open ("MySQL",  "Root:@/test?charset=utf8")     checkerr (Err)      stmt, err: = db. Prepare (' DELETE from user WHERE user_id=? ')     checkerr (err)     res, err: = stmt. Exec (1)     checkerr (err)     num, err: = Res. Rowsaffected ()     checkerr (err)     fmt. PRINTLN (num)}func checkerr (err error) {    if err! = Nil {         panic (Err)     }}

Summary

As a whole, it is relatively simple to query the other side using a dictionary to store the returned data is more complex. When it comes to database connectivity, the connection pooling is usually used in the application to reduce the connection cost, and the next time the connection pool is sorted out, it will be put up.

Reference: "Go Web Programming", go Web Programming in the database connection made a more detailed explanation, it is worth a look.