Mysql 的 NO_BACKSLASH_ESCAPES

Posted in :

2017-08-15

寫程式遇到 mysql 吃掉我一個 \ 符號，原來是mysql 預設不會加入 NO_BACKSLASH_ESCAPES 參數, 所以預設是允許 \ 做跳脫！愈多跳脫, 就愈多問題, 解法是要加入 NO_BACKSLASH_ESCAPES 參數.

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (\), known as the escape character. MySQL recognizes the escape sequences shown in Table 9.1, “Special Character Escape Sequences”. For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, \x is just x. These sequences are case sensitive. For example, \b is interpreted as a backspace, but \B is interpreted as B. Escape processing is done according to the character set indicated by the character_set_connection system variable. This is true even for strings that are preceded by an introducer that indicates a different character set, as discussed in Section 10.1.3.6, “Character String Literal Character Set and Collation”.

Table 9.1 Special Character Escape Sequences

Escape Sequence	Character Represented by Sequence
`\0`	An ASCII NUL (`X'00'`) character
`\'`	A single quote (`'`) character
`\"`	A double quote (`"`) character
`\b`	A backspace character
`\n`	A newline (linefeed) character
`\r`	A carriage return character
`\t`	A tab character
`\Z`	ASCII 26 (Control+Z); see note following the table
`\\`	A backslash (`\`) character
`\%`	A `%` character; see note following the table
`\_`	A `_` character; see note following the table

mysql 看目前 sql_mode 指令：

SELECT @@session.sql_mode;

output:

'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

結論: 缺少 NO_BACKSLASH_ESCAPES 參數

mysql 看系統變數指令：

SHOW VARIABLES WHERE Variable_Name LIKE "%dir"

Output (on Linux):

+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| basedir                   | /usr                       |
| character_sets_dir        | /usr/share/mysql/charsets/ |
| datadir                   | /var/lib/mysql/            |
| innodb_data_home_dir      |                            |
| innodb_log_group_home_dir | ./                         |
| lc_messages_dir           | /usr/share/mysql/          |
| plugin_dir                | /usr/lib/mysql/plugin/     |
| slave_load_tmpdir         | /tmp                       |
| tmpdir                    | /tmp                       |
+---------------------------+----------------------------+

Output (on macOS Sierra):

+---------------------------+-----------------------------------------------------------+
| Variable_name             | Value                                                     |
+---------------------------+-----------------------------------------------------------+
| basedir                   | /usr/local/mysql-5.7.17-macos10.12-x86_64/                |
| character_sets_dir        | /usr/local/mysql-5.7.17-macos10.12-x86_64/share/charsets/ |
| datadir                   | /usr/local/mysql/data/                                    |
| innodb_data_home_dir      |                                                           |
| innodb_log_group_home_dir | ./                                                        |
| innodb_tmpdir             |                                                           |
| lc_messages_dir           | /usr/local/mysql-5.7.17-macos10.12-x86_64/share/          |
| plugin_dir                | /usr/local/mysql/lib/plugin/                              |
| slave_load_tmpdir         | /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T/         |
| tmpdir                    | /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T/

Default options are read from the following files in the given order:

/etc/my.cnf
/etc/my.cnf.d/mysql-server.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

如果是 MAC OS X 檔案在：

/usr/local/etc/my.cnf

找到 my.cnf 檔案後，修改

sql_mode=...;

加入

NO_BACKSLASH_ESCAPES

加好後，執行 service mysql restart 重啟 mysql

附上我使用中的 my.cnf

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_BACKSLASH_ESCAPES

如果是使用 Ubuntu linux , 也可以試看看這行指令:

To enable this, open up your mysqld.cnf file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

如果是 Rocky linux 的話, mysql 設定檔有可能在: /etc/my.cnf.d/mysql-server.cnf


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES

說明: 就只有增加 sql_mode= 這行指令. 並重啟 mysql server: