SQL_MODE配置了STRICT_TRANS_TABLES导致插入数据报错ERROR 1366 (HY000)

0    80    1

👉 本文共约1178个字,系统预计阅读时间或需5分钟。

现象

分析

在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

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

若是客户端查询乱码,则可以配置:

解决

经过查询,肯定不是字符集配置的问题。

所以,把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

    头像

    小麦苗

    学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

    您可能还喜欢...

    发表评论

    您的电子邮箱地址不会被公开。

    17 + 5 =

     

    嘿,我是小麦,需要帮助随时找我哦
    • 18509239930
    • 个人微信

    • 麦老师QQ聊天
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部
    返回顶部