go操作mysql

go get -u github.com/go-sql-driver/mysqlfunc Open(driverName, dataSourceName string) (*DB, error) //第一个参数指定了数据库类型，第二个指定数据库的连接方式//创建数据库
CREATE DATABASE sql_test;
use sql_test；
//创建表
CREATE TABLE `user` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`name` VARCHAR(20) DEFAULT '',`age` INT(11) DEFAULT '0',PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

package mainimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql" //使用包的init()方法
)var db *sql.DB //设置一个全局的连接池// 初始化数据库,建立数据库连接,指定一个全局的连接池
func initdb() {// 数据库信息//用户名:密码@tcp(ip:端口)/数据库的名称dsn := "root:123456@tcp(127.0.0.1:3306)/goday10"// Open打开一个dirverName指定的数据库，dataSourceName指定数据源，一般包至少括数据库文件名和（可能的）连接信息。// 连接数据库db, err := sql.Open("mysql", dsn) //如果db是一个全局变量,那么这里就不能重复声明了if err != nil {fmt.Printf("dsn:%s err:%v\n", dsn, err)}err = db.Ping() //尝试连接校验用户名密码if err != nil {fmt.Printf("open %s failed, err:%s\n", dsn, err)}	db.SetMaxOpenConns(10) //设置数据库连接池的最大连接数db.SetMaxIdleConns(5) //设置最大空闲连接数fmt.Println("连接数据库成功")
}

type user struct {id   intname stringage  int
}// 查询单个记录
func queryOne(id int) {var u1 user// 1. 写查询单条记录的sql语句sqlStr := "select id,name,age from user where id=?;"// 2. 执行并拿到结果// 必须对rowObj对象调用Scan方法,因为该方法会释放数据库链接 // 从连接池里拿一个连接出来去数据库查询单条记录db.QueryRow(sqlStr, id).Scan(&u1.id, &u1.name, &u1.age)// 3.打印fmt.Printf("u1:%#v\n", u1)
}// 查询多个记录
func queryMore(n int) {var u1 user// 1. 定义sqlsqlStr := "select id,name,age from user where id>?;"// 2. 执行sqlrows, err := db.Query(sqlStr, n)if err != nil {fmt.Printf("exec %s query faild, err:%v\n", sqlStr, err)}// 3. 一定要关闭rowsdefer rows.Close()// 4. 循环取值for rows.Next() {err := rows.Scan(&u1.id, &u1.name, &u1.age)if err != nil {fmt.Printf("scan faild,err:%s\n", err)}fmt.Printf("u1:%#v\n", u1)}
}// 增加记录
func insertRow() {// 1. sqlsqlStr := `insert into user(name,age) values("lll",20);`// 2. execret, err := db.Exec(sqlStr)if err != nil {fmt.Printf("insert faild,err:%v\n", err)}// 3. 如果是插入数据的操作，能够拿到插入数据的idid, err := ret.LastInsertId()if err != nil {fmt.Printf("get id failed,err:%v\n", err)return}fmt.Println("id:", id)
}// 修改记录
func updateRow(newage, id int) {sqlStr := `update user set age=? where id =?;`ret, err := db.Exec(sqlStr, newage, id)if err != nil {fmt.Printf("update failed, err:%v\n", err)return}//获取影响的行数n, err := ret.RowsAffected()if err != nil {fmt.Printf("get id failed,err:%v\n", err)return}fmt.Printf("更新了%d行数据\n", n)
}// 删除记录
func deleteRow(id int) {sqlStr := `delete from user where id=?`ret, err := db.Exec(sqlStr, id)if err != nil {fmt.Printf("delete failed,err:%v\n", err)return}n, err := ret.RowsAffected()if err != nil {fmt.Printf("get id failed,err:%v\n", err)return}fmt.Printf("删除了%d行数据\n", n)
}

// 使用预处理插入多条数据
func prepareInsert() {sqlStr := `insert into user(name,age) values(?,?);`stmt, err := db.Prepare(sqlStr) // 把SQL语句先发给MySQL预处理一下if err != nil {fmt.Printf("prepare failed ,err:%v\n", err)return}defer stmt.Close()// 后续只需要拿到stmt去执行一些操作var m = map[string]int{"yw":  20,"lqx": 21,"xk":  33,}for k, v := range m {stmt.Exec(k, v)}
}

// mysql事务
func transactionDemo() {// 1. 开启事务tx, err := db.Begin()if err != nil {fmt.Printf("begin failed,err:%v\n", err)return}// 2. 执行多条sqlsqlStr1 := `update user set age=age-2 where id=3`sqlStr2 := `update user set age=age+2 where id=5`// 3. 执行sql1_, err = tx.Exec(sqlStr1)if err != nil {// 发生回滚tx.Rollback()fmt.Println("执行sql1出错,要回滚")return}// 4. 执行sql2_, err = tx.Exec(sqlStr2)if err != nil {// 发生回滚tx.Rollback()fmt.Println("执行sql2出错,要回滚")return}// 5. 都返回成功,就提交本次事务// 上面两步SQL都执行成功，就提交本次事务err = tx.Commit()if err != nil {// 要回滚tx.Rollback()fmt.Println("提交出错啦，要回滚！")return}fmt.Println("事务执行成功！")
}

package mainimport ("fmt"_ "github.com/go-sql-driver/mysql" // init()"github.com/jmoiron/sqlx"
)// Go连接MySQL示例
var db *sqlx.DB // 是一个连接池对象func initDB() (err error) {// 数据库信息// 用户名:密码@tcp(ip:端口)/数据库的名字dsn := "root:root@tcp(127.0.0.1:3306)/sql_test"// 连接数据库db, err = sqlx.Connect("mysql", dsn)if err != nil {return}db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数db.SetMaxIdleConns(5)  // 设置最大空闲连接数return
}type user struct {ID   intName stringAge  int
}// 插入数据
func queryRow() {// 查询单条记录sqlStr1 := `select id, name, age from user where id=1`var u userdb.Get(&u, sqlStr1)fmt.Printf("u:%#v\n", u)// 查询多条记录var userList []usersqlStr2 := `select id,name, age from user`err := db.Select(&userList, sqlStr2)if err != nil {fmt.Printf("select failed, err:%v\n", err)return}fmt.Printf("userList:%#v\n", userList)
}// 插入数据
func insertRowDemo() {sqlStr := "insert into user(name, age) values (?,?)"ret, err := db.Exec(sqlStr, "沙河小王子", 19)if err != nil {fmt.Printf("insert failed, err:%v\n", err)return}theID, err := ret.LastInsertId() // 新插入数据的idif err != nil {fmt.Printf("get lastinsert ID failed, err:%v\n", err)return}fmt.Printf("insert success, the id is %d.\n", theID)
}// 更新数据
func updateRowDemo() {sqlStr := "update user set age=? where id = ?"ret, err := db.Exec(sqlStr, 39, 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 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 transactionDemo() {tx, err := db.Beginx() // 开启事务if err != nil {if tx != nil {tx.Rollback()}fmt.Printf("begin trans failed, err:%v\n", err)return}sqlStr1 := "Update user set age=40 where id=?"tx.MustExec(sqlStr1, 2)sqlStr2 := "Update user set age=50 where id=?"tx.MustExec(sqlStr2, 4)err = tx.Commit() // 提交事务if err != nil {tx.Rollback() // 回滚fmt.Printf("commit failed, err:%v\n", err)return}fmt.Println("exec trans success!")
}
func main() {err := initDB()if err != nil {fmt.Printf("init DB failed, err:%v\n", err)}
}