mysql Error Code: 1064. You have an error in your SQL syntax;

在 mysql 裡遇到一個錯誤訊息:

Error Code: 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 '05152018_bwamem.sam' where id=6' at line 1

遇到的 sql command:

update component set code='bwa  mem -t 4 -R ''@RG\ILLUMINA\''  05152018_bwamem.sam' where id=6;

發生的原因, \ (backslash) 在 mysql 是跳脫字元, 愈多跳脫, 就愈多問題!

解法:

Mysql 的 NO_BACKSLASH_ESCAPES
https://stackoverflow.max-everyday.com/2017/08/mysql-no_backslash_escapes/

修改檔案的 /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