Mysql 的 NO_BACKSLASH_ESCAPES

寫程式遇到 mysql 吃掉我一個 \ 符號,原來是 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
\% % character; see note following the table
\_ _ character; see note following the table

 


mysql 看目前 sql_mode 指令:

SELECT @@session.sql_mode;

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/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

 

 

相關文章:

Python 裡 mysql 的 backsplash 跳脫
http://stackoverflow.max-everyday.com/2017/09/python-mysql-backsplash/

相關文章

寫留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *