Shulou(Shulou.com)06/01 Report--

Phenomenon: after MySQL5.7 version, the default value of date and datetime type is "0000-00-00". Exception occurs: Invalid default value for 'time'

Reason: view the current sql_mode configuration in the 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

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

Solution.

Under the 1.windows system:

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

Note: SESSION (default): it takes effect in the current reply; GLOBAL (needs to be restarted): it takes effect 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'

Under the 1.Linux system:

Modify the my.cnf file and add it in [mysqld]

The copy code is as follows: 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 is completed.

Under the 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. The author copied my-default.cnf, put it in another directory, modified it according to the above, renamed it to my.cnf, then copied it to the / etc directory and restarted mysql.

Problem extension of sql_mode Model

Description of common values for sql_mode

1.SQL syntax support class

ONLY_FULL_GROUP_BY

For GROUP BY aggregation operations, if the columns in the SELECT, HAVING, or ORDER BY clause do not appear in the GROUP BY, then the SQL is illegal. It is understandable because there is a contradiction in the display that is not found in the group by list. It is enabled by default in 5.7, so you should pay attention to the process of upgrading from 5.6 to 5.7:

ANSI_QUOTES

When ANSI_QUOTES is enabled, you cannot refer to a string in double quotes because it is interpreted as an identifier and works 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 concatenation operator of a string rather than an operator, which is the same as an Oracle database and similar to the string concatenation function CONCAT ()

NO_TABLE_OPTIONS

Mysql-specific syntax parts, such as ENGINE, are not output when using SHOW CREATE TABLE, which needs to be considered when migrating across DB types using mysqldump

NO_AUTO_CREATE_USER

Literally, users are not created automatically. We are used to using GRANT when authorizing MySQL users. ON... TO dbuser is on the way to create users. Setting this option is similar to the oracle operation, where a user must be established before authorization. 5.7.7 also defaulted at the beginning.

two。 Data check class

NO_ZERO_DATE

It is considered that the date '0000-0000' is illegal, which is related to whether to set the later strict mode. 1. If strict mode is set, NO_ZERO_DATE is naturally satisfied. But if it is INSERT IGNORE or UPDATE IGNORE,'0000-00-00', it is still allowed and only 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 is not set, as a perfectly legal value. The 3.NO_ZERO_IN_DATE situation is similar to the above, except that it controls whether the date and day can be 0, that is, whether 2010-01-00 is legal.

NO_ENGINE_SUBSTITUTION

What to do if the required storage engine is disabled or uncompiled when you specify ENGINE using ALTER TABLE or CREATE TABLE. 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 does not change, and a warning is thrown.

STRICT_TRANS_TABLES

Set it to enable strict mode.

Note that STRICT_TRANS_TABLES is not a combination of several strategies. It refers to how to deal with the low or invalid values of INSERT and UPDATE: passing''to int is illegal in strict mode, and becomes 0 if non-strict mode is enabled, resulting in a warning.

Out Of Range, to insert the 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

Three sql_mode modes are supported in mysql5.0 above: set @ @ sql_mode=xxx schema name

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

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

4.TRADITIONAL mode: strict mode, when inserting data into MySQL database, the data is strictly checked to ensure that the wrong data can not be inserted and error error is reported. When used for a transaction, a rollback of the transaction occurs.

3.STRICT_TRANS_TABLES mode: strict mode, strict verification of data, error data can not be inserted, report error error.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.