Incorrect datetime value
How 00:00:00 was inserted into the database in the 0000-00-00, does the application have this requirement?
For the first question, it is still necessary to return to the definition of date time in MySQL, stating in the official document that MySQL allows ' 0000-00-00 ' to be saved as "pseudo date" (if No_zero_date SQL mode is not used). This is easier in some cases than using a null value (and the data and indexes take up less space). Then, it is to look at the parameters in the Sql_mode;
Root@db06:40:48>show variables like ' Sql_mode ';
+ ————— + ——————————————————————————————————————————-+
| variable_name | Value |
+ ————— + ——————————————————————————————————————————-+
| Sql_mode | Strict_trans_tables,strict_all_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,traditional,no_ Auto_create_user |
+ ————— + ——————————————————————————————————————————-+
1 row in Set (0.00 sec)
Parameter contains No_zero_date,
In strict mode, do not make ' 0000-00-00 ' a legal date. You can still insert the 0 issue with the Ignore option. In a non-strict mode, the date can be accepted, but a warning is generated.
No_zero_in_date
In strict mode, do not accept the month or day part is 0 of the date. If you use the Ignore option, our date is inserted in ' 0000-00-00 '. In a non-strict mode, the date can be accepted, but a warning is generated.
The problem can be solved by changing sql_mode: removing No_zero_date and No_zero_in_date:
ROOT@DB 07:05:21>set Global sql_mode= ' Strict_trans_tables,strict_all_tables,error_for_division_by_zero, Traditional,no_auto_create_user ';
Query OK, 0 rows Affected (0.00 sec)
Root@db 07:07:01>show variables like '%sql_mode% ';
+ ————— + ——————————————————————————————————————————-+
| variable_name | Value |
+ ————— + ——————————————————————————————————————————-+
| Sql_mode | Strict_trans_tables,strict_all_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,traditional,no_ Auto_create_user |
+ ————— + ——————————————————————————————————————————-+
1 row in Set (0.00 sec)
Although No_zero_date and no_zero_in_date are removed, there are two parameters in the argument that exit the session, and the value of the view parameter is still invalid:
Root@db 07:07:10>exit
Bye
[Mm-writable@dev ~]
$mysql-uroot DB
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 23505133
Server Version:5.1.37-log Source Distribution
Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
Root@db 07:07:41>show variables like '%sql_mode% ';
+ ————— + ——————————————————————————————————————————-+
| variable_name | Value |
+ ————— + ——————————————————————————————————————————-+
| Sql_mode | Strict_trans_tables,strict_all_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,traditional,no_ Auto_create_user |
+ ————— + ——————————————————————————————————————————-+
1 row in Set (0.00 sec)
Why this happens, is not the parameter set wrong, view the other library Sql_mode parameters, there is no setting, but this library sql_mode set up, it seems to need to do a detailed understanding of Sql_mode:
Simply put, Sql_mode is the set of SQL syntax that MySQL should support, and what kind of data validation checks. This makes it easier to use MySQL in different environments and to use MySQL in conjunction with other database servers. You can do this by using the set [session| GLOBAL] sql_mode= ' modes ' statement sets the Sql_mode variable to change the SQL schema. You need to have super permissions when you set up a global variable, and you can affect the operations of all clients that have been connected since then. Setting the session variable affects only the current client. Any client can change its own session Sql_mode value at any time.
The sql_mode of the current database are:
Strict_trans_tables,strict_all_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,traditional,no_ Auto_create_user
These values, this is the Sql_mode set to: Traditional mode, so only remove no_zero_in_date,no_zero_date is not good, but also to remove traditional;
ROOT@DB 07:07:43>set Global sql_mode= ' Strict_trans_tables,strict_all_tables,error_for_division_by_zero,no_auto _create_user ';
Query OK, 0 rows Affected (0.00 sec)
When you retire, log on again:
Root@db 07:20:47>show variables like '%sql_mode% ';
+ ————— + ———————————————————————————— –+
| variable_name | Value |
+ ————— + ———————————————————————————— –+
| Sql_mode | Strict_trans_tables,strict_all_tables,error_for_division_by_zero,no_auto_create_user |
+ ————— + ———————————————————————————— –+
1 row in Set (0.00 sec)
Root@db 07:20:48>alter TABLE ' DB '. ' Ali_mall_user ' ADD COLUMN ' Status_mode ' TINYINT UNSIGNED after ' op_invest_id ';
Query OK, affected rows (0.66 sec)
records:106 duplicates:0 warnings:0
You've seen that you can modify the structure of the table, and now MySQL allows DDL.
For the second question: Why insert the 0000-00-00 00:00:00.
。 Strict mode allows dates to be used in the "0" section, such as ' 2004-04-00 ' or ' 0 ' dates. To prohibit, you should enable No_zero_in_date and no_zero_date SQL mode on a strict mode basis.
。 Each time type has a valid range of values and a value of "0" that uses the "0" value when specifying a value that is not valid for MySQL to represent.
。 Invalid datetime, date, or timestamp value is converted to the corresponding type of "0" value (' 0000-00-00 00:00:00 ', ' 0000-00-00 ' or 00000000000000).
From the above Sql_mode can be seen in strict mode (enable Strict_trans_tables or Strict_all_tables mode) can be inserted: 0000-00-00 00:00:00 ', but it also started the traditional,traditional in the No_zero_in_date and no_zero_date mode, so the early 0000-00-00 00:00:00 data was inserted, There is a change in the Sql_mode, which eventually caused the insertion of the data inserted before the change in order to be illegal, it will appear above the total problem.
Other:
the difference between Strict_trans_tables and strict_all_tables in Sql_mode
The official description of MySQL Presents:
Strict mode controls how MySQL handles illegal or missing input values. There are several reasons to make a value illegal. For example, the data type is incorrect, does not fit the column, or is out of range. The value is lost when the newly inserted row does not contain a value that does not show the definition default clause for a column.
for a transaction table, an error occurs when the Strict_all_tables or Strict_trans_tables mode is enabled, if there are illegal or missing values in the statement. Statement is discarded and scrolled.
for non-transaction tables, if a bad value occurs on line 1th of the insert or update, the behavior of both modes is the same. Statement is discarded and the table remains unchanged. If a statement inserts or modifies multiple rows, and a bad value appears in the 2nd or subsequent row, the result depends on which strict option is enabled:
• Return an error for Strict_all_tables,mysql and ignore the remaining rows. However, in this case, the preceding row has been inserted or updated. This means you can partially update it, which may not be what you want. To avoid this, it is best to use a single-line statement, because you can discard the table without changing it.
• Converts an illegal value to the closest legal value to the column and inserts an adjusted value for Strict_trans_tables,mysql. If the value is lost, MySQL inserts an implicit default value in the column. In any case, MySQL generates a warning instead of giving an error and continues executing the statement.
My example Analysis:
1: If the SQL statement operates on a transaction table (InnoDB type), the Strict_all_tables or strict_trans_tables pattern has the same effect on the data.
It is to be noted that:
In a transaction if a statement of SQL update data does not make an error, then the statement below is not executed, and the statement that has been executed above will commit when the transaction ends
This is the need for us to catch the error and let the transaction roll back.
Examples are as follows:
CREATE TABLE ' T1 ' (
' F1 ' int (one) not NULL auto_increment,
' F2 ' varchar (2) Not NULL,
UNIQUE KEY ' F1 ' (' F1 ')
) Engine=innodb auto_increment=12 DEFAULT Charset=utf8 checksum=1 delay_key_write=1 row_format=dynamic
DELIMITER $$
DROP PROCEDURE IF EXISTS ' pr_test ' $$
CREATE definer= ' root ' @ ' 127.0.0.1 ' PROCEDURE ' pr_test ' ()
BEGIN
DECLARE EXIT HANDLER for SQLEXCEPTION BEGIN ROLLBACK; End;
START TRANSACTION;
INSERT into T1 (F2) VALUES (' CD ');
INSERT into T1 (F2) VALUES (' abc ');
INSERT into T1 (F2) VALUES (' de ');
COMMIT;
end$$
DELIMITER;
2: If the SQL statement operates a transaction table.
Two differences are
INSERT into T1 (F2) VALUES (' AB '), (' ACD ');
If it's strict_all_tables,
The result:
F1 F2
1 AB
If it's strict_trans_tables,
The result:
F1 F2
1 AB
2 ac
