Detailed explanation of the error problem when setting the default value of 0000-00-00 in the date and datetime types of Mysql database

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

Reason: Check the current sql_mode configuration in the command line window:

select @@sql_mode;

Here are the results:

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

The two options NO_ZERO_IN_DATE and NO_ZERO_DATE prohibit dates and times such as 0000. Therefore, in the mysql configuration file, reset sql_mode and remove these two items.

Solution

1. Under Windows:

Use SET [SESSION|GLOBAL] sql_mode='modes'

Note: SESSION (default option): means it is effective in the current session; GLOBAL (requires restart): means it is effective globally

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. Linux system:

Modify the my.cnf file and add [mysqld]

After the modification is completed, be sure to restart MySQL

2. Mac system:

There is no my.cnf file by default in MacOS. If you need to customize MySQL, copy any .cnf file in the /usr/local/mysql/support-files/ directory. I copied my-default.cnf and put it in another directory. After completing the above modifications, I renamed it to my.cnf, copied it to the /etc directory, and restarted mysql.

sql_mode mode problem expansion

sql_mode Common Value Description

1.SQL syntax support class

• ONLY_FULL_GROUP_BY

For GROUP BY aggregation operations, if the columns in the SELECT, HAVING, or ORDER BY clauses do not appear in GROUP BY, then the SQL is illegal. This is understandable, because the columns not in the group by column query will be contradictory. It is enabled by default in 5.7, so you need to pay attention to the process of upgrading from 5.6 to 5.7:

• ANSI_QUOTES

When ANSI_QUOTES is enabled, you cannot use double quotes to quote strings, because they are interpreted as identifiers, the same as `.

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

• PIPES_AS_CONCAT

Treat || as a string concatenation operator rather than an operator, which is the same as Oracle database and similar to the string concatenation function CONCAT()

• NO_TABLE_OPTIONS

SHOW CREATE TABLE will not output MySQL-specific syntax, such as ENGINE. This needs to be considered when using mysqldump to migrate across DB types.

• NO_AUTO_CREATE_USER

Literally does not automatically create users. When granting authorization to a MySQL user, we are accustomed to using GRANT ... ON ... TO dbuser to create the user at the same time. After setting this option, it is similar to Oracle operation. Users must be created before authorization. This is also the default starting from 5.7.7.

2. Data inspection

• 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 is naturally satisfied. But if it is INSERT IGNORE or UPDATE IGNORE, '0000-00-00' is still allowed and only a warning is displayed. 2. If NO_ZERO_DATE is set in non-strict mode, the effect is the same as above, '0000-00-00' is allowed but a warning is displayed; if NO_ZERO_DATE is not set, no warning is given, and it is treated as a completely legal value. 3. NO_ZERO_IN_DATE is similar to the above situation, except that it controls whether the date and day can be 0, that is, whether 2010-01-00 is legal.

• NO_ENGINE_SUBSTITUTION

When specifying ENGINE with ALTER TABLE or CREATE TABLE, what should I do if 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 uses the default storage engine instead, ATLER does not make changes, and throws a warning.

• STRICT_TRANS_TABLES

Setting this enables strict mode.

Note that STRICT_TRANS_TABLES is not a combination of several strategies, but refers to how to handle missing or invalid values ​​in INSERT and UPDATE: Passing '' to int is illegal in strict mode. If non-strict mode is enabled, it becomes 0 and a warning is generated.
Out Of Range, becomes the maximum boundary value of the insert

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 5.0 and above versions support three sql_mode modes: set @@sql_mode=xxx mode name;

The sql_mode we often set is ANSI, STRICT_TRANS_TABLES, TRADITIONAL, where ANSI and TRADITIONAL are combinations of the above.

3.ANSI mode: Loose mode, check the inserted data. If it does not meet the defined type or length, adjust the data type or truncate it before saving, and issue a warning.

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

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

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.