mysql sql_mode MysqlDataTruncation: Data truncation

最新推荐文章于 2024-06-20 14:26:29 发布

原创最新推荐文章于 2024-06-20 14:26:29 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文深入解析MySQL的sql_mode系统参数，探讨其对SQL语法和数据校验的影响，介绍常见取值如STRICT_TRANS_TABLES的作用，以及如何在启动和运行时进行配置。同时，文章提供了在Java中设置sql_mode的方法，并通过实例说明JDBC配置对数据处理的影响。

sql_mode是什么

sql_mode是mysql的系统参数，会影响mysql的行为：SQL语法和数据的校验。

sql_mode取值有那些

常用的比如：STRICT_TRANS_TABLES 值的行为是设置mysql为严格模式(Strict SQL Mode)，这种模式下MYSQL对于错误的数据会终止数据更新，并且报error错误。

比如某个字段name varchar(20)，如果insert或update的值长度超过20

在严格模式下：mysql会报错error，终止数据更新。

在非严格模式下：mysql会报warning，并且把数据截断后更新数据库

完整列表如下：

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-full

sql_mode设置方式

启动时配置

1、启动的时候在命令行通过 --sql-mode="modes"

2、mysql的配置文件 sql-mode="modes"

运行时配置

1、全局： set global sql_mode='modes'; (修改后新的连接，才会生效)

2、session: set session sql_mode='modes'; (只影响当前的连接)

sql_mode查看

查看全局: select @@global.sql_mode

查看session: select @@session.sql_mode

在Java中能否设置

根据MYSQL的JDBC手册中可以看出，可以在datasource和connection上分别设置一些参数。具体的方式可以通过设置属性或在url后面添加参数。参照: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

实例说明

准备

1、数据库端设置global.sql_mode为'NO_ENGINE_SUBSTITUTION'，这个模式的作用是：

启用的时候(默认启用)：如果create和alter指定的引擎不存在就报错，终止语句执行。

禁用的使用： create表的时候如果指定的引擎不存在,会发出警告，用默认的替代。如果是alter表时候引擎不存在,发出警告不做修改。

2、创建表t 如下:

JDBC默认配置执行结果

输出结果，服务器端设置的是：NO_ENGINE_SUBSTITUTION，这个为为什么会输出两个呢？见下面分析

3.1.13 jdbc版本

8.0.17 jdbc输出如下

数据库没有记录

直接在MYSQL命令行执行

从上面可以看到使用JDBC默认配置执行会抛异常，并且数据不能插入，下面直接从客户端执行

备注：

字符串是按照长度截断，数字是按照范围截断。由于age是有符号的tinyint，最大值是127。

类型最大值最小值见：https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

JDBC配置jdbcCompliantTruncation参数

控制台输出

MYSQL查看结果

关于 jdbcCompliantTruncation参数

通过查看JDBC源码(不同版本源码不同)可以看到这个参数的作用，大概是：

1、JDBC规范要求如果写数据的时候发生截断应该抛出异常，如果服务器是 4.1.0或更高版本数据会被截断插入，但是会抛出一个异常。如果服务器是5.0.2或更高版本JDBC会设置sql_mode为严格模式，这样效率比检查warning更高。

2、从3.1.2版开始，默认值是true

8.0.17 jdbc的代码

抛出异常数据就没有插入到数据库么？

从关于 jdbcCompliantTruncation参数的说明中可以知道，即使客户端抛出了异常，数据也有可能被插入数据库

大概试了下小于JDBC 3.1.13 版本，会报以下的错误，但是数据还是被截断后插入数据库了。

应该选择那个版本的MYSQL客户端

参照官网：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-versions.html

如果是JDK 8 推荐用8.0的 JDBC

总结

1、可以通过JDBC的参数配置会话级别为SQL严格模式，而且mysql jdbc客户端大于3.1.2（实际测试应该是3.1.13版本）的话都会默认在客户端设置为严格模式。

2、数据库抛异常不代表数据没有插入成功，参照 "抛出异常数据就没有插入到数据库么？"

3、可以通过如下方式查看JDBC会话sql mode，如果输出中有"STRICT_TRANS_TABLES"或"STRICT_ALL_TABLES"就是严格模式

private void showSQlMode(Connection conn) throws Exception {
    Statement sqlModeState = conn.createStatement();
    ResultSet resultSet = sqlModeState.executeQuery("select @@session.sql_mode");
    while (resultSet.next()) {
        String sqlMode = resultSet.getString(1);
        System.out.println("sql_mode:" + sqlMode);
    }
}