Golang sql语句引号问题

太过爱你忘了你带给我的痛 2022-05-28 12:55 175阅读 0赞

使用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’)

[test.py]: http://test.py