Go 语言：使用 mysql 包操作 MySQL

#Go 语言

MySQL 用户名 root，密码 123456。

创建数据库

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	db, err := sql.Open("mysql", "root:123456@/")
	checkError(err)
	result, err := db.Exec("create database blog;")
	checkError(err)
	rowAffected, _ := result.RowsAffected()
	lastInsertId, _ := result.LastInsertId()
	fmt.Printf("rowAffected -> %v, lastInsertId -> %v", rowAffected, lastInsertId)
}

运行结果：

rowAffected -> 1, lastInsertId -> 0

建表

示例1：连接时直接指定数据库，然后建表：

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	db, err := sql.Open("mysql", "root:123456@/blog")  // 指定数据库 blog
	checkError(err)
	sql := `
	create table user_info (
            id bigint unsigned not null auto_increment,
            name varchar(45) not null default '',
            primary key (id)
	) engine = InnoDB default charset = utf8mb4;`

	result, err := db.Exec(sql)
	checkError(err)
	rowAffected, _ := result.RowsAffected()
	lastInsertId, _ := result.LastInsertId()
	fmt.Printf("rowAffected -> %v, lastInsertId -> %v", rowAffected, lastInsertId)
}

运行结果：

rowAffected -> 0, lastInsertId -> 0

示例2：连接后指定数据库

package main

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

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	db, err := sql.Open("mysql", "root:123456@/")
	checkError(err)

	_, err = db.Exec("use blog;")
	checkError(err)

	sql := `
	create table user_info_2 (
            id bigint unsigned not null auto_increment,
            name varchar(45) not null default '',
            primary key (id)
	) engine = InnoDB default charset = utf8mb4;`
	_, err = db.Exec(sql)
	checkError(err)
}

插入数据

package main

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

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	db, err := sql.Open("mysql", "root:123456@/blog")
	checkError(err)
	result, err := db.Exec("insert into user_info (name) values (?)", "name001")
	checkError(err)
	rowAffected, _ := result.RowsAffected()
	lastInsertId, _ := result.LastInsertId()
	fmt.Printf("rowAffected -> %v, lastInsertId -> %v", rowAffected, lastInsertId)
}

执行结果：

rowAffected -> 1, lastInsertId -> 1

查询数据

package main

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

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	db, err := sql.Open("mysql", "root:123456@/blog")
	checkError(err)
	result, err := db.Query("select id, name from user_info")
	checkError(err)

	for result.Next() {
		var id int
		var name string
		result.Scan(&id, &name)
		fmt.Printf("id -> %v, name -> %v", id, name)
	}

}

执行结果:

id -> 1, name -> name001

查询返回数据的列名

package main

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

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	db, err := sql.Open("mysql", "root:123456@/blog")
	checkError(err)
	result, err := db.Query("select * from user_info")
	checkError(err)
	columns, err := result.Columns()
	checkError(err)
	fmt.Printf("columns 类型: %T\n", columns)
	fmt.Printf("columns 值: %v\n", columns)
}

运行结果:

columns 类型: []string
columns 值: [id name]

查询返回数据的列名、MySQL类型、Go类型

package main

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

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	db, err := sql.Open("mysql", "root:123456@/blog")
	checkError(err)
	defer db.Close()
	result, err := db.Query("select * from user_info")
	checkError(err)
	columnTypeArray, err := result.ColumnTypes()
	checkError(err)

	for _, columnType := range columnTypeArray {
		fmt.Printf("字段名: %v, 数据库类型: %v, Go语言默认映射类型: %v\n",
			columnType.Name(),  columnType.DatabaseTypeName(), columnType.ScanType())

	}
}

执行结果：

字段名: id, 数据库类型: BIGINT, Go语言默认映射类型: uint64
字段名: name, 数据库类型: VARCHAR, Go语言默认映射类型: sql.RawBytes

事务

package main

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

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	db, err := sql.Open("mysql", "root:123456@/blog")
	checkError(err)
	defer db.Close()

	// 清理 user_info 表数据
	db.Exec("truncate table user_info;")

	// 开启事务 tx1
	tx1, err := db.Begin()
	result, err := tx1.Exec("insert into user_info (name) values (?)", "name002")
	checkError(err)
	rowAffected, _ := result.RowsAffected()
	lastInsertId, _ := result.LastInsertId()
	fmt.Printf("tx1 rowAffected -> %v, lastInsertId -> %v\n", rowAffected, lastInsertId)
	// 提交事务 tx1
	tx1.Commit()

	// 开启事务 tx2
	tx2, err := db.Begin()
	result, err = tx2.Exec("insert into user_info (name) values (?)", "name003")
	checkError(err)
	rowAffected, _ = result.RowsAffected()
	lastInsertId, _ = result.LastInsertId()
	fmt.Printf("tx2 rowAffected -> %v, lastInsertId -> %v\n", rowAffected, lastInsertId)
	// 回滚事务 tx2
	tx2.Rollback()

	// 查询数据
	queryResult, err := db.Query("select id, name from user_info")
	checkError(err)
	for queryResult.Next() {
		var id int
		var name string
		queryResult.Scan(&id, &name)
		fmt.Printf("id -> %v, name -> %v\n", id, name)
	}

}

执行结果示例：

tx1 rowAffected -> 1, lastInsertId -> 1
tx2 rowAffected -> 1, lastInsertId -> 2
id -> 1, name -> name002

( 本文完 )