Golang connection MySQL database implementation and deletion change check

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 for 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.

To create a test table

Create a test table in the MySQL test library

The code is as follows

Copy Code

CREATE TABLE IF not EXISTS ' test '. ' User ' ( ' user_id ' INT (one) UNSIGNED not NULL auto_increment COMMENT ' user 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 sex ', PRIMARY KEY (' user_id ')) ENGINE = InnoDB Auto_increment = 1 DEFAULT CHARACTER SET = UTF8 COLLATE = Utf8_general_ci COMMENT = ' User table '

Database connection

The database connection uses the Datebase/sql open function to connect

The code is as follows	Copy Code
DB, err: = SQL. Open ("MySQL", "user:password@tcp (localhost:5555)/dbname?charset=utf8") There are several forms of connection parameters: 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 use the second kind.

Insert operation

The code is as follows	Copy Code
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 the method of directly stitching SQL statements.

Query operations

code is as follows	copy code
rows, err: = db. Query (' select * from user ') Checkerr (err)   for rows. Next () {     var userId int     var userName string     var UserA GE 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) }

In this way the query is used to declare 4 independent variables userid, UserName, Userage, usersex to save the values of each row of the query. The operation of the database is usually encapsulated in the actual development, and the query usually considers returning the dictionary type.

The code is as follows

Copy Code

//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: = R Ange values {     scanargs[i] = &values[i]   for rows. Next () {    //Save row data to the record dictionary     err = rows. Scan (Scanargs ...)     Record: = Make (map[string]string)     for I, col: = range values {  &N bsp;      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 modification and deletion operations are simpler, similar to the insertion data, but use rowsaffected to get the number of rows that affect the data.

Complete code

The code is as follows

Copy Code

Package Main Import ( "Database/sql" "FMT" _ "Github.com/go-sql-driver/mysql" ) Func Main () { Insert () } Insert Demo Func 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) } Search Demo Func query () { DB, err: = SQL. Open ("MySQL", "Root:@/test?charset=utf8") Checkerr (ERR) Rows, err: = db. Query ("SELECT * from user") Checkerr (ERR) Common Demo 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 () { Saving row data to a 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

On the whole is relatively simple, that is, the query that use the dictionary to store the return data is more complex. When it comes to database connections, the connection pool is usually used to reduce the connection overhead, and the connection pool will be sorted again next time.