Go basics-Operating MySQL (ii)

In the previous article mainly organized the Golang connection MySQL and some basic operations, and carried out a general introduction, this article on the deletion and modification of the detailed collation

Reading data

In the previous article to organize the query data, the use of Query method queries, in fact, Database/sql also provides the Queryrow method query data, as previously said Database/sql connection creation is inert, So when we query the data via query, we are divided into three main steps:

1. Request a connection from the connection pool
2. SQL statement to execute query
3. To pass ownership of a database connection to a result set

The result set returned by query is the sql.rows type. It has a next method that iterates through the database's cursors to get the data for each row, using the following method:

perform a query operation Rows,err:= db.query ("Select email from user_info WHERE user_id>=5")if err! =  nil{    FMT. Println ("SELECT db Failed,err:", Err)    return}//The rows obtained here are email messages from the database that satisfy user_id>=5 of all rows, Rows. Next (), which is used to loop through all for rows. Next () {    var s string    = rows. Scan (&s)    if err ! =        nil{fmt. PRINTLN (Err)        return    }    fmt. Println (s)}rows. Close ()

In fact, when we iterate over the database through the For loop, we start an IO when we iterate to the last data. EOF signal, an error is raised, and go automatically calls rows. The Close method frees the connection and then returns False, at which point the loop ends the exit.

Normally you will iterate through the data and exit the loop normally. However, if there is no normal loop, the loop is exited due to other errors. At this point, rows. Next the process of processing the result set is not completed, and the connection to rows is not released back to the connection pool. It is therefore necessary to properly handle rows. Close event. If you do not close the rows connection, it will result in a large number of connections and will not be reused by other functions, as if it were overflowed. The result is that the database will not be available.

So in order to avoid this, the best way is to show the call rows. The Close method ensures that the connection is released, or the connection is freed when the function exits using the defer instruction, even if the connection has been freed, rows. Close can still be called multiple times and is harmless.

Rows. Next loop iteration, because the IO is triggered. EOF and exit the loop. To check whether an iteration exits gracefully or exits unexpectedly, you need to check rows. Err. For example, the above code should be changed to:

query executes the rows,err:= db.query ("Select email from user_info WHERE user_id>=5")if err! = nil{    fmt. Println ("SELECT db Failed,err:", Err)    return}//The rows obtained here are email messages from the database that satisfy user_id>=5 of all rows, Rows. Next (), which is used to loop through all for rows. Next () {    var s string    = rows. Scan (&s)    if err ! =        nil{fmt. PRINTLN (Err)        return    }    fmt. Println (s)}rows. Close ()if err = rows. Err (); Err ! = nil{    fmt. PRINTLN (Err)    return}

Reading a single piece of data

The query method is to read multiple rows of result sets, in real development, many queries only need a single record, do not need to go through next iteration. Golang provides a result set that Queryrow methods use to query a single record.

The Queryrow method is simple to use, and it either returns SQL. Row type, either returns an error and, if it is sent, delays the return of the scan call and, if there is no error, the scan executes normally. A SQL is triggered only when the result of the query is empty. Errnorows error. You can choose to check for errors before calling the scan method, or call scan to check for errors first.

We've used the scan method in the previous code, and here's what we've got to say about this method.

The result set method scan can assign the value of the field retrieved from the database to the specified data structure. Its argument is a slice of an empty interface, which means that any value can be passed in. A pointer to a target variable that needs to be assigned is usually passed in as a parameter, which assigns the value of the database fetch to the pointer value object.
Examples of code such as:

query data var username stringvar email stringrows  := Db.queryrow ("Select Username,email from User_info WHERE US Er_id=6 "= rows. Scan (&username,&email)if err ! = nil{    fmt. Println ("Scan err:", Err)    return}fmt. Println (Username,email)

Scan will also help us automatically infer that the data field matches the target variable. For example, there is a database field whose type is varchar, and his value is a string of numbers, such as "1". If we define the target variable to be a string, then the target variable is a number string after the scan is assigned. If the declared target variable is a numeric type, then scan automatically calls StrConv. parseint () or StrConv. The parseint () method converts a field to a type that is consistent with the declared target variable. Of course, if some fields cannot be converted successfully, an error is returned. Therefore, you need to check for errors after you call scan.

Null value processing

The database has a special type of NULL null value. However, NULL cannot be directly assigned to universal variables via scan, or even null can be assigned to nil. For null you must specify a special type, which is defined in the Database/sql library. For example, SQL. Nullfloat64,sql. Nullstring,sql. Nullbool,sql. NullInt64. If you cannot find a matching type in the standard library, you can try to find it in the driver. The following is a simple example:

The following code, Create_time in the database is null this time, if this query directly, will prompt the error:

query data var username stringvar email stringvar createtime stringrows  := Db.queryrow ("Select Username,email, Create_time from User_info WHERE user_id=6 "= rows. Scan (&username,&email,&createtime)if err ! = nil{    fmt. Println ("Scan err:", Err)    return}fmt. Println (username,email,createtime)

The error content is as follows:

Scan Err:sql:Scan error on column index 2:unsupported Scan, storing driver. Value type <nil> into type *string

So you need to change the code to:

query data var username stringvar email stringvar createtime SQL. Nullstringrows  := Db.queryrow ("Select Username,email,create_time from User_info WHERE user_id=6"= Rows. Scan (&username,&email,&createtime)if err ! = nil{    fmt. Println ("Scan err:", Err)    return}fmt. Println (username,email,createtime)

The result of the execution is:

false}

I added a column to the database, the int type, the same default value is NULL, the code is:

 introws  := Db.queryrow ("Select Username,email,create_time,socre from User_info WHERE user_id=6 ") rows. Scan (&username,&email,&createTime,&score) fmt. Println (Username,email,createtime,score)

In fact, but we ignore the error direct output, you can also output, of course, null fields are converted to 0 value
And when we do the following, the code is:

query data var username stringvar email stringvar createtime SQL. Nullstringvar score SQL. Nullint64rows  := Db.queryrow ("Select Username,email,create_time,socre from User_info WHERE user_id=6"  = rows. Scan (&username,&email,&createTime,&score)if err ! = nil{    fmt. Println ("Scan fail,err:", Err)    return}fmt. Println (Username,email,createtime,score)

The result of the output is:

false false}

For null operations, it is generally necessary to verify that the code is as follows:

query data var score sql. Nullint64rows  := Db.queryrow ("Select Socre from User_info WHERE user_id=6"= rows. Scan (&score)if err ! = nil{    fmt. Println ("Scan fail,err:", Err)    return}if  score. valid{    FMT. PRINTLN ("res:", score. Int64)}else{    FMT. Println ("Err", score. Int64)}

Here I have added content to the field in the database, so the default output here is 10, but when it is null, the output is 0 value.
But sometimes if we don't care if it's null, just want to treat it as an empty string, we can also use []byte, the code is as follows:

query data var score []bytevar modifytime []byterows  := Db.queryrow (" Select Modify_time,socre from User_info WHERE user_id=6 "= rows. Scan (&modifyTime,&score)if err ! = nil{    fmt. Println ("Scan fail,err:", Err)    return}fmt. Println (String (modifytime), string (score))

After this processing, if there is a value then you can get the value, and if not, get the empty string

Auto Match Fields

In the above query example, we have defined the variables themselves, while the query also indicated that the field, if not named field, or the order of the field and the query is not the same, there may be errors. Therefore, if you can automatically match the field values of the query, you will save code and be easy to maintain.
Go provides the columns method with the Get field name, like most functions, the read failure will return an err, so you need to check for errors.
The code example is as follows:

//query data rows,err:= Db.query ("select * from User_info WHERE user_id>6")ifErr! =nil{FMT. Println ("Select Fail,err:", Err) Return}cols,err:=rows. Columns ()ifErr! =nil{FMT. Println ("Get columns Fail,err:", err) return}fmt. Println (cols) Vals:= Make ([]byte, Len (cols)) scans:=Make ([]interface{},len (cols)) for I:=range vals{Scans[i]= &vals[i]}fmt. PRINTLN (Scans) var results []map[string]stringfor rows. Next () {Err=rows.    Scan (Scans ...) ifErr! =nil{FMT. Println ("Scan Fail,err:", err) return} row:=Make (map[string]string) for k,v:=Range vals{key:=Cols[k] Row[key]=string (v)} results=Append (results,row)}for k,v:=Range results{FMT. Println (k,v)}

Because the statement is when the query is:
SELECT * from User_info WHERE user_id>6
This will get all the fields for each row of data
Use rows. Columns () Gets the field name, which is an array of string
Then create a slice vals that holds the resulting data, similar to the slice of byte. Next, you need to define a slice, which is used for scan, to copy the value of the database to it
Vals gets the value that scan copied to him because it is a slice of byte, so once in a loop, convert it to a string.
The converted row is the value of the data row we took out and finally assembled into the result slice.

The result of the above code is:

[user_id username sex email create_time modify_time socre][0xc4200c6000 0xc4200c6018 0xc4200c6030 0xc4200c6048 0xc4200c6060 0xc4200c6078 0xc4200c6090]0 map[user_id:7 username:user01 Sex: Male Email:[email protected] Create_ time:2018-03-05 14:10:08 modify_time:socre:]1 map[username:user11 Sex: Male Email:[email protected] Create_time : 2018-03-05 14:10:11 modify_time:socre:user_id:8]2 map[sex: Male email:[email protected] create_time:2018-03-05 14:10:15 Modify_time:socre:user_id:9 Username:user12]

Through the above example of the collation and the above article, we can basically know:
Exec is typically used to perform insert and update operations
Query and Queryrow are typically used for querying operations

After exec finishes, the connection is immediately released back into the connection pool, so you do not need to call the row's Close method manually, as in query.

Go basics-Operating MySQL (ii)