由mysql的 datetime 类型字段默认设置为了'0000-00-00' 引发的血案

2019独角兽企业重金招聘Python工程师标准>>>

一、错误信息

Java连接数据库转化为对象时报以下错误信息：

Error querying database. Cause: 
java.sql.SQLException: Value '0000-00-00 00:00:00' 
can not be represented as java.sql.Timestamp

数据库迁移导入sql文件时报以下错误：

[SQL] INSERT INTO `auto_policy_info` VALUES 
('14', null, null, '0', '0000-00-00 00:00:00', '2015-09-21 10:10:36', ...);
[Err] 1292 - Incorrect datetime value:
 '0000-00-00 00:00:00' for column 'update_time' at row 1

二、解决办法

     连接数据库转化为对象出错的解决办法为在数据库连接后面加上参数zeroDateTimeBehavior=convertToNull 这样如果碰到 ‘0000-00-00：00:00:00’的日期类型时，将会转化为null值

db.jdbcurl=jdbc:mysql://192.168.1.52:3306/db?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull

   针对数据插入数据‘0000-00-00：00:00:00’ 数据本身不接受的解决办法为，用root用户登录，重新设置数据库的模式（尽量使用root用户 要不然 GLOBAL设置不成功，但是可以设置SESSION的）

    1、首先查询出数据库现有的模式

select @@sql_mode;

    

    2、把NO_ZERO_IN_DATE,NO_ZERO_DATE去掉，然后重新设置

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

这样关闭数据库客户端的连接，重新登录，然后再执行那种比较操蛋的插入语句即可正确的插入。

三、刨根问底

如果以上两步已经解决了问题，不想再细究其他的东西，那到此问题就结束了。

从刚开始接触Java程序开始，我们对这种数据库的连接语句已经烂熟于胸，也许在我们也会经常设置字符编码，但是对其他的参数知之甚少，或者很少使用，还有就是数据库服务器是有模式这个东西的，这个如果仅仅是对开发人员来说应该很少接触，这个模式的设置对我们程序的有什么样的影响，如何设置数据库的模式（此处暂时只看mysql的）。所以引发了我对以上两个问题的探究。

1、数据库连接参数

数据库连接URL格式

jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]] »
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

简单连接

jdbc:mysql://localhost:3306/sakila?profileSQL=true

多数据连接

jdbc:mysql:loadbalance://localhost:3306,localhost:3310/sakila

数据连接后的参数影响着服务器到数据库数据的行为，在我们通常使用的情况下，mysql数据库连接参数我们设置最多的就是characterEncoding=GBK/UTF-8 了，这个是为了防止应用程序乱码（数据库查询出来的数据转化到应用程序中乱码）而设置的，还有user和password参数，但是对其他参数就不知道了，跑到官方文档几经折腾，终于找到了针对MySQL 5.7版本的关于参数的详细说明，

以下简要说明，其他的可以自行在里面去查找

Connection/Authentication. 

属性和描述
user 连接数据库的用户 Since version: all versions
password 连接数据库的密码 Since version: all versions
socketFactory The name of the class that the driver should use for creating socket connections to the server. This class must implement the interface 'com.mysql.jdbc.SocketFactory' and have public no-args constructor. Default: com.mysql.jdbc.StandardSocketFactory Since version: 3.0.3
connectTimeout 连接超时时间（毫秒数），设置为在jdk1.4版本以后默认为0,意思是永不超时 Default: 0 Since version: 3.0.1
socketTimeout 操作超时时间，默认为0，永不超时 Default: 0 Since version: 3.0.1
connectionLifecycleInterceptors A comma-delimited list of classes that implement "com.mysql.jdbc.ConnectionLifecycleInterceptor" that should notified of connection lifecycle events (creation, destruction, commit, rollback, setCatalog and setAutoCommit) and potentially alter the execution of these commands. ConnectionLifecycleInterceptors are "stackable", more than one interceptor may be specified via the configuration property as a comma-delimited list, with the interceptors executed in order from left to right. Since version: 5.1.4
useConfigs Load the comma-delimited list of configuration properties before parsing the URL or applying user-specified properties. These configurations are explained in the 'Configurations' of the documentation. Since version: 3.1.5
authenticationPlugins Comma-delimited list of classes that implement com.mysql.jdbc.AuthenticationPlugin and which will be used for authentication unless disabled by "disabledAuthenticationPlugins" property. Since version: 5.1.19
defaultAuthenticationPlugin Name of a class implementing com.mysql.jdbc.AuthenticationPlugin which will be used as the default authentication plugin (see below). It is an error to use a class which is not listed in "authenticationPlugins" nor it is one of the built-in plugins. It is an error to set as default a plugin which was disabled with "disabledAuthenticationPlugins" property. It is an error to set this value to null or the empty string (i.e. there must be at least a valid default authentication plugin specified for the connection, meeting all constraints listed above). Default: com.mysql.jdbc.authentication.MysqlNativePasswordPlugin Since version: 5.1.19
disabledAuthenticationPlugins Comma-delimited list of classes implementing com.mysql.jdbc.AuthenticationPlugin or mechanisms, i.e. "mysql_native_password". The authentication plugins or mechanisms listed will not be used for authentication which will fail if it requires one of them. It is an error to disable the default authentication plugin (either the one named by "defaultAuthenticationPlugin" property or the hard-coded one if "defaultAuthenticationPlugin" property is not set). Since version: 5.1.19
disconnectOnExpiredPasswords If "disconnectOnExpiredPasswords" is set to "false" and password is expired then server enters "sandbox" mode and sends ERR(08001, ER_MUST_CHANGE_PASSWORD) for all commands that are not needed to set a new password until a new password is set. Default: true Since version: 5.1.23
interactiveClient Set the CLIENT_INTERACTIVE flag, which tells MySQL to timeout connections based on INTERACTIVE_TIMEOUT instead of WAIT_TIMEOUT Default: false Since version: 3.1.0
localSocketAddress Hostname or IP address given to explicitly configure the interface that the driver will bind the client side of the TCP/IP connection to when connecting. Since version: 5.0.5
propertiesTransform An implementation of com.mysql.jdbc.ConnectionPropertiesTransform that the driver will use to modify URL properties passed to the driver before attempting a connection Since version: 3.1.4
useCompression Use zlib compression when communicating with the server (true/false)? Defaults to 'false'. Default: false Since version: 3.0.17

2、数据库服务器模式

在mysql官网的mode index中有对各个模式的描述

MySQL服务器可以以不同的SQL模式来操作，并且可以为不同客户端应用不同模式。这样每个应用程序可以根据自己的需求来定制服务器的操作模式。

模式定义MySQL应支持哪些SQL语法，以及应执行哪种数据验证检查。这样可以更容易地在不同的环境中使用MySQL，并结合其它数据库服务器使用MySQL。

你可以用--sql-mode="modes"选项启动mysqld来设置默认SQL模式。如果你想要重设，该值还可以为空(--sql-mode ="")。

你还可以在启动后用 SET [SESSION|GLOBAL] sql_mode='modes'语句设置 sql_mode变量来更改 SQL模式。设置 GLOBAL变量时需要拥有 SUPER权限，并且会影响从那时起连接的所有客户端的操作。设置 SESSION变量只影响当前的客户端。任何客户端可以随时更改自己的会话 sql_mode值

查阅5.17版本，提供了如下模式

ALLOW_INVALID_DATES

在严格模式下不要检查全部日期。只检查1到12之间的月份和1到31之间的日。这在Web应用程序中，当你从三个不同的字段获取年、月、日，并且想要确切保存用户插入的内容(不进行日期验证)时很重要。该模式适用于DATE和DATETIME列。不适合TIMESTAMP列，TIMESTAMP列需要验证日期

ANSI

等同 REAL_AS_FLOAT、 PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE

ANSI_QUOTES

将‘ "’视为识别符引号 (‘ `’引号字符 )，不要视为字符串的引号字符。在 ANSI模式，你可以仍然使用‘ `’来引用识别符。启用 ANSI_QUOTES后，你不能用双引号来引用字符串，因为它被解释为识别符。

ERROR_FOR_DIVISION_BY_ZERO

在严格模式下，在INSERT或者UPDATE过程中，如果零除（或者MOD(X,0)），则产生错误（否则为警告）。如果未给出该模式，背零除时Mysql返回NULL。

HIGH_NOT_PRECEDENCE

NOT操作符的优先顺序是表达式例如 NOT a BETWEEN b AND c被解释为 NOT (a BETWEEN b AND c)。在一些旧版本 MySQL中， 表达式被解释为 (NOT a) BETWEEN b AND c。启用 HIGH_NOT_PRECEDENCE SQL模式，可以获得以前的更高优先级的结果。

IGNORE_SPACE

允许函数名和  '(' 之间有空格。

MAXDB

等同 PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER。

MSSQL

等同 PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS。

MYSQL323

等同 NO_FIELD_OPTIONS、 HIGH_NOT_PRECEDENCE。

MYSQL40

等同 NO_FIELD_OPTIONS、 HIGH_NOT_PRECEDENCE。

NO_AUTO_CREATE_USER

防止GRANT自动创建新用户，除非还制定了密码

NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO影响AUTO_INCREMENT列的处理。一般情况，你可以向该列插入NULL或0生成下一个序列号。NO_AUTO_VALUE_ON_ZERO禁用0，因此只有NULL可以生成下一个序列号。

如果将0保存到表的AUTO_INCREMENT列，该模式会很有用。(不推荐采用该惯例)。例如，如果你用mysqldump转储表并重载，MySQL遇到0值一般会生成新的序列号，生成的表的内容与转储的表不同。重载转储文件前启用NO_AUTO_VALUE_ON_ZERO可以解决该问题。mysqldump在输出中自动包括启用NO_AUTO_VALUE_ON_ZERO的语句。

NO_BACKSLASH_ESCAPES

禁用反斜线字符（'\'）作为字符串内的退出字符。启用改模式，反斜线则成为普通字符

NO_DIR_IN_CREATE

创建表时，忽视所有 INDEX DIRECTORY和 DATA DIRECTORY指令。该选项对从复制服务器有用。

NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或未编译，可以防止自动替换存储引擎

NO_FIELD_OPTIONS

不要在 SHOW CREATE TABLE的输出中打印 MySQL专用列选项。该模式在可移植模式（ portability mode）下用于 mysqldump。

NO_KEY_OPTIONS

不要在 SHOW CREATE TABLE的输出中打印 MySQL专用索引选项。该模式在可移植模式（ portability mode）下用于 mysqldump。

NO_TABLE_OPTIONS

不要在 SHOW CREATE TABLE的输出中打印 MySQL专用表选项（例如 ENGINE）。该模式在可移植模式（ portability mode）下用于 mysqldump。

NO_UNSIGNED_SUBTRACTION

在减运算中，如果某个操作数没有符号，不要将结果标记为 UNSIGNED。请注意这样使 UNSIGNED BIGINT不能 100%用于上下文中。

NO_ZERO_DATE

在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用 IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。

NO_ZERO_IN_DATE

在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

ONLY_FULL_GROUP_BY

不要让 GROUP BY部分中的查询指向未选择的列。

ORACLE

等同 PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER。

PAD_CHAR_TO_FULL_LENGTH

PIPES_AS_CONCAT

POSTGRESQL

等同 PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS。

REAL_AS_FLOAT

将 REAL视为 FLOAT的同义词，而不是 DOUBLE的同义词。

STRICT_TRANS_TABLES

如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第 1行，则放弃该语句

TRADITIONAL

Make MySQL的行为象“传统”SQL数据库系统。该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。注释：一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎，这种方式不是你想要的，因为出现错误前进行的数据更改不会“滚动”，结果是更新“只进行了一部分”。

转载于:https://my.oschina.net/u/2457218/blog/639886