Go操作MySQL

连接

初始化连接

sql.DB是表示连接的数据库对象（结构体实例），它保存了连接数据库相关的所有信息。它内部维护着一个具有零到多个底层连接的连接池，它可以安全地被多个goroutine同时使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
	// DSN:Data Source Name
	dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
	// 不会校验账号密码是否正确
	// 给全局变量赋值，然后在main函数中使用全局变量db
	db, err = sql.Open("mysql", dsn) // 使用MySQL驱动
	if err != nil {
		return err
	}
	// 尝试与数据库建立连接（校验dsn是否正确）
	err = db.Ping()
	if err != nil {
		return err
	}
	return nil
}

func main() {
	err := initDB() // 调用输出化数据库的函数
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	fmt.Printf("init db success!!!")
}

SetMaxOpenConns设置与数据库建立连接的最大数目。 SetMaxIdleConns设置连接池中的最大闲置连接数。

CRUD操作

查询数据

单行查询

单行查询db.QueryRow()执行一次查询，并期望返回最多一行结果（即Row）。QueryRow总是返回非nil的值，直到返回值的Scan方法被调用时，才会返回被延迟的错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

type user struct {
	id   int
	age  int
	name string
}

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
	// DSN:Data Source Name
	dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
	// 不会校验账号密码是否正确
	// 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
	db, err = sql.Open("mysql", dsn) // 使用MySQL驱动
	if err != nil {
		return err
	}
	// 尝试与数据库建立连接（校验dsn是否正确）
	err = db.Ping()
	if err != nil {
		return err
	}

	db.SetConnMaxLifetime(time.Second * 10)
	db.SetMaxOpenConns(200) // 最大链接数
	db.SetMaxIdleConns(10)  // 最大空闲链接数
	return nil
}

// 查询单条数据示例
func queryRowDemo() {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

func main() {
	err := initDB() // 调用输出化数据库的函数
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	fmt.Printf("init db success!!!\n")
	queryRowDemo()
}

多行查询

多行查询db.Query()执行一次查询，返回多行结果（即Rows），一般用于执行select命令。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

type user struct {
	id   int
	age  int
	name string
}

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
	// DSN:Data Source Name
	dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
	// 不会校验账号密码是否正确
	// 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
	db, err = sql.Open("mysql", dsn) // 使用MySQL驱动
	if err != nil {
		return err
	}
	// 尝试与数据库建立连接（校验dsn是否正确）
	err = db.Ping()
	if err != nil {
		return err
	}

	db.SetConnMaxLifetime(time.Second * 10)
	db.SetMaxOpenConns(200) // 最大链接数
	db.SetMaxIdleConns(10)  // 最大空闲链接数
	return nil
}

// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	rows, err := db.Query(sqlStr, 1)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}

	// 关闭rows释放持有的数据库连接
	defer rows.Close()

	// 循环读取结果集中的数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Printf("scan failed err:%v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}
func main() {
	err := initDB() // 调用输出化数据库的函数
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	fmt.Printf("init db success!!!\n")

	queryMultiRowDemo()

}

插入数据

插入、更新和删除操作都使用Exec方法。

Exec执行一次命令（包括查询、删除、更新、插入等），返回的Result是对已执行的SQL命令的总结。参数args表示query中的占位参数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package main

import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "time"
)

type user struct {
   id   int
   age  int
   name string
}

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
   // DSN:Data Source Name
   dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 不会校验账号密码是否正确
   // 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
   db, err = sql.Open("mysql", dsn) // 使用MySQL驱动
   if err != nil {
      return err
   }
   // 尝试与数据库建立连接（校验dsn是否正确）
   err = db.Ping()
   if err != nil {
      return err
   }

   db.SetConnMaxLifetime(time.Second * 10)
   db.SetMaxOpenConns(200) // 最大链接数
   db.SetMaxIdleConns(10)  // 最大空闲链接数
   return nil
}

// 插入数据
func insertRowDemo() {
   sqlStr := "insert into user(name, age) values(?, ?)"
   ret, err := db.Exec(sqlStr, "小贾", 22)

   if err != nil {
      fmt.Printf("insert failed, err:%v\n", err)
      return
   }
   theID, err := ret.LastInsertId() // 新插入的数据id
   if err != nil {
      fmt.Printf("get lastinsert ID failed, err%v\n", err)
      return
   }
   fmt.Printf("insert success, the id is %d.\n", theID)

}
func main() {
   err := initDB() // 调用输出化数据库的函数
   if err != nil {
      fmt.Printf("init db failed,err:%v\n", err)
      return
   }
   fmt.Printf("init db success!!!\n")

   insertRowDemo()

}

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package main

import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "time"
)

type user struct {
   id   int
   age  int
   name string
}

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
   // DSN:Data Source Name
   dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 不会校验账号密码是否正确
   // 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
   db, err = sql.Open("mysql", dsn) // 使用MySQL驱动
   if err != nil {
      return err
   }
   // 尝试与数据库建立连接（校验dsn是否正确）
   err = db.Ping()
   if err != nil {
      return err
   }

   db.SetConnMaxLifetime(time.Second * 10)
   db.SetMaxOpenConns(200) // 最大链接数
   db.SetMaxIdleConns(10)  // 最大空闲链接数
   return nil
}


// 更新数据
func updateRowDemo() {
   sqlStr := "update user set age=? where id = ?"
   ret, err := db.Exec(sqlStr, 21, 6)
   if err != nil {
      fmt.Printf("update failed, err:%v\n", err)
      return
   }
   n, err := ret.RowsAffected() // 操作影响的行数
   if err != nil {
      fmt.Printf("get RowsAffected failed, err:%v\n", err)
      return
   }
   fmt.Printf("update success, affected rows:%d\n", n)
}

func main() {
   err := initDB() // 调用输出化数据库的函数
   if err != nil {
      fmt.Printf("init db failed,err:%v\n", err)
      return
   }
   fmt.Printf("init db success!!!\n")

   updateRowDemo()

}

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package main

import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "time"
)

type user struct {
   id   int
   age  int
   name string
}

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
   // DSN:Data Source Name
   dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 不会校验账号密码是否正确
   // 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
   db, err = sql.Open("mysql", dsn) // 使用MySQL驱动
   if err != nil {
      return err
   }
   // 尝试与数据库建立连接（校验dsn是否正确）
   err = db.Ping()
   if err != nil {
      return err
   }

   db.SetConnMaxLifetime(time.Second * 10)
   db.SetMaxOpenConns(200) // 最大链接数
   db.SetMaxIdleConns(10)  // 最大空闲链接数
   return nil
}


// 删除数据
func deleteRowDemo() {
   sqlStr := "delete from user where id = ?"
   ret, err := db.Exec(sqlStr, 6)
   if err != nil {
      fmt.Printf("delete failed, err:%v\n", err)
      return
   }
   n, err := ret.RowsAffected() // 操作影响的行数
   if err != nil {
      fmt.Printf("get RowsAffected failed, err:%v\n", err)
      return
   }
   fmt.Printf("delete success, affected rows:%d\n", n)
}

func main() {
   err := initDB() // 调用输出化数据库的函数
   if err != nil {
      fmt.Printf("init db failed,err:%v\n", err)
      return
   }
   fmt.Printf("init db success!!!\n")

   deleteRowDemo()

}

MySQL预处理

预处理过程

普通SQL语句执行过程：

  1. 客户端对SQL语句进行占位符替换得到完整的SQL语句。
  2. 客户端发送完整SQL语句到MySQL服务端
  3. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

预处理执行过程：

  1. 把SQL语句分成两部分，命令部分与数据部分。
  2. 先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。
  3. 然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。
  4. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

预处理的优点

  1. 优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。
  2. 避免SQL注入问题。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
package main

import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "time"
)

type user struct {
   id   int
   age  int
   name string
}

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
   // DSN:Data Source Name
   dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 不会校验账号密码是否正确
   // 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
   db, err = sql.Open("mysql", dsn) // 使用MySQL驱动
   if err != nil {
      return err
   }
   // 尝试与数据库建立连接（校验dsn是否正确）
   err = db.Ping()
   if err != nil {
      return err
   }

   db.SetConnMaxLifetime(time.Second * 10)
   db.SetMaxOpenConns(200) // 最大链接数
   db.SetMaxIdleConns(10)  // 最大空闲链接数
   return nil
}

// 预处理插入示例
func prepareInsertDemo() {
   sqlStr := "insert into user(name, age) values (?,?)"
   stmt, err := db.Prepare(sqlStr) //Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态用于之后的查询和命令
   if err != nil {
      fmt.Printf("prepare failed, err:%v\n", err)
      return
   }
   defer stmt.Close()
   _, err = stmt.Exec("小贾", 18)
   if err != nil {
      fmt.Printf("insert failed, err:%v\n", err)
      return
   }
   _, err = stmt.Exec("小刘", 18)
   if err != nil {
      fmt.Printf("insert failed, err:%v\n", err)
      return
   }
   fmt.Println("insert success.")
}

func main() {
   err := initDB() // 调用输出化数据库的函数
   if err != nil {
      fmt.Printf("init db failed,err:%v\n", err)
      return
   }
   fmt.Printf("init db success!!!\n")

   prepareInsertDemo()

}

MySQL事务

事务：一个最小的不可再分的工作单元。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务的四个特性（ACID）

  • A：原子性（Atomicity，或称不可分割性）
  • C：一致性（Consistency）
  • I：隔离性（Isolation，又称独立性）
  • D：持久性（Durability）
条件 解释
原子性 一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

例子

  • 开始事务：func (db *DB) Begin() (*Tx, error)
  • 提交事务：func (tx *Tx) Commit() error
  • 回滚事务：func (tx *Tx) Rollback() error

更新数据库要么都成功要么都失败。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
// 事务操作示例
func transactionDemo() {
	tx, err := db.Begin() // 开启事务
	if err != nil {
		if tx != nil {
			tx.Rollback() // 回滚
		}
		fmt.Printf("begin trans failed, err:%v\n", err)
		return
	}
	sqlStr1 := "Update user set age=30 where id=?"
    
	ret1, err := tx.Exec(sqlStr1, 2)
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec sql1 failed, err:%v\n", err)
		return
	}
    // 返回受影响的行数
	affRow1, err := ret1.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
		return
	}

	sqlStr2 := "Update user set age=40 where id=?"
	ret2, err := tx.Exec(sqlStr2, 3)
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec sql2 failed, err:%v\n", err)
		return
	}
	affRow2, err := ret2.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
		return
	}

	fmt.Println(affRow1, affRow2)
	if affRow1 == 1 && affRow2 == 1 {
		fmt.Println("事务提交啦...")
		tx.Commit() // 提交事务
	} else {
		tx.Rollback()
		fmt.Println("事务回滚啦...")
	}

	fmt.Println("exec trans success!")
}