Go language move brick operation mysql

This is the 25th day of my participation in the August Challenge

Introduction to the

The relational database mysql is very popular, and has been for many years. It is still a solid number two in DB-Engines’ rankings, down from last year, but still very popular.

Leaderboard portal: db-engines.com/en/ranking

The installation

Quick installation with Docker, past portal: juejin.cn/post/684490…

Install mysql5.7 and set root password to 6 sixes

docker run --name mysql -e MYSQL_ROOT_PASSWORD=666666 -d -p 3306:3306 mysql:5.7
Copy the code

UI Tools bloggers use a free lightweight tool HeidiSQL: www.heidisql.com/

HeidiSQL also has the following image

If you like to use VScode, you can also install the mysql plugin directly in VScode and write SQL in VScode

The client

The go official package does not provide a database driver, but defines some standard interfaces for developing databases

We need to use third-party packages to operate mysql database. But the third party package is numerous, the most popular is: github.com/go-sql-driv…

There is also a database/ SQL extension based on the official, also very popular, portal: github.com/jmoiron/sql…

It is possible to use both packages interchangeably in this article, or to use only one without guarantee

Of course, some well-known ORM frameworks (GORM,XORM, GorOSE) are not mentioned here

API example

Initialize the

The client is defined as a global switch to facilitate subsequent use

Define a set of mysql configuration variables, and then initialize the connection

You need to create the test library before initializing the connection

var (
   UserName  string = "root"
   PassWord  string = "666666"
   IP string = "ip"
   Port int = 3306
   DbName string = "test"
   CharSet string = "utf8mb4"
   
   Db *sql.DB
   err error
)

func init() {
   dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s", UserName, PassWord, IP, Port, DbName, CharSet)
   Db, err = sql.Open("mysql", dsn)
   iferr ! = nil { log.Fatalln(Failed to connect to mysql:,err)
   }
}
Copy the code

Create a table

Create a user information table with primary key id and increment by 1

func createTable() {
   sql: =` create table user_info( id bigint unsigned not null auto_increment, Name varchar(100) NOT NULL DEFAULT "COMMENT", email varchar(50) NOT NULL DEFAULT "COMMENT", Age int(11) NOT NULL DEFAULT '0' COMMENT 'age ', primary key (id) ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4; `
   result, err := Db.Exec(sql)
   iferr ! = nil { log.Println(Table creation failed:,err)
   }
   log.Println(result.RowsAffected())
   log.Println(result.LastInsertId())
}
Copy the code

Insert data

Write a little loop and write 100 pieces of data

func insertData() {
   for i:=1; i<100; i++ { result,_ := Db.Exec("insert into user_info (name,age) value (? ,?) "."zhangsan" + strconv.Itoa(i),18+i)
      log.Println(result.LastInsertId())
   }
}
Copy the code

Query data

Define the structure in advance

Specify id to query a piece of data

Call the QueryRow method to return a result based on the query criteria

type UserInfo struct {
   Id  int
   Name string
   email string
   age int
}

func selectData() {
   var u UserInfo
   sqlStr: ="select id,name,age from user_info where id=?"
   _ = Db.QueryRow(sqlStr, 2).Scan(&u.Id, &u.Name, &u.age)
   fmt.Println(u.Id,u.Name,u.age)
}
Copy the code

Querying multiple pieces of Data

Call the Query method, return a result set, and loop through the output

sqlStr2 := "select id,name,age from user_info where id > ?"
rows, _ := Db.Query(sqlStr2, 0)
// The function closes the connection
defer rows.Close()

for rows.Next(){
   var u UserInfo
   _ = rows.Scan(&u.Id, &u.Name, &u.age)
   fmt.Println(u.Id,u.Name,u.age)
}
Copy the code

Update the data

Update the data whose ID1 is to change the age value to 18

func updateData() {
   sqlStr: ="update user_info set age=? where id = ?"
   exec, _ := Db.Exec(sqlStr, 18.1)
   fmt.Println(exec.RowsAffected())
}
Copy the code

Delete the data

Delete data with id 5, because id is increment, so only one data should be deleted

You can see from the RowsAffected method return how many pieces of data are affected

func deleteData() {
   sqlStr: ="delete user_info user where id = ?"
   exec, _ := Db.Exec(sqlStr, 5)
   fmt.Println(exec.RowsAffected())
}
Copy the code

Check the type

View the table database and GO language types

func columnType() {
   sqlStr: ="select * from user_info"
   query, _ := Db.Query(sqlStr)
   columns, _ := query.ColumnTypes()
   for _,column := range columns {
      fmt.Printf("Field name: %v, database type: %v,GO language mapping type: %v\n",column.Name(),column.DatabaseTypeName(),column.ScanType())
   }
}
Copy the code

Methods to summarize

• Db.query Executes a Query and returns a value with a result, usually a SELECT
• Db.exce executes commands that do not return the value of the result, usually update, delete, etc
• Rows.next () iterates over the query data
• Rows.scan () reads the value of each row
• QueryRow queries and returns a row of data

Mysql transaction

The following theories come from the Internet

A transaction is the smallest non-divisible unit of work, usually one transaction for a complete business

Classic transaction cases take the example of a bank transfer, characterized by either success or failure and no other state

The database engine used in mysql is InnoDB, because it supports transactions

The transaction of the ACID

• Atomicity All operations in a transaction either complete or do not complete, and do not end up somewhere in between. If a transaction fails during execution, it will be rolled back to the state before the transaction began, as if the transaction had never been executed

• Consistency Database integrity is not compromised before and after a transaction. This means that the data written must conform to all the preset rules, including the accuracy of the data, the concatenation of the data, and the ability of the subsequent database to do its predetermined work spontaneously

• Isolation The ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation prevents data inconsistencies due to cross-execution when multiple transactions are executed concurrently. Transaction isolation can be divided into different levels, including Read uncommitted, Read Committed, Repeatable Read and Serializable.

• After a persistent transaction, changes to the data are permanent and will not be lost even if the system fails

There are various isolation levels, as shown in the following figure —-. The image is from the network

Transaction example

This example does two transactions, one successful completion and one rollback

func transaction() {
   // Clear the user_info table
   sqlStr: ="truncate table user_info;"
   Db.Exec(sqlStr)

   sqlStr2: ="insert into user_info(name,age) values (? ,?) "
   tx1, _ := Db.Begin()
   result, _ := tx1.Exec(sqlStr2, "zhangsan17".17)
   fmt.Println(result.RowsAffected())
   fmt.Println(result.LastInsertId())
   tx1.Commit()

   tx2, _ := Db.Begin()
   result2, _ := tx2.Exec(sqlStr2, "zhangsan16".16)
   fmt.Println(result2.RowsAffected())
   fmt.Println(result2.LastInsertId())
   tx2.Rollback()

   sqlStr3: ="select id,name,age from user_info"
   result3, _ := Db.Query(sqlStr3)
   for result3.Next(){
      var u UserInfo
      _ = result3.Scan(&u.Id, &u.Name, &u.age)
      fmt.Println(u.Id,u.Name,u.age)
   }
}
Copy the code

conclusion

Go-sql-driver /mysql is actually based on the official database/ SQL enhancement package, example in most SQL statements are manual, if using ORM package, basically are method chain operation

For small projects, direct use of this package is small in size and fast in interaction