In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.