MySQL timestamp NOT NULL insert NULL problem

Explicit_defaults_for_timestamp

MySQL5.6 Version Introduction

Explicit_defaults_for_timestamp

to control the timestamp NULL handling of Values

timestamp not NULL insert null value, no error, no warning

if the my.cnf Zhong Explicit_defaults_for_timestamp =1

The value will be inserted when the error indicates that the column can not is null

It is recommended to turn on this value

Mysql> Show variables like '%explicit_defaults_for_timestamp% ';
+---------------------------------+-------+
| variable_name | Value |
+---------------------------------+-------+
| Explicit_defaults_for_timestamp | OFF |
+---------------------------------+-------+
1 row in Set (0.00 sec)

Mysql> Show CREATE TABLE Helei\g
1. Row ***************************
Table:helei
Create table:create Table ' Helei ' (
' A ' timestamp not NULL DEFAULT current_timestamp
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.08 sec)

Mysql> select * from Helei;
Empty Set (0.03 sec)

mysql> INSERT into Helei values (NULL);
Query OK, 1 row affected (0.39 sec)

Mysql> select * from Helei;
+---------------------+
| A |
+---------------------+
| 2016-05-26 11:44:24 |
+---------------------+
1 row in Set (0.03 sec)

Can see

Explicit_defaults_for_timestamp

of the inserted NULL the value changes to the current time and is not Not NULL the Limit

And the value cannot be modified dynamically, the library must be restarted before it can be changed

Mysql> set globalexplicit_defaults_for_timestamp=0;

ERROR 1238 (HY000): Variable ' Explicit_defaults_for_timestamp ' is a read only Variable

we are my.cnf After you restart the library, you can see NULL value has not been allowed to be inserted.

Mysql> select * from Helei;
+---------------------+
| A |
+---------------------+
| 2016-05-26 11:44:24 |
| 2016-05-26 11:45:46 |
+---------------------+
2 rows in Set (0.00 sec)

mysql> INSERT INTO helei values (NULL);
ERROR 1048 (23000): Column ' A ' cannot be null

mysql> INSERT into Helei values (NULL);
ERROR 1048 (23000): Column ' A ' cannot be null

This article is from the "Age volt" blog, please make sure to keep this source http://suifu.blog.51cto.com/9167728/1783400

MySQL timestamp NOT NULL insert NULL problem