Go操作Mysql数据库 使用Go操作MySQL等数据库，一般有两种方式：一是使用database/sql接口，直接在代码里硬编码sql语句；二是使用gorm，即对象关系映射的方式在代码里抽象的操作数据库。一般推荐使用第二种方式。
使用database/sql接口
Go没有内置的驱动支持任何数据库，但是Go定义了database/sql接口，用户可以基于驱动接口开发相应数据库的驱动。但缺点是，直接用 github.com/go-sql-driver/mysql 访问数据库都是直接写 sql，取出结果然后自己拼成对象，使用上面不是很方便，可读性也不好。
下载包
go get github.com/go-sql-driver/mysql
源码如下：
package mainimport ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "time" )//数据库连接信息 const ( USERNAME = "root" PASSWORD = "123qwe" NETWORK= "tcp" SERVER= "127.0.0.1" PORT= 3306 DATABASE = "test" )//user表结构体定义 type User struct { Idint`json:"id" form:"id"` Usernamestring `json:"username" form:"username"` Passwordstring `json:"password" form:"password"` Statusint`json:"status" form:"status"` // 0 正常状态， 1删除 Createtime int64`json:"createtime" form:"createtime"` }func CreateTable(DB *sql.DB) { sql := `CREATE TABLE IF NOT EXISTS users( id INT(4) PRIMARY KEY AUTO_INCREMENT NOT NULL, username VARCHAR(64), password VARCHAR(64), status INT(4), createtime INT(10) ); `if _, err := DB.Exec(sql); err != nil { fmt.Println("create table failed:", err) return } fmt.Println("create table successd") }//插入数据 func InsertData(DB *sql.DB) { result, err := DB.Exec("insert INTO users(username,password) values(?,?)", "demo", "123qwe") if err != nil { fmt.Printf("Insert data failed,err:%v", err) return } lastInsertID, err := result.LastInsertId() //获取插入数据的自增ID if err != nil { fmt.Printf("Get insert id failed,err:%v", err) return } fmt.Println("Insert data id:", lastInsertID)rowsaffected, err := result.RowsAffected() //通过RowsAffected获取受影响的行数 if err != nil { fmt.Printf("Get RowsAffected failed,err:%v", err) return } fmt.Println("Affected rows:", rowsaffected) }//查询单行 func QueryOne(DB *sql.DB) { user := new(User) //用new()函数初始化一个结构体对象 row := DB.QueryRow("select id,username,password from users where id=?", 1) //row.scan中的字段必须是按照数据库存入字段的顺序，否则报错 if err := row.Scan(&user.Id, &user.Username, &user.Password); err != nil { fmt.Printf("scan failed, err:%v\n", err) return } fmt.Println("Single row data:", *user) }//查询多行 func QueryMulti(DB *sql.DB) { user := new(User) rows, err := DB.Query("select id,username,password from users where id = ?", 2)defer func() { if rows != nil { rows.Close() //关闭掉未scan的sql连接 } }() if err != nil { fmt.Printf("Query failed,err:%v\n", err) return } for rows.Next() { err = rows.Scan(&user.Id, &user.Username, &user.Password) //不scan会导致连接不释放 if err != nil { fmt.Printf("Scan failed,err:%v\n", err) return } fmt.Println("scan successd:", *user) } }//更新数据 func UpdateData(DB *sql.DB) { result, err := DB.Exec("UPDATE users set password=? where id=?", "111111", 1) if err != nil { fmt.Printf("Insert failed,err:%v\n", err) return } fmt.Println("update data successd:", result)rowsaffected, err := result.RowsAffected() if err != nil { fmt.Printf("Get RowsAffected failed,err:%v\n", err) return } fmt.Println("Affected rows:", rowsaffected) }//删除数据 func DeleteData(DB *sql.DB) { result, err := DB.Exec("delete from users where id=?", 1) if err != nil { fmt.Printf("Insert failed,err:%v\n", err) return } fmt.Println("delete data successd:", result)rowsaffected, err := result.RowsAffected() if err != nil { fmt.Printf("Get RowsAffected failed,err:%v\n", err) return } fmt.Println("Affected rows:", rowsaffected) }func main() { conn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE) fmt.Println("conn: ", conn) DB, err := sql.Open("mysql", conn) if err != nil { fmt.Println("connection to mysql failed:", err) return }DB.SetConnMaxLifetime(100 * time.Second) //最大连接周期，超时的连接就close DB.SetMaxOpenConns(100)//设置最大连接数 CreateTable(DB) InsertData(DB) QueryOne(DB) QueryMulti(DB) UpdateData(DB) DeleteData(DB) }
运行结果如下：
[root@localhost mysql]# go run main.go conn:root:123qwe@tcp(127.0.0.1:3306)/test create table successd Insert data id: 2 Affected rows: 1 scan failed, err:sql: no rows in result set scan successd: {2 demo 123qwe 0 0} update data successd: {0xc0000d8000 0xc0000d60b0} Affected rows: 0 delete data successd: {0xc0000d8000 0xc000016330} Affected rows: 0
【golang|golang mysql demo】转载于:https://www.cnblogs.com/wangjq19920210/p/11577314.html
