go连接和使用mysql
一.依赖下载
go get github.com/go-sql-driver/mysql
二.连接mysql
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func mian() {
db, err := sql.Open("mysql", "dbname:dbpassword@tcp(ipaddress:port)/databsename")
if err != nil {
panic(err.Error())
}
fmt.Println(db)
}
三.创建mysql表
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func mian() {
db, err := sql.Open("mysql", "root:@tcp(localhost:3306)/ceshi")
if err != nil {
panic(err.Error())
}
sql := `
CREATE TABLE test(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(64) NOT NULL COMMENT '姓名',
password varchar(32) NOT NULL COMMENT '密码',
email varchar(32) NOT NULL COMMENT '电子邮箱',
PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8 COMMENT='测试表';
`
_, err = db.Exec(strings.Trim(sql, " "))
if err != nil {
panic(err.Error())
}
}
四.使用mysql事物
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func mian() {
db, err := sql.Open("mysql", "root:@tcp(localhost:3306)/ceshi")
if err != nil {
panic(err.Error())
}
begin, err := db.Begin()
if err != nil {
panic(err.Error())
}
sql := "INSERT INTO test (`name`, `password`, `email`) VALUES(?,?,?)"
stmt, err := begin.Prepare(sql)
if err != nil {
begin.Rollbak()
panic(err.Error())
}
_, err = stmt.Exec("王武", "a123456", "csdn@dn.com")
if err != nil {
begin.Rollbak()
panic(err.Error())
}
begin.Commit()
}
五.查询数据
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root:@tcp(localhost:3306)/ceshi")
if err != nil {
panic(err.Error())
}
begin, err := db.Begin()
if err != nil {
panic(err.Error())
}
sql := "select id, name, password, email from test where id = ? for update"
row := t.QueryRow(sql, 2)
var id int
var name string
var password string
var email string
err = row.Scan(&id, &name, &password, &email)
if err != nil {
t.Rollback()
panic(err.Error())
}
t.Commit()
fmt.Println(id, name, password, email)
sql := "select id, name, password, email from test"
rows, err := t.Query(sql)
if err != nil {
t.Rollback()
panic(err.Error())
}
users := make([]map[string]interface{}, 0)
for rows.Next() {
maps := make(map[string]interface{}, 4)
var id int
var name string
var password string
var email string
err = rows.Scan(&id, &name, &password, &email)
maps["id"] = id
maps["name"] = name
maps["password"] = password
maps["email"] = email
if err != nil {
t.Rollback()
panic(err.Error())
}
users = append(users, maps)
}
t.Commit()
for _, v := range users {
fmt.Printf("id: %d, name: %s, password: %s, email: %s \n", v["id"], v["name"], v["password"], v["email"])
}
}
六.删除数据
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root:@tcp(localhost:3306)/ceshi")
if err != nil {
panic(err.Error())
}
begin, err := db.Begin()
if err != nil {
panic(err.Error())
}
sql := "delete from test where id = ?"
_, err = t.Exec(sql, 1)
if err != nil {
t.Rollback()
panic(err.Error())
}
t.Commit()
fmt.Println("删除成功")
}