Reading data from Mysql server

import the required packages, then prepare a struct with the same schema as in the example of the tb_student table in the database, later this struct will be used as a container for the query result data type.

package main
import "fmt"
import "database/sql"
import _ "github.com/go-sql-driver/mysql"


type student struct {
 id string,
 name string,
 age int,
 grade int,
}

The database driver used needs to be imported using the _ sign, because even though it is needed by the database/sql package, we do not directly interact with the driver.

Next, create a function to connect to the database.

func connect() (*sql.DB, error) {
 db, err := sql.Open("mysql","root:@tcp(127.0.0.1:3306)/db_learn_go")
 if err != nil {
  return nil, err
 }
 return db, nil
}

The connection string scheme for the mysql driver that we use is quite unique, root@tcp(127.0.0.1:3306)/db_learn_go below is a connection string scheme that can be used on the go MYSQL Driver driver, if you use another mysql, the connection scheme may be different depending on the driver used.

user:password@tcp(host:port)/dbname
user@tcp(host:port)/dbname

Below is an explanation of the connection string used in the connect() function.

root@tcp(127.0.0.1:3306)/db_learn_go
// user => root
// password =>
// host => 127.0.0.1 atau localhost
// port => 3306
// dbname => db_learn_go

After the function for connectivity with the database has been created, it's time to practice the process of reading data from the database server. Prepare the function
sqlQuery() with the following code.

func sqlQuery() {
 db, err := connect()
 if err != nil {
 fmt.Println(err.Error())
 return
 }
 defer db.Close()
 var age = 27
 rows, err := db.Query("select id, name, grade from tb_student where age = ?
 if err != nil {
 fmt.Println(err.Error())
 return
 }
 defer rows.Close()
 var result []student
 for rows.Next() {
 var each = student{}
 var err = rows.Scan(&each.id, &each.name, &each.grade)
 if err != nil {
 fmt.Println(err.Error())
 return
 }
 result = append(result, each)
 }
 if err = rows.Err(); err != nil {
 fmt.Println(err.Error())
 return
 }
 for _, each := range result {
 fmt.Println(each.name)
 }
}

Every time a new connection is created, don't forget to always close the connection instance. You can use the defer keyword as in the code above, defer
db.Close() .
The db.Query() function is used to execute sql queries. The function's second parameter is variadic, so it can be left blank. In the code above, you can see that the value of one of the where clauses is a question mark (?).

The sign will then be replaced by the value in the parameter after it (the value of the age variable). This type of query writing technique is highly recommended, to prevent sql injection. The function produces an instance of type sql.*Rows , which also needs to be closed when it is no longer used ( defer rows.Close() ). Next, an array with the element type struct student is prepared with the name result . Later the query results will be stored in the variable. Then a loop is performed with the condition reference being rows.Next() . This loop is performed as many times as the total number of records, sequentially from the first record to the end, one by one. The Scan() method of sql.Rows functions to retrieve the value of the record that is being iterated, to be stored in a pointer variable. The variables used to store the record fields are written sequentially as variadic parameters, according to the fields selected in the query. Please see the comparison below for more details. // query
select id, name, grade ...
// scan
rows.Scan(&each.id, &each.name, &each.grade ...
The obtained record data is then appended to the result slice, via the statement
result = append(result, each) .
OK, now just call the sqlQuery() function in main , then run the program.

func main() {
sqlQuery()
}

I'm just an ordinary blog writer. If there are still too many shortcomings, please forgive me.

The above is the detailed content of Reading data from Mysql server. For more information, please follow other related articles on the PHP Chinese website!