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);
}
}