Go sql語句引號問題

標籤：插入   問題   into   ace   說明   syn   轉義   check   man   

使用Go進行Mysql開發時，會遇到引號問題(實際上，與語言無關，只要使用sql就會遇到這些類似問題)。

本文舉例說明如何解決這些問題。

Example1

第一例子示範格式字元是否要加引號。

代碼如下：

detailMsg := "abc"sql := fmt.Sprintf(   "insert into tbl_log(ip, name, detail_msg) values (‘%s‘, ‘%s‘, %s)",    "1.1.0.1",     "test.py",     detailMsg,)fmt.Println("sql: ", sql)

output:

2018/04/05 17:40:00 sql: insert into tbl_log(ip, name, detail_msg) values (‘1.1.0.1‘, ‘test.py‘, abc)
2018/04/05 17:40:00 exec failed: Error 1054: Unknown column ‘abc‘ in ‘field list‘ , sql: insert into tbl_log(ip, name, detail_msg) values (‘1.1.0.1‘, ‘test.py‘, abc)

如果欄位類型是字串，格式化字元需要加上兩個單引號，也就是格式字元%s改為‘%s‘ .

正確樣本

detailMsg := "abc"sql := fmt.Sprintf(         "insert into tbl _log(ip, name, detail_msg) values (‘%s‘, ‘%s‘, ‘%s‘)",          "1.1.0.1",          "test.py",          detailMsg,        )fmt.Println("sql: ", sql)

output

sql: insert into tbl_log(ip, name, detail_msg) values (‘1.1.0.1‘, ‘test.py‘, ‘abc‘)

Example2

如果插入的內容中包含單引號，例子如下。

也就是detailMsg中如果有單引號。

detailMsg := "ab‘c"fmt.Println("detailMsg: ", detailMsg)sql := fmt.Sprintf(         "insert into tbl_log(ip, name, detail_msg) values (‘%s‘, ‘%s‘, ‘%s‘)",          "1.1.0.1",          "test.py",          detailMsg,        )fmt.Println("sql: ", sql)

output

2018/04/05 17:45:03 detailMsg: ab‘c
2018/04/05 17:45:03 sql: insert into tbl _log(ip, name, detail_msg) values (‘1.1.0.1‘, ‘test.py‘, ‘ab‘c‘)
2018/04/05 17:45:03 exec failed: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘c‘)‘ at line 1 , sql: insert into tbl_log(ip, name, detail_msg) values (‘1.1.0.1‘, ‘test.py‘, ‘ab‘c‘)

解決辦法

對內容中的單引號進行轉義 ‘.

detailMsg:= "ab‘c"detailMsg = strings.Replace(detailMsg, "‘", "\\‘", -1)log.Println("detailMsg:", detailMsg)sql := fmt.Sprintf(  "insert into tbl_log(ip, name, detail_msg) values (‘%s‘, ‘%s‘, ‘%s‘)",   "1.1.0.1",   "test.py",   detailMsg,)log.Println("sql:", sql)

output:

2018/04/05 17:48:11 detailMsg: ab‘c
2018/04/05 17:48:11 sql: insert into tbl_log(ip, name, detail_msg) values (‘1.1.0.1‘, ‘test.py‘, ‘ab‘c‘)

Go sql語句引號問題