How MySQL handles implicit default values

Some students said that they encountered the problem of inconsistency between master and slave.

The general situation is that the slave database is created by exporting and importing data using mysqldump. After the creation is successful, when using mysqldump to verify whether the table structure of the master and slave is consistent, it is found that some table definitions are inconsistent:

From the comparison results, we can see that on the slave side, "default null" is added to the definitions of three columns.

I suspected that the environment had been modified by someone, but I finally confirmed that the environment had not been tampered with. Then I did another test, using mysqldump to export the data, and using source to import the data into the slave database. I found that this phenomenon still occurred, and asked if there was a bug in the source command!

In fact, this is related to how MySQL handles implicit default values ​​internally.

If a data type does not include an explicit default value, MySQL determines the default value according to the following rules:
If the column can take NULL values, an explicit default null clause is added to the column definition. If the column cannot take NULL values, an explicit default clause is not added to the column definition.

For the case where data is entered into a NOT NULL column without an explicit DEFAULT clause, if an INSERT or REPLACE statement does not include a value for the column, or if an UPDATE statement sets the column to NULL , MySQL handles the column according to the SQL Mode in effect:
If strict SQL mode is enabled, an error will occur for transactional tables and the statement will be rolled back. For nontransactional tables, an error occurs, but if this error occurs in the second or subsequent row of a multirow statement, all rows before the error have already been inserted.
If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

Assume that table t is defined as follows:

mysql> create table t(i int not null);

Here, i has no explicit default value.
In strict SQL mode, the following statements will generate errors and the insertion will fail.

mysql> show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

 mysql> INSERT INTO t VALUES();
ERROR 1364 (HY000): Field 'i' doesn't have a default value
mysql> INSERT INTO t VALUES(DEFAULT);
ERROR 1364 (HY000): Field 'i' doesn't have a default value
mysql> INSERT INTO t VALUES(DEFAULT(i));
ERROR 1364 (HY000): Field 'i' doesn't have a default value
mysql>

In non-strict SQL mode:

mysql> SET @@sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

 mysql> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)

 mysql> INSERT INTO t VALUES();
Query OK, 1 row affected, 1 warning (0.00 sec)

 mysql> INSERT INTO t VALUES(DEFAULT);
Query OK, 1 row affected, 1 warning (0.01 sec)

 mysql> INSERT INTO t VALUES(DEFAULT(i));
ERROR 1364 (HY000): Field 'i' doesn't have a default value
mysql> select * from t;
+---+
| i |
+---+
| 0 |
| 0 |
+---+
2 rows in set (0.01 sec)

 mysql>

For a given table, the SHOW CREATE TABLE statement shows which columns have an explicit DEFAULT clause. The implicit default values ​​are defined as follows:
For numeric types, the default is 0, but for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.
For date and time types other than TIMESTAMP, the default value is the "zero" value for that type. The same is true for TIMESTAMP if the explicit_defaults_for_timestamp system variable is enabled. Otherwise, for the first TIMESTAMP column in the table, the default is the current date and time.
For string types other than ENUM, the default value is an empty string. For ENUM, the default value is the first enumeration value.

mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.04 sec)

 mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

 mysql>

The above is the details of how MySQL handles implicit default values. For more information about MySQL implicit default values, please pay attention to other related articles on 123WORDPRESS.COM!