go mysql id为0_go 的 mysql 的简单操作

一、数据库的连接及初始化

// 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: %dn", 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: %dn", 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: %dn", 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: %sn", 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:%sn", 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 NULLAUTO_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 NULLAUTO_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