Golang connect MySQL database to make additions and deletions

This is a creation in Article, where the information may have evolved or changed. database connections are connected using the Datebase/sql open function

The code is as follows
db, err: = SQL. Open ("MySQL", "user:password@tcp (localhost:5555)/dbname?charset=utf8") where 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

code as follows	 
stmt, err: = db. Prepare (' INSERT user (User_name,user_age,user_sex) VALUES (?,?,?) ') Checkerr (Err) Res, err: = stmt. Exec ("Tony", 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

The code is as follows
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.

The code is as follows

//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: = Do ([]interface{}, Len (Columns)) for I: = Range 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 {  & nbsp;      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

The code is as follows

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) } Query Demo Func query () { DB, err: = SQL. Open ("MySQL", "Root:@/test?charset=utf8") Checkerr (ERR) Rows, err: = db. Query ("SELECT * from User") Checkerr (ERR) Normal 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 () { Save 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, connection pooling is typically used in applications to reduce connection overhead, and next time you put the connection pool together