环境筹备
# MySQL筹备工作$ sudo service mysql start * Starting MySQL database server mysqld ...done.$ mysql -uroot -e "create database test;"$ mysql -urootMariaDB [test]> CREATE TABLE `person` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(260) DEFAULT NULL, -> `age` int(28) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.014 sec)# 导包$ go get github.com/go-sql-driver/mysql$ go get github.com/jmoiron/sqlx
连贯数据库
$ mkdir mysql-demo && cd mysql-demo && go mod init mysql-demo$ vi main.go
// main.gopackage mainimport( "fmt" "github.com/jmoiron/sqlx" _ "github.com/go-sql-driver/mysql")func main(){ db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test") if err != nil{ panic(err) } defer db.Close() fmt.Println("connect to MySQL success...")}
$ go run main.go connect to MySQL success...
Insert 操作
// main.gopackage mainimport( "fmt" "github.com/jmoiron/sqlx" _ "github.com/go-sql-driver/mysql")var ( Db *sqlx.DB)func init(){ db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test") if err != nil{ fmt.Println("connect to MySQL failed...") panic(err) } // defer db.Close() Db = db fmt.Println("connect to MySQL success...")}func main(){ defer Db.Close() r, err := Db.Exec("insert into person(name, age)values(?,?)","xiaoming","18") if err!=nil{ fmt.Println("exec failed, ", err) panic(err) } id, err := r.LastInsertId() if err != nil { fmt.Println("exec failed, ", err) return } fmt.Println("insert succ:", id)}
$ go run main.go connect to MySQL success...insert succ: 1
Select 操作
// main.gopackage mainimport( "fmt" "github.com/jmoiron/sqlx" _ "github.com/go-sql-driver/mysql")var ( Db *sqlx.DB)type Person struct { Id int `db:"id"` Name string `db:"name"` Age string `db:"age"`}func init(){ db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test") if err != nil{ fmt.Println("connect to MySQL failed...") panic(err) } // defer db.Close() Db = db fmt.Println("connect to MySQL success...")}func main(){ defer Db.Close() var p []Person err := Db.Select(&p, "select id,name,age from person where id = ?", 1) if err!=nil{ fmt.Println("exec failed, ", err) panic(err) } fmt.Println("select succ:", p)}
$ go run main.go connect to MySQL success...select succ: [{1 xiaoming 18}]
Update操作
package mainimport( "fmt" "github.com/jmoiron/sqlx" _ "github.com/go-sql-driver/mysql")var ( Db *sqlx.DB)func init(){ db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test") if err != nil{ fmt.Println("connect to MySQL failed...") panic(err) } // defer db.Close() Db = db fmt.Println("connect to MySQL success...")}func main(){ defer Db.Close() res, err := Db.Exec("update person set name=? where id=?", "xiaobai", 1) if err != nil { fmt.Println("exec failed, ", err) return } row, err := res.RowsAffected() if err != nil { fmt.Println("rows failed, ",err) } fmt.Println("update succ:",row)}
$ go run main.go connect to MySQL success...update succ: 1
Delete 操作
package mainimport( "fmt" "github.com/jmoiron/sqlx" _ "github.com/go-sql-driver/mysql")var ( Db *sqlx.DB)func init(){ db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test") if err != nil{ fmt.Println("connect to MySQL failed...") panic(err) } // defer db.Close() Db = db fmt.Println("connect to MySQL success...")}func main(){ defer Db.Close() res, err := Db.Exec("delete from person where id=?", 1) if err != nil { fmt.Println("exec failed, ", err) return } row, err := res.RowsAffected() if err != nil { fmt.Println("rows failed, ",err) } fmt.Println("delete succ:",row)}
$ go run main.go connect to MySQL success...delete succ: 1
MySQL事务
事务具备四个个性(ACID)：原子性 一致性 隔离性 持久性开启事务 Db.Begin()提交事务 Db.Commit()回滚事务 Db.Rollback()
package mainimport( "fmt" "github.com/jmoiron/sqlx" _ "github.com/go-sql-driver/mysql")var ( Db *sqlx.DB)func init(){ db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test") if err != nil{ fmt.Println("connect to MySQL failed...") panic(err) } // defer db.Close() Db = db fmt.Println("connect to MySQL success...")}func main(){ defer Db.Close() // begin transaction conn, err := Db.Begin() if err != nil { fmt.Println("begin failed :", err) return } r, err := conn.Exec("insert into person(name, age)values(?, ?)", "xiaoming", 18) if err != nil { fmt.Println("exec failed, ", err) conn.Rollback() // rollback transaction return } id, err := r.LastInsertId() if err != nil { fmt.Println("exec failed, ", err) conn.Rollback() // rollback transaction return } fmt.Println("insert succ:", id) r, err = conn.Exec("insert into person(name, age)values(?, ?)", "xiaohong", 18) if err != nil { fmt.Println("exec failed, ", err) conn.Rollback() // rollback transaction return } id, err = r.LastInsertId() if err != nil { fmt.Println("exec failed, ", err) conn.Rollback() // rollback transaction return } fmt.Println("insert succ:", id) conn.Commit() // commit transaction}
$ go run main.go connect to MySQL success...insert succ: 2insert succ: 3