寫程式遇到 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
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_ESCAPESBy default, the OS X installation does not use a my.cnf, and MySQL just uses the default values. To set up your own my.cnf, you could just create a file straight in /etc.
OS X provides example configuration files at /usr/local/mysql/support-files/.
在新的 macOS, 並沒有 /etc/my.cnf 需要手動建立：
sudo vi /etc/my.cnf預設 mode:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION建議 mode:
ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION建議 /etc/my.cnf 內容
[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
sql_mode=ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION如果是使用 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:
sudo service mysqld restart或指令:
sudo systemctl restart mysql.service重啟完 mysql 後, 再使用下面指令, 確定 sql_mode
SELECT @@session.sql_mode;附上完成圖:
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)
相關文章：
Python 裡 mysql 的 backsplash 跳脫
https://stackoverflow.max-everyday.com/2017/09/python-mysql-backsplash/