Detailed explanation of Mysql database date datetime type setting 0000 00 00 default value of default error reporting problem

Phenomenon: After MySQL version 5.7, the default value of date and datetime types is set to "0000-00-00", and there is an exception: Invalid default value for 'time'

Reason: View the current sql_mode configuration in a command-line window:

select @@sql_mode;

The results are as follows:

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION

Among them, NO_ZERO_IN_DATE and NO_ZERO_DATE prohibit the date and time of 0000. So in the configuration file of mysql, reset sql_mode and remove these two items.

Solution

1. Under windows system:

Use SET [SESSIONGLOBAL] sql_mode= 'modes'

Note: SESSION (default option): indicates that it is effective in the current reply; GLOBAL (need to restart): Indicates global effective

You can also modify the my. ini configuration file

***
 Demo : 
    SET GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, 
    ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

1. Under Linux system:

Modify the my. cnf file to add in [mysqld]

 sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

Restart MySQL after modification

2. Under 2. Mac system:

There is no my. cnf file in MacOS by default. If you need to customize MySql, copy any one. cnf file in the/usr/local/mysql/support-files/directory. The author copied my-default.cnf, put it in other directories, changed its name to my.cnf after being modified according to the above, then copied it to/etc directory and restarted mysql.

Extension of sql_mode Schema Problem

sql_mode Common Values Description

1. SQL syntax support class

ONLY_FULL_GROUP_BY

For an GROUP BY aggregation operation, the SQL is not valid if the column in SELECT, the column in the HAVING or the column in the ORDER BY clause does not appear in GROUP BY. It is understandable, because there are contradictions when not shown in the column of group by. It is enabled by default in 5.7, so it is necessary to pay attention to the following during the process of upgrading from 5.6 to 5.7:

ANSI_QUOTES

With ANSI_QUOTES enabled, a string cannot be referenced in double quotation marks because it is interpreted as an identifier and acts like ` 1 '.

When it is set, update t set f1= ""... will report a syntax error such as Unknown column ''in'' field list.

PIPES_AS_CONCAT

Will be treated as a string concatenation operator rather than an operator, which is similar to the Oracle database and the string splicing function CONCAT ()

NO_TABLE_OPTIONS

SHOW CREATE TABLE does not output syntax parts specific to mysql, such as ENGINE, which should be considered when migrating across DB categories using mysqldump

NO_AUTO_CREATE_USER

Literally, users are not created automatically. When authorizing MySQL users, we are used to using GRANT... ON... TO dbuser to create users from Drop 1. Setting this option is similar to the oracle operation, and the user must be established before authorization. 5.7. 7 also defaulted at the beginning.

2. Data checking classes

NO_ZERO_DATE

The date '0000-00-00' is considered illegal, depending on whether the following strict mode is set. 1. If strict mode is set, NO_ZERO_DATE naturally satisfies. However, if it is INSERT IGNORE or UPDATE IGNORE, '0000-00-00' still allows and only displays warning 2. If NO_ZERO_DATE is set in non-strict mode, the effect is the same as above, '0000-00-00' allows but displays warning; If NO_ZERO_DATE, no warning are not set, treat them as perfectly legal values. 3. NO_ZERO_IN_DATE is similar to the above, except whether the control date and day can be 0, that is, whether 2010-01-00 is legal.

NO_ENGINE_SUBSTITUTION

When ENGINE is specified using ALTER TABLE or CREATE TABLE, the required storage engine is disabled or not compiled. When NO_ENGINE_SUBSTITUTION is enabled, an error is thrown directly; When this value is not set, CREATE is replaced with the default storage engine, ATLER is not changed, and 1 warning is thrown.

STRICT_TRANS_TABLES

Set it to indicate that strict mode is enabled.

Note that STRICT_TRANS_TABLES is not a combination of several strategies, but refers to INSERT alone. How to deal with the occurrence of less or invalid values in UPDATE: Pass ''to int, which is illegal in strict mode, and change to 0 if non-strict mode is enabled, resulting in an warning
Out Of Range, becomes the insertion maximum boundary value

A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition

mysql version 5.0 and above supports three sql_mode modes: set @ @ sql_mode=xxx schema name;

We often set sql_mode as ANSI, STRICT_TRANS_TABLES and TRADITIONAL, where ANSI and TRADITIONAL are several combinations of the above.

3. ANSI mode: loose mode, check the inserted data. If it does not meet the defined type or length, adjust or truncate the data type and save it, and report warning warning.

4. TRADITIONAL mode: Strict mode, when inserting data into MySQL database, strict verification of data is carried out to ensure that wrong data cannot be inserted, and error error is reported. When used for transactions, the transaction will be rolled back.

3. STRICT_TRANS_TABLES mode: Strict mode, strict verification of data, error data cannot be inserted, and error error is reported.