MySQL timestamp NOT NULL insert NULL problem

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.