Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL date zero processing method", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in-depth, together to study and learn "MySQL date zero processing method"!

Foreword:

In the previous article, we introduced the query method for the date and time fields, and recently encountered the problem that the date value is zero. It turns out that it has something to do with the setting of sql_mode parameters, but it is not very clear. This article will explore how MySQL handles the problem of date values being zero.

1. Problem description

Here we say that the date is zero, which means that the year, month and day are zero, that is, '0000-00-00'. Obviously, this is an illegal date value, but due to design problems or historical problems, sometimes there is data with a similar date value of zero in the database, and inserting a zero date will report an error by default, which can be avoided by modifying the parameter sql_mode schema. The following shows how zero values are inserted by default:

# first create a test table CREATE TABLE `tzerodate` (`test id`int unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key', `year_ col`year DEFAULT NULL COMMENT', `date_ col`date', `dt_ col`datetime DEFAULT NULL COMMENT 'datetime time', `ts_ col`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp time', PRIMARY KEY (`increment_ id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='zerodate';# to view sql_mode mode mysql > select @ @ sql_mode +-+ | @ @ sql_mode | +- -+ | STRICT_TRANS_TABLES NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION | +-+ # insert year, month, and Day zero test # year 0000 insert successful mysql > insert into t_zerodate (year_col) values (0000) Query OK, 1 row affected (0.02 sec) can be inserted successfully mysql > insert into t_zerodate (date_col) values ('0000-0000'); ERROR 1292 (22007): Incorrect date value:' 0000-0000' for column 'date_col' at row 1mysql > insert into t_zerodate (date_col) values (' 0000-01-00') ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column' date_col' at row 1mysql > insert into t_zerodate (date_col) values ('0000-00-01'); ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1mysql > insert into t_zerodate (date_col) values (' 0000-01-01'); Query OK, 1 row affected (0.02 sec) 2.sql_mode change test

With regard to sql_mode, I originally wrote an article that sql_mode supports different combinations of multiple variables, and different sql_mode affects the SQL syntax and data validation rules supported by the server. Among them, NO_ZERO_IN_DATE and NO_ZERO_DATE affect the processing of date zero by MySQL. In the above test, we can find that in strict mode, when the sql_mode contains the two variables NO_ZERO_IN_DATE,NO_ZERO_DATE, neither the month nor the day can be inserted successfully at 00:00.

At first glance, the NO_ZERO_IN_DATE and NO_ZERO_DATE variables are similar, but what's the difference between them? Below we give the role of these two variables and do a specific test.

The NO_ZERO_DATE mode affects whether the server allows' 0000-00' as a valid date. The effect also depends on whether sql_mode has strict mode enabled.

If this mode is not enabled, '0000-0000' allows insertion and does not generate a warning.

If only this mode is enabled, '0000-0000' allows insertion but generates a warning.

If this mode and strict mode are enabled, '0000-00' will be considered illegal and insertion will result in an error. Unless there is both IGNORE, insert is allowed for INSERT IGNORE and UPDATE IGNORE,'0000-00-00' but a warning is generated.

The NO_ZERO_IN_DATE mode affects whether the server allows the insertion of a date where the year part is non-zero but the month or day part is 0. (for example, '2010-00-01' or '2010-01-00', but does not affect the date '0000-00-00'), the effect also depends on whether strict mode is enabled in sql_mode.

If this mode is not enabled, dates with partial zeros are allowed to be inserted without any warning.

If only this mode is enabled, the zero date is inserted as' 0000-00 'and a warning is generated.

If this mode and strict mode are enabled, dates that are zero are not allowed to be inserted unless both are specified by IGNORE. For INSERT IGNORE and UPDATE IGNORE, insert the zero date as' 0000-00-00 'and generate a warning.

At the same time, the official documentation states that although NO_ZERO_DATE and NO_ZERO_IN_DATE are not part of strict mode, they should be used in conjunction with strict mode, and a warning will be generated if NO_ZERO_DATE or NO_ZERO_IN_DATE is enabled without strict mode enabled, and vice versa, (sql_mode includes STRICT_TRANS_TABLES, which is generally considered to be enabled).

Let's test the effect of enabling and not enabling these two variables respectively in strict mode:

1. Enabling NO_ZERO_DATE and NO_ZERO_IN_DATE# in strict mode at the same time has been tested above. Under normal circumstances, neither month nor day can be successfully inserted at 00:00. # Test insert ignore intomysql > set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" below; Query OK, 0 rows affected (0 sec) mysql > truncate table t_zerodate Query OK, 0 rows affected (0.07sec) mysql > insert ignore into t_zerodate (date_col) values ('0000-0000'); Query OK, 1 row affected, 1 warning (0.01sec) mysql > insert ignore into t_zerodate (date_col) values (' 2010-0000'); Query OK, 1 row affected, 1 warning (01sec) mysql > insert ignore into t_zerodate (date_col) values ('2010-01-00') Query OK, 1 row affected, 1 warning (0.01sec) mysql > insert ignore into t_zerodate (date_col) values ('2010-00-01'); Query OK, 1 row affected, 1 warning (0.01sec) mysql > select date_col from t_zerodate +-+ | date_col | +-+ | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | +-+ # conclusion: under normal circumstances, it can be inserted successfully if it is not 00:00 on either month or day. For INSERT IGNORE, insertion is allowed, but an alarm is generated and the zero date is saved as' 0000-00'. two。 NO_ZERO_DATEmysql > set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" is not enabled in strict mode; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > truncate table tweezerodate, query OK, 0 rows affected (0.08 sec) mysql > insert into t_zerodate (date_col) values ('0000-00-00') Query OK, 1 row affected (0.01 sec) mysql > insert into t_zerodate (date_col) values ('2010-00-01'); ERROR 1292 (22007): Incorrect date value: '2010-00-01' for column 'date_col' at row 1mysql > insert into t_zerodate (date_col) values (' 2010-01-00'); ERROR 1292 (22007): Incorrect date value: '2010-01-00' for column 'date_col' at row 1mysql > insert into t_zerodate (date_col) values (' 0000-00-01') ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1mysql > insert into t_zerodate (date_col) values (' 0000-01-00'); ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column' date_col' at row 'conclusion:' 0000-00-00' can be inserted normally, and other dates with zero months and days will report an error. For INSERT IGNORE, insertion is allowed, but an alarm is generated and the zero date is saved as' 0000-00'. 3. NO_ZERO_IN_DATEmysql > set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" is not enabled in strict mode; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > truncate table tweezerodate, query OK, 0 rows affected (0.07 sec) mysql > insert into t_zerodate (date_col) values ('0000-00-00') ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column' date_col' at row 1mysql > insert into t_zerodate (date_col) values ('2010-0000'); Query OK, 1 row affected (0.01sec) mysql > insert into t_zerodate (date_col) values (' 2010-00-01'); Query OK, 1 row affected (0.00 sec) mysql > insert into t_zerodate (date_col) values ('2010-01-00') Query OK, 1 row affected (0.00 sec) mysql > insert ignore into t_zerodate (date_col) values ('0000-00-00'); Query OK, 1 row affected, 1 warning (0.01 sec) mysql > select date_col from t_zerodate +-+ | date_col | +-+ | 2010-00 | | 2010-00-01 | | 2010-01-00 | | 0000-00-00 | +-+ # conclusion: an error is reported when '0000-00-00' is inserted, and the dates with zero months and days can be inserted normally. For INSERT IGNORE, the insertion of '0000-00-00' is allowed, but an alarm is generated. 4. Mysql > set session sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" is not enabled for both NO_ZERO_DATE and NO_ZERO_IN_DATE in strict mode; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > truncate table tweezerodate and query OK, 0 rows affected (0.09 sec) mysql > insert into t_zerodate (date_col) values ('0000-00-00') Query OK, 1 row affected (0.01 sec) mysql > insert into t_zerodate (date_col) values ('2010-00-00'); Query OK, 1 row affected (0. 01 sec) mysql > insert into t_zerodate (date_col) values ('2010-00-01'); Query OK, 1 row affected (0. 01 sec) mysql > insert into t_zerodate (date_col) values ('2010-01-00') Query OK, 1 row affected (0. 01 sec) # conclusion: '0000-0000' and other dates with zero months and days can be inserted normally. 3. Conclusions and suggestions

To sum up, NO_ZERO_DATE mode affects the insertion of '0000-00' dates, while NO_ZERO_IN_DATE mode affects the insertion of dates with zero months and days except' 0000-0000'. In addition, regardless of the mode, the YEAR type allows 0000 insertion, and these two variables affect the processing of zero dates in the three field types of DATE, DATETIME, and TIMESTAMP.

Whether we want to enable these two modes or not depends on the business requirements. If your business needs to insert a zero date, you can choose not to include NO_ZERO_DATE and NO_ZERO_IN_DATE in sql_mode. For example, a field is required to be set to type DATE and not empty, and the default value is set to '0000-00-00'. In general, NO_ZERO_DATE and NO_ZERO_IN_DATE recommend both or not at the same time, the need to insert a zero date value can remove both, and without such a need, both can be retained. As a reminder, the official documentation says that these two variables will no longer be used as independent variables in future versions, so they are not officially recommended.

Thank you for your reading, the above is the content of "the processing method of zero date in MySQL". After the study of this article, I believe you have a deeper understanding of the problem of dealing with zero date in MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.