go語言通過database/sql使用mysql資料庫，以及以及第三方sqlx擴充

mysql支援插件式的存儲引擎, 常見的存儲引擎有：MyISAM，InnoDB

MyISAM：
1. 查詢速度快
2. 隻支援表鎖
3. 不支援事務
InnoDB:
1. 整體速度快
2. 支援表鎖和行鎖
3. 支援事務

事務：多個SQL操作為一個整體執行

事務特點：ACID

原子性
一緻性
隔離性
持久性

索引：

原理：B樹和B+樹

需要掌握的知識點：索引類型命中分庫分表 SQL注入 SQL慢優化查詢 MYSQL主從讀寫分離

database/sql

原生支援連接配接池，是并發安全的

該标準庫沒有具體實作，隻列出第三方庫需要實作的具體内容

mysql驅動

go get github.com/go-sql-driver/mysql

連接配接資料庫

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 連接配接資料庫
	dsn:="root:【填入自己的資料庫密碼】@tcp(127.0.0.1:3306)/hello"
	// 連接配接資料庫
	db,err:=sql.Open("mysql",dsn)// 不會校驗使用者和密碼石是否正确，隻會校驗格式是否正确
	if err != nil {               // dsn格式不正确會報錯
		fmt.Printf(" %s invalid, err:%v\n",dsn,err)
		return
	}
	err=db.Ping()
	if err != nil {
		fmt.Printf("Open %s failed, err:%v\n",dsn,err)
		return
	}
    db.SetMaxOpenConns(10)// 最大連接配接數
	db.SetMaxIdleConns(3)// 最大空閑連接配接數
	fmt.Println("連接配接資料庫成功!")
}

增删改查

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB //一個連接配接池

type user struct{
	id int `db:"id"`
	name string `db:"name"`
	age int `db:"age"`
}
func initDB()(err error) {
	// 連接配接資料庫
	dsn:="root:[email protected](127.0.0.1:3306)/hello"
	// 連接配接資料庫
	db,err=sql.Open("mysql",dsn)// 不會校驗使用者和密碼石是否正确，隻會校驗格式是否正确
	if err != nil {               // dsn格式不正确會報錯
		//fmt.Printf(" %s invalid, err:%v\n",dsn,err)
		return 
	}
	err=db.Ping()
	if err != nil {
		//fmt.Printf("Open %s failed, err:%v\n",dsn,err)
		return 
	}
	// fmt.Println("連接配接資料庫成功!")
	db.SetMaxOpenConns(10)// 最大連接配接數
	db.SetMaxIdleConns(3)// 最大空閑連接配接數
	return
}

func queryone(id int)(u *user){
	u=&user{}
	// 1. 查詢單條記錄的sql語句
	sqlstr:="select id,name,age from user where id=?;"
	// 2.執行
	db.QueryRow(sqlstr,id).Scan(&u.id,&u.name,&u.age) // 從連接配接池取取一個連接配接出來，去資料庫查詢單條記錄,并調用scan拿結果
	// 3 傳回結果
	return u
}

func queryMore(n int){
	// 1. sql語句
	sqlstr:="select id,name,age from user where id > ?"
	// 2 執行
	rows, err := db.Query(sqlstr, n)
	if err!=nil{
		fmt.Printf(" exec %s query faile, err := %v",sqlstr,err)
		return
	}
	// 3 關閉資料庫連接配接
	defer rows.Close()
	// 循環取數
	for rows.Next(){
		var u1 user
		err:=rows.Scan(&u1.id,&u1.name,&u1.age)
		if err != nil{
			fmt.Printf("scan failed, err : %v\n",err)
		}
		fmt.Printf("user:%#v\n",u1)
	}
}

// 插入資料
func insert(name string,age int){
	// 1寫sql語句
	sqlstr:="insert into user(name,age) values(?,?)"
	// 2 執行exec
	ret,err:=db.Exec(sqlstr,name,age)
	if err != nil {
		fmt.Printf("insert failed, err : %v\n",err)
		return
	}
	// 插入資料的操作，會拿到操作資料的id值
	id,err:=ret.LastInsertId()
	if err != nil {
		fmt.Printf("get id failed, err : %v\n",err)
		return
	}
	fmt.Println("id",id)
}

// 更新資料
func updateRow(newAge int,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 main() {
	err:=initDB()
	if err != nil {
		fmt.Printf("init DB failed, err : %v\n", err)
	}
	fmt.Println("連接配接資料庫成功!")
	//insert("wangkai",400) 
	//queryone(2)
	//queryMore(3)
	 updateRow(34,2)
	//deleteRow(2)
}

mysql預處理

普通SQL語句執行過程：

用戶端對SQL語句進行占位符替換得到完整的SQL語句。
用戶端發送完整SQL語句到MySQL服務端
MySQL服務端執行完整的SQL語句并将結果傳回給用戶端。

預處理執行過程：

把SQL語句分成兩部分，指令部分與資料部分。
先把指令部分發送給MySQL服務端，MySQL服務端進行SQL預處理。
然後把資料部分發送給MySQL服務端，MySQL服務端對SQL語句進行占位符替換。
MySQL服務端執行完整的SQL語句并将結果傳回給用戶端。

優點：

優化MySQL伺服器重複執行SQL的方法，可以提升伺服器性能，提前讓伺服器編譯，一次編譯多次執行，節省後續編譯的成本。
避免SQL注入問題。

适用于批量處理類的操作。

Go 實作mysql預處理：

Prepare

方法會先将sql語句發送給MySQL服務端，傳回一個準備好的狀态用于之後的查詢和指令。傳回值可以同時執行多個查詢和指令。

// 預處理方式插入多條資料
func prepareInsert(){
	sqlstr:="insert into user(name,age) values(?,?)"
	stmt,err := db.Prepare(sqlstr)
	if err != nil {
		fmt.Printf("prepar failed, err : %v\n",err)
		return
	}
	defer stmt.Close()
	// 後續隻需要stmt 執行操作 
	var m= map[string]int{
		"dazhangwei":30,
		"lushuo":230,
		"zhangerwei":46,
	}
	for k,v := range m {
		stmt.Exec(k,v)
	}

}

Go語言實作事務

什麼是事務？

事務：一個最小的不可再分的工作單元；通常一個事務對應一個完整的業務(例如銀行賬戶轉賬業務，該業務就是一個最小的工作單元)，同時這個完整的業務需要執行多次的DML(insert、update、delete)語句共同聯合完成。A轉賬給B，這裡面就需要執行兩次update操作。

在MySQL中隻有使用了

Innodb

資料庫引擎的資料庫或表才支援事務。事務處理可以用來維護資料庫的完整性，保證成批的SQL語句要麼全部執行，要麼全部不執行。

事務的ACID

通常事務必須滿足4個條件（ACID）：原子性（Atomicity，或稱不可分割性）、一緻性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

條件	解釋
原子性	一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被復原（Rollback）到事務開始前的狀态，就像這個事務從來沒有執行過一樣。
一緻性	在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精确度、串聯性以及後續資料庫可以自發性地完成預定的工作。
隔離性	資料庫允許多個并發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導緻資料的不一緻。事務隔離分為不同級别，包括讀未送出（Read uncommitted）、讀送出（read committed）、可重複讀（repeatable read）和串行化（Serializable）。
持久性	事務處理結束後，對資料的修改就是永久的，即便系統故障也不會丢失。

事務相關方法

Go語言中使用以下三個方法實作MySQL中的事務操作。

開始事務

送出事務

復原事務

func transaction(){
	// 開啟事務
	tx,err := db.Begin()
	if err != nil {
		fmt.Printf("Transaction Begin failed, err: %v\n",err)
		return
	}
	// 執行多個sql操作
	sqlstr1 := "update user set age=age-2 where id=1"
	sqlstr2:="update user set age=age+2 where id=2"
	ret,err:=tx.Exec(sqlstr1)
	if err != nil{
		// 要復原
		tx.Rollback()
		fmt.Println("執行sql1出錯，要復原")
	}
	if rows,_:=ret.RowsAffected();rows==0{
		tx.Rollback()
		fmt.Println("執行sql1出錯，要復原")
	}
	ret,err=tx.Exec(sqlstr2)
	if err != nil{
		// 要復原
		tx.Rollback()
		fmt.Println("執行sql2出錯，要復原")
	}
	if rows,_:=ret.RowsAffected();rows==0{
		tx.Rollback()
		fmt.Println("執行sql2出錯，要復原")
	}
	 // 上面都執行成功，送出
	 err=tx.Commit()
	 if err != nil {
		 tx.Rollback()
		 fmt.Println("送出出錯，要復原")
	 }
	 fmt.Println("事務執行成功！")
}

sqlx 的使用

sqlx是一個go語言包，在内置database/sql包之上增加了很多擴充，簡化資料庫操作代碼的書寫

handle types

sqlx設計和database/sql使用方法是一樣的。包含有4種主要的handle types:

sqlx.DB: 和sql.DB相似，表示資料庫
sqlx.Tx: 和sql.Tx相似，表示transacion
sqlx.Stmt: 和sql.Stmt相似，表示prepared statement.
sqlx.NamedStmt: 表示prepared statement(支援named parameters)

所有的handler types都提供了對database/sql的相容，意味着當用調用sqlx.DB.Query時，可以直接替換為sql.DB.Query。這就使得sqlx可以很容易的加入到已有的資料庫項目中。

此外，sqlx還有兩個cursor類型:

sqlx.Rows 和sql.Rows類似，Queryx傳回。
sqlx.Row 和 sql.Row類似，QueryRowx傳回。

與官方包sql的差別

1初始化：

sql：

func initDB() (err error) {
	// 連接配接資料庫
	dsn := "root:[你的資料庫密碼]@tcp(127.0.0.1:3306)/hello"
	// 連接配接資料庫
	db, err = sqlx.Open("mysql", dsn) // 不會校驗使用者和密碼石是否正确，隻會校驗格式是否正确
	if err != nil {                  // dsn格式不正确會報錯
		//fmt.Printf(" %s invalid, err:%v\n",dsn,err)
		return
	}
	err = db.Ping()
	if err != nil {
		//fmt.Printf("Open %s failed, err:%v\n",dsn,err)
		return
	}
	// fmt.Println("連接配接資料庫成功!")
	db.SetMaxOpenConns(10) // 最大連接配接數
	db.SetMaxIdleConns(3)  // 最大空閑連接配接數
	return
}

sqlx:

func initDB() (err error) {
	// 連接配接資料庫
	dsn := "root:[你的資料庫密碼]@tcp(127.0.0.1:3306)/hello"
	// 連接配接資料庫
	
	db, err = sqlx.Connect("mysql",dsn) // 
	if err != nil {
		//fmt.Printf("connect failed, err : %v\n",err)
		return
	}
	// fmt.Println("連接配接資料庫成功!")
	db.SetMaxOpenConns(10) // 最大連接配接數
	db.SetMaxIdleConns(3)  // 最大空閑連接配接數
	return
}

主要差別是connect 相當于sqlx的open和ping的結合，下面是connect的代碼：

// Connect to a database and verify with a ping.
func Connect(driverName, dataSourceName string) (*DB, error) {
	db, err := Open(driverName, dataSourceName)
	if err != nil {
		return nil, err
	}
	err = db.Ping()
	if err != nil {
		db.Close()
		return nil, err
	}
	return db, nil
}

2 查詢語句

sqlx中的handle types實作了資料庫查詢相同的基本的操作文法。

Exec(…) (sql.Result,error) 和database/sql相比沒有改變
Query(…) (*sql.Rows, error) 和database/sql相比沒有改變
QueryRow(…) *sql.Row 和database/sql相比沒有改變

對内置文法的擴充

MustExec()sql.Result - Exec, but panic or error
Queryx(…) (*sqlx.Rows, error) - Query, but return an sqlx.Rows
QueryRows(…) *sqlx.Row - QueryRow, but return an sqlx.Row

擴充下面新的文法

Get(dest interface{},…) error
Select(dest interface{},…) error

因為官方的sql包query()和quertrow(）在将查詢到的資料傳遞到變量時，對于結構體變量，隻能通過scan（）方法逐一傳遞。sqlx包擴充了structscan()方法。可以對結果體變量直接傳值。

例子：

type Place struct {
    Country       string
    City          sql.NullString
    TelephoneCode int `db:"telcode"`
}
// 多條查詢
rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    var p Place
    err = rows.StructScan(&p)
}
// 單條查詢
var p Place
err := db.QueryRowx("SELECT city, telcode FROM place LIMIT 1").StructScan(&p)

get 和select文法

他們是上述查詢（query）和傳值掃描（scan）操作的集合。

可掃描的定義:

a value is scannable if it is not a struct, eg string , int
a value is scannable if it implements sql.Scanner
a value is scannable if it is a struct with no exported fields (eg. time.Time )

get和select使用

rows.Scan

方法掃描可掃描的資料，

rows.StructScan

掃描不可掃描的資料。get用于查詢單條資料，select用于查詢多條資料。

例子：

p := Place{}
pp := []Place{}
 
// this will pull the first place directly into p
err = db.Get(&p, "SELECT * FROM place LIMIT 1")
 
// this will pull places with telcode > 50 into the slice pp
err = db.Select(&pp, "SELECT * FROM place WHERE telcode > ?", 50)
 
// they work with regular types as well
var id int
err = db.Get(&id, "SELECT count(*) FROM place")
 
// fetch at most 10 place names
var names []string
err = db.Select(&names, "SELECT name FROM place LIMIT 10")

Get 和 Select 會關閉rows ，無需再調用rows.close()方法。

但是要注意的是，select會把查詢結果一次性全放到記憶體，如果查詢量較大的話，可以使用queryx和stuctscan結合的方法。

func Select(q Queryer, dest interface{}, query string, args ...interface{}) error {
	rows, err := q.Queryx(query, args...)
	if err != nil {
		return err
	}
	// if something happens here, we want to make sure the rows are Closed
	defer rows.Close()
	return scanAll(rows, dest, false)
}

3事務：

沒啥差別

4 預處理:

Preparex和get方法可用于預處理

stmt, err := db.Preparex(`SELECT * FROM place WHERE telcode=?`)
var p Place
err = stmt.Get(&p, 852)

5 in 查詢

因為database / sql不會檢查您的查詢，而是将您的參數直接傳遞給驅動程式，是以使用IN子句處理查詢變得困難。

SELECT * FROM users WHERE level IN (?);

？隻會接受一個單變量，但是更希望它接受一個可變數組。

var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)

可以先使用

sqlx.In

文法建構查詢語句和參數清單。

var levels = []int{4, 6, 7}
query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels) 
// sqlx.In returns queries with the `?` bindvar, we can rebind it for our backend
query = db.Rebind(query)
rows, err := db.Query(query, args...)

例子：

sqlstr, args, err := sqlx.In("select id,category_name,category_no from category where id in (?)", categoryIds)
	if err != nil {
		return
	}
	// 查詢
	err = DB.Select(&categoryList, sqlstr, args...)

##遇到的問題及解決方案

Q：go的time包的time.time類型與 mysql的date datetime 自動解析？

A：在dsn位址加上parseTime=true字段

Q：時差問題?

A:loc=local

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", 
    "root:6862273aliyun@tcp(39.97.229.151:3306)/weixiaobo?parseTime=true&loc=Local")

    var myTime time.Time
    rows, err := db.Query("SELECT current_timestamp()")
    fmt.Println(time.Now())
    if rows.Next() {
        if err = rows.Scan(&myTime); err != nil {
            panic(err)
        }
    }

    fmt.Println(myTime)
}

go語言通過database/sql使用mysql資料庫，以及以及第三方sqlx擴充go語言通過database/sql使用mysql資料庫，以及以及第三方sqlx擴充

go語言通過database/sql使用mysql資料庫，以及以及第三方sqlx擴充

database/sql

mysql驅動

連接配接資料庫

增删改查

mysql預處理

Go語言實作事務

什麼是事務？

事務的ACID

事務相關方法

sqlx 的使用

handle types

與官方包sql的差別

1初始化：

sql：

2 查詢語句

get 和select文法

3事務：

4 預處理:

5 in 查詢

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

最小化DevOps自動化流程(Golang)

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述