SQL_MODE配置了STRICT_TRANS_TABLES导致插入数据报错ERROR 1366 (HY000)
现象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | MySQL [test]> show variables like 'char%'; +--------------------------+--------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb4 | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | +--------------------------+--------------------------------+ 8 rows in set (0.03 sec) MySQL [test]> source C:\Users\lhrxxt\Desktop\2.sql Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.06 sec) Query OK, 0 rows affected (0.03 sec) ERROR 1366 (HY000): Incorrect string value: '\xED\xA0\xBD\xED\xB9\x8F...' for column 'FD_CONTENT' at row 1 Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.03 sec) MySQL [test]> show create database test; +----------+--------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+--------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) MySQL [test]> MySQL [test]> MySQL [test]> show variables like 'sql_mode'; +---------------+-----------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +---------------+-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) MySQL [test]> exit Bye C:\Users\lhrxxt>mysql -uroot -plhr -h119.3.171.15 -D test < C:\Users\lhrxxt\Desktop\2.sql mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1366 (HY000) at line 44: Incorrect string value: '\xED\xA0\xBD\xED\xB9\x8F...' for column 'FD_CONTENT' at row 1 C:\Users\lhrxxt>mysql -uroot -plhr -h119.3.171.15 -D test --default-character-set=utf8 < C:\Users\lhrxxt\Desktop\2.sql mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1366 (HY000) at line 44: Incorrect string value: '\xED\xA0\xBD\xED\xB9\x8F...' for column 'FD_CONTENT' at row 1 |
分析
在MySQL中，我们可以为数据库、数据表和数据列分别设置编码格式，而且，在指定了编码格式的情况下，数据列的编码格式优先于数据表，数据表的编码格式优先于数据列，在没有指定编码格式的情况下，数据列继承数据表的编码格式，数据表继承数据库的编码格式——但是继承是以时序为前提的，例如，数据表继承的是在创建该表前数据库的编码格式，在一个数据表已经存在的情况下，修改该数据表所在数据库的编码格式并不会导致该数据表原有编码格式的改变。
如果单独执行和整体source文件执行都报错，那么就需检查一下数据库的编码、数据表的编码、sql_mode参数等细节，
查看数据库编码： show variables like 'char%';
查看数据表字段编码： show full columns from XX表名;
查出来如果不是UTF-8的，用MYSQL命令修改，如：
SET character_set_server = utf8; -----更改mysql服务器的编码为utf8
SET character_set_connection = utf8; -----更改数据库连接的编码为utf8
SET character_set_database = utf8; -----更改数据库编码为utf8
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | set character_set_client = utf8mb4; set character_set_server = utf8mb4; set character_set_connection = utf8mb4; set character_set_database = utf8mb4; set character_set_results = utf8mb4; set character_set_system = utf8mb4; set collation_connection = utf8mb4_general_ci; set collation_database = utf8mb4_general_ci; set collation_server = utf8mb4_general_ci; show variables like 'char%'; show create table 表名; show full columns from 表名; show create database test; -- 更改某个数据表的编码为utf8： alter table 表名 convert to character set utf8; -- 数据库 alter database onlinedb character set utf8; -- 字段 alter table users CHANGE ulogin ulogin varchar(20) CHARACTER SET utf8; |
若是客户端查询乱码，则可以配置：
1 | set character_set_results = gbk; |
解决
经过查询，肯定不是字符集配置的问题。
所以，把sql文件打开，查看报错的列，发现报错的列里边有不可见字符或不可显示字符（乱码）。
对于这种情况，只能配置sql_mode参数，去掉STRICT_TRANS_TABLES和STRICT_ALL_TABLES参数，默认自带STRICT_TRANS_TABLES参数，不启用严格模式。
STRICT_TRANS_TABLES和STRICT_ALL_TABLES，只要把SQL_MODE设置为其中的一个，就被称为严格模式。
严格模式与非严格模式，简单来说就是MySQL自身对数据进行严格的校验(格式、长度、类型等)，比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下MySQL不会报错, 定义的类型长度超出了也不会报错, 严格模式则会。
这两个选项对于Innodb表是相同的作用，对于单个insert操作，无论插入单行或是多行，只要插入数据与字段类型不兼容，则insert操作失败并回滚；
对于Myisam表是不同的作用：
1.STRICT_TRANS_TABLES，对于单个insert操作，插入单行数据与字段类型不兼容，则insert操作失败并回滚；插入多行数据，如果插入数据的第一行内容与字段类型不兼容，则insert操作失败并回滚，如果插入数据的第一行内容与字段类型兼容，但后续的数据行存在不兼容的情况，则兼容的数据正常插入，不兼容的数据会转换成符合字段类型的格式再插入，不会中断和回滚；
2.STRICT_ALL_TABLES，与STRICT_TRANS_TABLES不同的是，如果插入数据的第一行内容与字段类型兼容，但后续的数据行存在不兼容的情况，则兼容的数据正常插入，不兼容的数据则会报错并终止insert操作。
参考：https://blog.csdn.net/weixin_39004901/article/details/89373287
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | MySQL [test]> set sql_mode=''; Query OK, 0 rows affected (0.03 sec) MySQL [test]> source C:\Users\lhrxxt\Desktop\1.sql Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.07 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 1 row affected, 2 warnings (0.03 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.03 sec) |
