合 SQL_MODE配置了STRICT_TRANS_TABLES导致插入数据报错ERROR 1366 (HY000)
Tags： 故障处理MySQLERROR 1366STRICT_TRANS_TABLES严格模式
👉 本文共约1170个字，系统预计阅读时间或需5分钟。
现象
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; |
若是客户端查询乱码，则可以配置：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
