I Download MySQL driver
Execute the following command: go get - u GitHub com/go-sql-driver/mysql
II Connect to the database using MySQL driver
First create a database create database go_mysql_test
Then establish a connection through the sql package:
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dsn := "username:password@tcp(host:port)/go_mysql_test"
db, err := sql.Open("mysql", dsn) // db is a * SQL db type
if err != nil {
panic(err)
}
defer db.Close()
}
It should be noted that although the connection is recommended through the sql package, the necessary implementation is done through "GitHub. COM / go sql driver / MySQL". The package should be imported and declared not to use, Let its init do initialization
You can also set the maximum number of connections, the maximum number of idle connections, etc.:
db.SetMaxOpenConns(100) // maximum connection db.SetMaxIdleConns(100) // Maximum idle number db.SetConnMaxLifetime(time.Second * 10) // Maximum lifetime of the connection db.SetConnMaxIdleTime(time.Second * 5) // Maximum idle time of the connection
Refer to this blog for four meanings: https://www.cnblogs.com/gitfong/p/13722204.html
III CRUD
First, create a user table as follows:
create table `user` ( id INT PRIMARY KEY AUTO_INCREMENT, sex VARCHAR(2) DEFAULT '', name VARCHAR(10) DEFAULT '' )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Similarly, create a structure with the same structure:
type User struct {
id int
sex string
name string
}
3.1 insert data
Through the Exec() operation of db:
sql := "insert into user(sex, name) values(?,?)"
ret, err := db.Exec(sql, "male", "jerry")
if err != nil {
fmt.Println("Insert error", err.Error())
return
}
id, err := ret.LastInsertId()
fmt.Println(id) // 1
3.2 update data
Still use the above method to modify the sql:
sql := `update user set sex=?, name=? where id = ?`
ret, err := db.Exec(sql, "female", "Bing Dwen Dwen", 1)
if err != nil {
fmt.Println("Update error", err.Error())
return
}
row, err := ret.RowsAffected()
fmt.Println(row)
3.3 deleting data
Similarly
3.4 query data
Single line query:
var user User
// Single line query
sql := `select * from user where id = ?`
// Here, pay attention to Scan to release the connection
err = db.QueryRow(sql, 1).Scan(&user.id, &user.sex, &user.name)
if err != nil {
fmt.Println("Query error", err.Error())
return
}
fmt.Println(user)
Multi line query:
sql := `select * from user where id < ?`
query, err := db.Query(sql, 3)
if err != nil {
fmt.Println("Query error", err.Error())
return
}
defer query.Close() // Pay attention to closing
for query.Next() {
var user User
err := query.Scan(&user.id, &user.sex, &user.name)
if err != nil {
fmt.Println("scan error", err.Error())
return
}
fmt.Println(user)
}
IV Pretreatment
Compile ahead of time to save costs and prevent SQL injection:
sql = "select * from user where id < ?"
stmt, er := db.Prepare(sql)
if err != nil {
fmt.Println("error" , err.Error())
return
}
defer stmt.Close() // Pay attention to closing
query, err := stmt.query(3) // Write parameters directly
defer query.Close()
for query.Next() {
// Same operation
}
// Addition, deletion and modification are the same, stmt Exec()
V affair
The process is as follows:
- Use dB Begin() gets a transaction
- Replace the previous dB with a transaction Exec() and stmt Query() and other operations
- Execute tx.Rollback() operation in error to roll back
- After the tx.Commit() operation is finally executed
tx, err := db.Begin() // Open transaction
if err != nil {
if tx != nil {
tx.Rollback() // RollBACK
}
fmt.Printf("begin trans failed, err:%v\n", err)
return
}
sql1 := "xxx"
r1, err := tx.Exec(...)
if err != nil {
tx.Rollback()
Log()
return
}
sql2 := "xxx"
r2, err := tx.Exec(...)
if err != nil {
tx.Rollback()
Log()
return
}
if expected {
tx.COmmit()
} else {
tx.Rollback()
}
Vi GORM framework
gorm is an ORM framework written in Go language. The download method is as follows:
go get github.com/jinzhu/gorm
Official documents (very easy to use): https://gorm.io/zh_CN/
Connect to MySQL:
package main
import (
"github.com/jinzhu/gorm"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dsn := "username:password@tcp(host:port)/go_mysql_test"
db, err := gorm.Open("mysql", dsn) // Change to Gorm Open
if err != nil {
panic(err)
}
defer db.Close()
}
If you want to set the number of MySQL connections, you can use dB Setmaxidleconns() and other methods are used in the same way as the above sql package
VII GORM model
gorm's model is a standard go struct, which corresponds to a table structure in the database. The definition method is as follows:
type User struct {
// id int64 `gorm:"cloumn:id;primarykey" ` of course, you can also specify the primary key in this way
sex string `gorm:"column:sex"` // gorm's tag+column specifies the column, disdaining the default snake naming
name string `gorm:"column:name"`
gorm.Model // The ID, creation time, update time and deletion time are stored in this Model
}
// Specify the table name. If this method is not written, the default is the serpentine plural to indicate that here is users
func (user *User) TableName() string {
return "user"
}
It should be noted that gorm tag supports many kinds of tags, but if it is not suitable for automatic migration and automatic creation, most things are not needed. (automatic migration is not recommended)
Automatically migrate the cousin structure. At run time, the table will be modified according to the defined model. If there are fewer fields, the previous data will not change and the subsequent data will not change. If the fields are added, the following columns will be added:
db.AutoMigrate(&User{})
IX GORM insertion
user := &User{Sex: "male", Name: "Lula La"}
result := db.Create(user) // This method returns three values
// First value: error
if err := result.Error; err != nil {
fmt.Println(err.Error())
} else {
fmt.Println(result.RowsAffected) // Second value: number of affected rows
}
// The third value is LastInsertId, in user In ID
Large amount of data can be inserted in batch:
users := []User{{Sex: "male", Name: "Lula La"}, {Sex: "female", Name: "hear me roar"}}
db.Create(&users) // The return value is the same as above
X GORM query operation
var user User
if err := db.Where("sex=?", "male").First(&user).Error; err != nil {
fmt.Println(err.Error())
}
fmt.Println(user)
users := make([]*User, 0)
var count int64
db.Where("sex=?", "male").Order("id desc").Offset(0).Limit(2).Find(&users).Offset(1).Count(&count)
for _, user := range users {
fmt.Println(user)
}
fmt.Println(count)
Xi GORM update operation
var user User
if err := db.Where("sex=?", "male").First(&user).Error; err != nil {
fmt.Println(err.Error())
}
user.Sex = "female"
if err := db.Save(&user).Error; err != nil {
fmt.Println(err.Error())
}
If you want to specify update columns:
db.Model(&User{}).Where("name = ?", "jerry").Update("sex", "female")
Specify to update multiple columns:
db.Model(&user).Where("name", "jerry").Updates(User{Sex: "male"}) // Add other attributes to the User object. Note that if the attribute is zero, it will not be updated
XII GORM delete operation
if err := db.Where("sex=?", "female").Delete(&User{}).Error; err != nil {
fmt.Println(err.Error())
}
Batch delete:
if err := db.Where("name in (?)", []string{"jerry", "jerry1"}).Delete(&User{}).Error; err != nil {
fmt.Println(err.Error())
}
All deletions here are soft deletions, that is, set deleted_at field. If you want to permanently delete it, you need to use:
db.Unscoped().Delete(&User{})
XIII GORM transactions
Like the native writing method, the acquisition method is dB Begin()