MySQL5.6匯入資料時的錯誤分析

MySQL5.6匯入資料時的錯誤分析

今天在匯入mysqldump匯出的資料的時候出現了一個奇怪的錯誤？

ERROR 1064 (42000) at line 13327: 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 '*/' at line 1

開啟檔案的13327行，發現這個是一個觸發器的SQL語句帶有 /*! */ 不仔細看以為建立triggers失敗
但是手工執行的時候確實這樣子的：

mysql> /*!50003 CREATE*/ /*!50017 DEFINER=`appuser`@`%`*/ /*!50003 TRIGGER `b_admin_user_update` AFTER UPDATE ON `b_admin_user` FOR EACH ROW
    ->      INSERT INTO delta_b_admin_user(`user_id`,`username`, `password`, `salt`, `real_name`, `cell_phone`, `qq`, `phone`, `email`, `wangwang`, `created_time`, `last_login_time`, `last_login_ip`, `user_group_id`, `flag`, `parent_id`, `qr_code`)
    -> values
    -> (NEW.user_id,NEW.username, NEW.password, NEW.salt, NEW.real_name, NEW.cell_phone, NEW.qq, NEW.phone, NEW.email, NEW.wangwang, NEW.created_time, NEW.last_login_time, NEW.last_login_ip, NEW.user_group_id, NEW.flag, NEW.parent_id, NEW.qr_code); */;;
Query OK, 0 rows affected (0.01 sec)

ERROR 1064 (42000): 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 '*/' at line 1

這是執行成功了呢？ 但還是報了個錯誤：（

經過尋找，發現mysql在匯入的時候有個參數是忽略錯誤繼續執行後面的SQL知道整個檔案執行完成

--force 這個參數，加上參數再匯入一次

/usr/local/mysql-5.6/bin/mysql -uroot -p'pass' --force  < /usr/local/mysql-5.6/all.sql
Warning: Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 13327: 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 '*/' at line 1
ERROR 1064 (42000) at line 13345: 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 '*/' at line 1
ERROR 1064 (42000) at line 15612: 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 '*/' at line 1
ERROR 1064 (42000) at line 15630: 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 '*/' at line 1
ERROR 1064 (42000) at line 21753: 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 '*/' at line 1
ERROR 1064 (42000) at line 21771: 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 '*/' at line 1
ERROR 1064 (42000) at line 28734: 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 '*/' at line 1

恩，確實沒有退出。
之後又做了一個測試
在匯出資料的時候不匯出觸發器，那麼需要加上參數 --skip-triggers

再匯入的時候就沒有報錯了
那麼是不是mysql-5.6版本的關係嗎？
有待研究

本文永久更新連結地址：