【REST2SQL】01RDB關(guān)系型數(shù)據(jù)庫REST初設(shè)計
【REST2SQL】02 GO連接Oracle數(shù)據(jù)庫
【REST2SQL】03 GO讀取JSON文件
【REST2SQL】04 REST2SQL第一版Oracle版實現(xiàn)
【REST2SQL】05 GO 操作 達(dá)夢 數(shù)據(jù)庫
【REST2SQL】06 GO 跨包接口重構(gòu)代碼

MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，屬于 Oracle旗下產(chǎn)品。MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫管理系統(tǒng))應(yīng)用軟件之一。
MySQL是一種關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，關(guān)系數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個大倉庫內(nèi)，這樣就增加了速度并提高了靈活性。
MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫的最常用標(biāo)準(zhǔn)化語言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型和大型網(wǎng)站的開發(fā)都選擇 MySQL作為網(wǎng)站數(shù)據(jù)庫。

1 準(zhǔn)備工作

我的操作環(huán)境：
操作系統(tǒng)：Win10 x64
數(shù)據(jù)庫： Ver 8.0.35 for Win64 on x86_64 (MySQL Community Server - GPL)
go:1.21.5
LiteIDE : X38.3

1.1 安裝Mysql數(shù)據(jù)

登錄 Mysql 官網(wǎng)，有Mysql社區(qū)版可以下載，官網(wǎng)下載顯示的是32位，實際上安裝包里也包括64位的，下載成功后，安裝配置等這里省略5217字，我安裝后就是64位的，自己腦補(bǔ)。
創(chuàng)建測試表 guci
導(dǎo)入部分測試數(shù)據(jù)

1.2 Mysql go驅(qū)動安裝

第三方驅(qū)動下載。

go get -u github.com/go-sql-driver/mysql
//其中包含一個依賴庫
go get -u filippo.io/edwards25519

眾所周知的原因，可能同步失敗，自己想辦法翻墻或代理等一系列操作。
我這里偶爾能登錄github.com網(wǎng)站，我從網(wǎng)站上手動下載下來加到開發(fā)環(huán)境中的src

2 新建一個gomysql的項目

新建一下gomysql的項目用來測試go操作mysql數(shù)據(jù)庫。

2.1 go連接Mysql數(shù)據(jù)庫

1 引入相關(guān)包

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

2 聲明連接字符串

var ConnString string = "mysql://blma:5217@tcp(127.0.0.1:3306)/blma"

3 連接數(shù)據(jù)庫

// 連接Mysql數(shù)據(jù)庫
func connDB(connStr string) *sql.DB {end := strings.Index(connStr, "://")if end < 0 {log.Println("連接字符串設(shè)置有誤。")panic(nil)}driverName := connStr[:end] // dmconnstring := connStr[end+3:]db, err := sql.Open(driverName, connstring)dieOnError("Can't open the driver:", err)if err = db.Ping(); err != nil {fmt.Println("不能連接mysql數(shù)據(jù)庫：", err)return nil}// fmt.Printf("connect to \"%s\" succeed.\n", connStr)return db
}

2.2 實現(xiàn)CRUD

CUD比較簡單，都執(zhí)行execSQL操作，只是sql語句不同。
代碼如下：

/* 往表插入數(shù)據(jù) */
func insertData(insertSql string) string {result, _ := execSQL(insertSql)rows, err := result.RowsAffected()dieOnError("Can't insert", err)ret := map[string]int{"Insert rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 轉(zhuǎn) json失敗：", err)return string(jsonBytes)
}/* 刪除表數(shù)據(jù) */
func deleteData(deleteSql string) string {result, _ := execSQL(deleteSql)rows, err := result.RowsAffected()dieOnError("Can't delete", err)ret := map[string]int{"Delete rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 轉(zhuǎn) json失敗：", err)return string(jsonBytes)
}/* 修改表數(shù)據(jù) */
func updateData(updateSql string) string {result, _ := execSQL(updateSql)rows, err := result.RowsAffected()dieOnError("Can't update", err)ret := map[string]int{"Update rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 轉(zhuǎn) json失敗：", err)return string(jsonBytes)// var sql =// result, err := db.Exec(sql)// if err != nil {// 	return err// }// affectedRows, _ := result.RowsAffected()// fmt.Println("updateTable succeed Affected rows:", affectedRows)// return nil
}// 執(zhí)行SQL， execute stmt (INSERT, UPDATE, DELETE, DML, PLSQL) and return driver.Result object
func execSQL(sqls string) (result driver.Result, err error) {//連接數(shù)據(jù)庫DB := connDB(ConnString)//延遲關(guān)閉連接defer DB.Close()statement, err := DB.Prepare(sqls)if err != nil {fmt.Println("prepare statement failed:", err.Error())}defer statement.Close()//執(zhí)行SQL， execute stmt (INSERT, UPDATE, DELETE, DML) and return driver.Result objectresult, err = statement.Exec()if err != nil {fmt.Println("Exec failed:", err.Error())}dieOnError("Can't execSql() ", err)return result, err
}

2.3 動態(tài)查詢有點費勁

沒仔細(xì)研究Mysql的驅(qū)動，還是用達(dá)夢數(shù)據(jù)庫的思路。
總體思路是先查詢獲取 *sql.Rows對象，從這里通過rows.Columns（）和 rows. ColumnTypes()再獲取列名切片和列類型信息，第三步把列名和數(shù)據(jù)庫數(shù)據(jù)類型組合在一個map[string]string里；第四步初始化列值接收變量；第五步 rows.Next() 逐行遍歷返回結(jié)果集并根據(jù)數(shù)據(jù)庫類型（目前只匹配的VARCHER2 和 NUMBER，遇到其它類型再匹配）轉(zhuǎn)換為Go的數(shù)據(jù)類型，組成一個dataset數(shù)據(jù)集；第六步序列化并轉(zhuǎn)json返回查詢結(jié)果。
代碼如下：

/* 查詢表數(shù)據(jù) */
func selectData(sqlSelect string) string {// 連接數(shù)據(jù)庫DB := connDB(ConnString)//延遲關(guān)閉連接defer DB.Close()// 準(zhǔn)備查詢語句statement, err := DB.Prepare(sqlSelect)if err != nil {fmt.Println("prepare statement failed:", err.Error())return ""}defer statement.Close()
// 1查詢rows, err := statement.Query()if err != nil {fmt.Println("query failed:", err.Error())}// 2查詢的列名稱切片columns, err := rows.Columns()if err != nil {fmt.Println(err.Error())return ""}//fmt.Println(columns)// 3數(shù)據(jù)庫列類型cType, err := rows.ColumnTypes()if err != nil {log.Fatal(err)}//fmt.Println(cType[0].Name(), cType[0].DatabaseTypeName())// 4列名類型mapcoltyp := colType(cType)// 5初始化列值接收變量row := make([]sql.RawBytes, len(columns))scanArgs := make([]interface{}, len(row))for i := range row {scanArgs[i] = &row[i]}// 查詢結(jié)果數(shù)據(jù)集var dataset []map[string]interface{} //元素為map的切片for rows.Next() {err := rows.Scan(scanArgs...)if err != nil {panic(err.Error())}// rowvar row1 map[string]interface{} = make(map[string]interface{})for pos, col := range row {//fmt.Println(columns[pos], ":", string(col))colname := columns[pos]svalue := string(col)//數(shù)據(jù)類型處理value := typeConv(colname, svalue, coltyp)row1[colname] = value}//fmt.Println("row1:", row1)dataset = append(dataset, row1)//fmt.Println()}if err != io.EOF {dieOnError("Can't Next", err)}//切片轉(zhuǎn)jsonjsonBytes, err := json.Marshal(dataset)dieOnError("slice 轉(zhuǎn) json失敗：", err)//fmt.Println(string(jsonBytes))return string(jsonBytes)
}// 生成列名和類型 map
func colType(cType []*sql.ColumnType) map[string]string {var colTyp map[string]string = make(map[string]string)for _, col := range cType {colTyp[col.Name()] = col.DatabaseTypeName()}//fmt.Println(colTyp)return colTyp
}// 字符根據(jù)數(shù)據(jù)庫類型轉(zhuǎn)為go數(shù)據(jù)類型
func typeConv(colname string, svalue string, ct map[string]string) interface{} {var ret interface{}switch ct[colname] {case "VARCHAR":ret = svaluecase "DECIMAL":if len(svalue) > 0 {flt, err := strconv.ParseFloat(svalue, 64)if err != nil {fmt.Println("轉(zhuǎn)換失敗：", colname, svalue, err)} else {ret = flt}} else { //空串處理ret = nil}default:ret = svalue}return ret
}

3 全部代碼及運行結(jié)果截圖

全部代碼：

/*該例程實現(xiàn)了Mysql數(shù)據(jù)庫插入數(shù)據(jù)，修改數(shù)據(jù)，刪除數(shù)據(jù)，數(shù)據(jù)查詢等基本操作。*/
package main// 引入相關(guān)包
import ("database/sql""database/sql/driver""encoding/json""fmt""io""log""strconv""strings"_ "github.com/go-sql-driver/mysql"
)var ConnString string = "mysql://blma:5217@tcp(127.0.0.1:3306)/blma"// var ConnString string = config.Conf.ConnStringfunc main() {fmt.Println("\ngo 操作mysql數(shù)據(jù)庫 demo")var (sqls   string //sql語句result string //sql執(zhí)行后返回的結(jié)果)//顯示版本號showMysqlVersion()// // insert 插入一行數(shù)據(jù)sqls = `INSERT INTO kezz(p_id,s_dm,s_mc,n_zzxj) VALUES(-101,'125217','白龍馬',5217);`result = insertData(sqls)fmt.Println(result)// update 更新數(shù)據(jù)sqls = "UPDATE kezz SET n_zzxj = 111 WHERE p_id = -100"result = updateData(sqls)fmt.Println(result)// delete 刪除數(shù)據(jù)sqls = "delete from kezz where p_id = -109"result = deleteData(sqls)fmt.Println(result)// select 查詢數(shù)據(jù)sqls = "select p_id,s_dm,s_mc,n_zzxj from kezz limit 5 "result = selectData(sqls)fmt.Println(result)}func showMysqlVersion() {//連接數(shù)據(jù)庫db := connDB(ConnString)var version stringerr2 := db.QueryRow("SELECT VERSION()").Scan(&version)if err2 != nil {log.Fatal(err2)}fmt.Println("Mysql:", version)
}// 連接Mysql數(shù)據(jù)庫
func connDB(connStr string) *sql.DB {end := strings.Index(connStr, "://")if end < 0 {log.Println("連接字符串設(shè)置有誤。")panic(nil)}driverName := connStr[:end] // dmconnstring := connStr[end+3:]db, err := sql.Open(driverName, connstring)dieOnError("Can't open the driver:", err)if err = db.Ping(); err != nil {fmt.Println("不能連接mysql數(shù)據(jù)庫：", err)return nil}// fmt.Printf("connect to \"%s\" succeed.\n", connStr)return db
}// 發(fā)生錯誤退出1
func dieOnError(msg string, err error) {if err != nil {log.Println(msg, err)//os.Exit(1)}
}/* 往表插入數(shù)據(jù) */
func insertData(insertSql string) string {result, _ := execSQL(insertSql)rows, err := result.RowsAffected()dieOnError("Can't insert", err)ret := map[string]int{"Insert rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 轉(zhuǎn) json失敗：", err)return string(jsonBytes)
}/* 刪除表數(shù)據(jù) */
func deleteData(deleteSql string) string {result, _ := execSQL(deleteSql)rows, err := result.RowsAffected()dieOnError("Can't delete", err)ret := map[string]int{"Delete rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 轉(zhuǎn) json失敗：", err)return string(jsonBytes)
}/* 修改表數(shù)據(jù) */
func updateData(updateSql string) string {result, _ := execSQL(updateSql)rows, err := result.RowsAffected()dieOnError("Can't update", err)ret := map[string]int{"Update rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 轉(zhuǎn) json失敗：", err)return string(jsonBytes)
}// 執(zhí)行SQL， execute stmt (INSERT, UPDATE, DELETE, DML, PLSQL) and return driver.Result object
func execSQL(sqls string) (result driver.Result, err error) {//連接數(shù)據(jù)庫db := connDB(ConnString)//延遲關(guān)閉連接defer db.Close()statement, err := db.Prepare(sqls)if err != nil {fmt.Println("prepare statement failed:", err.Error())}defer statement.Close()//執(zhí)行SQL， execute stmt (INSERT, UPDATE, DELETE, DML) and return driver.Result objectresult, err = statement.Exec()if err != nil {fmt.Println("Exec failed:", err.Error())}dieOnError("Can't execSql() ", err)return result, err
}/* 查詢表數(shù)據(jù) */
func selectData(sqlSelect string) string {// 連接數(shù)據(jù)庫db := connDB(ConnString)//延遲關(guān)閉連接defer db.Close()// 準(zhǔn)備查詢語句statement, err := db.Prepare(sqlSelect)if err != nil {fmt.Println("prepare statement failed:", err.Error())return ""}defer statement.Close()// 1查詢rows, err := statement.Query()if err != nil {fmt.Println("query failed:", err.Error())}// 2查詢的列名稱切片columns, err := rows.Columns()if err != nil {fmt.Println(err.Error())return ""}//fmt.Println(columns)// 3數(shù)據(jù)庫列類型cType, err := rows.ColumnTypes()if err != nil {log.Fatal(err)}//fmt.Println(cType[0].Name(), cType[0].DatabaseTypeName())// 4列名類型mapcoltyp := colType(cType)// 5初始化列值接收變量row := make([]sql.RawBytes, len(columns))scanArgs := make([]interface{}, len(row))for i := range row {scanArgs[i] = &row[i]}// 查詢結(jié)果數(shù)據(jù)集var dataset []map[string]interface{} //元素為map的切片for rows.Next() {err := rows.Scan(scanArgs...)if err != nil {panic(err.Error())}// rowvar row1 map[string]interface{} = make(map[string]interface{})for pos, col := range row {//fmt.Println(columns[pos], ":", string(col))colname := columns[pos]svalue := string(col)//數(shù)據(jù)類型處理value := typeConv(colname, svalue, coltyp)row1[colname] = value}//fmt.Println("row1:", row1)dataset = append(dataset, row1)//fmt.Println()}if err != io.EOF {dieOnError("Can't Next", err)}//切片轉(zhuǎn)jsonjsonBytes, err := json.Marshal(dataset)dieOnError("slice 轉(zhuǎn) json失敗：", err)//fmt.Println(string(jsonBytes))return string(jsonBytes)
}// 生成列名和類型 map
func colType(cType []*sql.ColumnType) map[string]string {var colTyp map[string]string = make(map[string]string)for _, col := range cType {colTyp[col.Name()] = col.DatabaseTypeName()}//fmt.Println(colTyp)return colTyp
}// 字符根據(jù)數(shù)據(jù)庫類型轉(zhuǎn)為go數(shù)據(jù)類型
func typeConv(colname string, svalue string, ct map[string]string) interface{} {var ret interface{}switch ct[colname] {case "VARCHAR":ret = svaluecase "DECIMAL":if len(svalue) > 0 {flt, err := strconv.ParseFloat(svalue, 64)if err != nil {fmt.Println("轉(zhuǎn)換失敗：", colname, svalue, err)} else {ret = flt}} else { //空串處理ret = nil}default:ret = svalue}return ret
}

運行結(jié)果截圖：

完