go 的 mysql 的简单操作
关于 sql：https://studygolang.com/articles/3022
异常处理： http://www.jianshu.com/p/f30da01eea97
一、数据库的连接及初始化
// demo_db.go
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
var (
db *sql.DB
)
func check(err error) {
if err != nil {
fmt.Println(err)
}
}
func InitDB(mydb *sql.DB) {
db = mydb
}
func main() {
mydb, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/imooc")
check(err)
defer mydb.Close()
InitDB(mydb)
fmt.Println(db)
}
二、增删改查
// demo_method.go
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
var (
db *sql.DB
)
func check(err error) {
if err != nil {
fmt.Println(err)
}
}
func InitDB(mydb *sql.DB) {
db = mydb
}
func DemoExec() {
sql_insert := "insert user set username=?"
result, err := db.Exec(sql_insert, "xiaoming")
check(err)
rows, err := result.RowsAffected()
check(err)
fmt.Printf("rows affected: %d\n", rows)
}
func DemoQuery() {
sql_query := "select acctid from account where money=?"
rows, err := db.Query(sql_query, 2000)
defer rows.Close()
check(err)
for rows.Next() {
var id int
if err := rows.Scan(&id); err != nil {
fmt.Println(err)
}
fmt.Printf("id: %d\n", id)
}
}
func DemoQueryRow() {
var id int
sql_query := "select acctid from account where money=?"
row := db.QueryRow(sql_query, 2000)
err := row.Scan(&id)
check(err)
fmt.Printf("id: %d\n", id)
}
func DemoPrepare() {
stmt, err := db.Prepare("select username from user where userid=?")
check(err)
rows, err := stmt.Query(3)
defer rows.Close()
check(err)
for rows.Next() {
var username string
if err := rows.Scan(&username); err != nil {
fmt.Println(err)
}
fmt.Printf("username: %s\n", username)
}
}
func main() {
mydb, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/imooc")
check(err)
defer mydb.Close()
InitDB(mydb)
DemoExec()
DemoQuery()
DemoQueryRow()
DemoPrepare()
}
三、事务
// demo_transaction.go package main
import ( "database/sql" _ "github.com/go-sql-driver/mysql" "fmt" "log" ) var ( db *sql.DB tx *sql.Tx ) func check(err error) { if err != nil { fmt.Println(err) } } func InitDB(mydb *sql.DB) { db = mydb } func InitTx(mytx *sql.Tx) { tx = mytx } func CheckAccountAvaiable(acctid int) { sql := "select * from account where acctid=?" rows,err := tx.Query(sql, acctid) // 如果不调用rows.Close，conn无法回到 tx上 defer rows.Close() if err != nil { log.Println(err) panic(fmt.Sprintf("查询错误，账号%d不可得!", acctid)) } if !rows.Next() { panic(fmt.Sprintf("查询失败，账号%d不可得!", acctid)) } } func HasEnoughMoney(acctid, money int) { sql := "select * from account where acctid=? and money>=?" rows,err := tx.Query(sql, acctid, money) defer rows.Close() if err != nil { log.Println(err) panic(fmt.Sprintf("用户%d余额查询失败！", acctid)) } if !rows.Next() { panic(fmt.Sprintf("用户%d余额不足！", acctid)) } } func ReduceMoney(acctid, money int) { sql := "update account set money = money-? where acctid = ?" _,err := tx.Exec(sql, money, acctid) if err != nil { log.Println(err) panic(fmt.Sprintf("用户%d减款失败！", acctid)) } } func AddMoney(acctid, money int) { sql := "update account set money =money+? where acctid = ?" _,err := tx.Exec(sql, money, acctid) if err != nil { log.Println(err) panic(fmt.Sprintf("用户%d加款失败！", acctid)) } } func Transfer(source_acctid, target_acctid, money int) { tx,err := db.Begin() check(err) InitTx(tx) defer func() { if err := recover(); err != sql.ErrTxDone && err != nil { fmt.Printf("出错了 ERR:%s\n", err) tx.Rollback() } else { tx.Commit() fmt.Printf("%d转给%d一共%d元, 转账成功！",source_acctid, target_acctid, money) } }() CheckAccountAvaiable(source_acctid) CheckAccountAvaiable(target_acctid) HasEnoughMoney(source_acctid, money) ReduceMoney(source_acctid, money) AddMoney(target_acctid, money) } func main() { mydb,err := sql.Open("mysql", "root:root@tcp(localhost:3306)/imooc") check(err) defer mydb.Close() InitDB(mydb) Transfer(4, 3, 500) }
sql 脚本
$ source imooc.sql
imooc.sql
-- MySQL dump 10.16 Distrib 10.1.28-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: imooc -- ------------------------------------------------------ -- Server version 10.1.28-MariaDB /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `account` -- DROP TABLE IF EXISTS `account`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `account` ( `acctid` int(11) NOT NULL AUTO_INCREMENT, `money` int(6) DEFAULT NULL, PRIMARY KEY (`acctid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `account` -- LOCK TABLES `account` WRITE; /*!40000 ALTER TABLE `account` DISABLE KEYS */; INSERT INTO `account` VALUES (1,2000),(2,1300),(3,2800),(4,4000); /*!40000 ALTER TABLE `account` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `user` -- DROP TABLE IF EXISTS `user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user` ( `userid` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(11) DEFAULT NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user` -- LOCK TABLES `user` WRITE; /*!40000 ALTER TABLE `user` DISABLE KEYS */; INSERT INTO `user` VALUES (1,'lisi'),(2,'zhangsan'),(3,'liuqi'),(4,'white'),(5,'xiaoming'),(6,'xiaoming'),(7,'xiaoming'),(8,'xiaoming'),(9,'xiaoming'),(10,'xiaoming'),(11,'xiaoming'),(12,'xiaoming'),(13,'xiaoming'),(14,'xiaoming'),(15,'xiaoming'),(16,'xiaoming'),(17,'xiaoming'); /*!40000 ALTER TABLE `user` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2017-10-15 20:27:53
