将 NULL 插入 MySQL 时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12489078/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting NULL into MySQL timestamp
提问by Rejeev Divakaran
I'm observing unexpected behavior with inserting/updating NULL in a MySQL timestamp column.
我正在观察在 MySQL 时间戳列中插入/更新 NULL 的意外行为。
Consider the following statements.
考虑以下陈述。
create table temp(id int, hireDate timestamp default 0);
insert into temp (id) values(1);
insert into temp (id, hireDate) values(2, null);
select * from temp;
id hireDate
-----------------------------
1
2 2012-09-19 10:54:10.0
The first insert (when hiredateis not specified in the SQL, hireDate is null(0)which is expected.
第一次插入（当hiredate未在 SQL 中指定时，hireDatenull(0)是预期的。
However when an explicit null passed in SQL, the current date time is inserted which is unexpected. Why does this happen?
但是，当在 SQL 中传递显式空值时，会插入当前日期时间，这是出乎意料的。为什么会发生这种情况？
Note: Hibernate uses second type of insert and hence it become an issue. How do I insert null into a timestamp column?
注意：Hibernate 使用第二种类型的插入，因此它成为一个问题。如何在时间戳列中插入空值？
采纳答案by Juris Malinens
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.
此外，您可以通过为任何 TIMESTAMP 列分配一个 NULL 值来将其初始化或更新为当前日期和时间，除非它已使用 NULL 属性定义为允许 NULL 值。
In order to allow a TIMESTAMP to be nullable, create it using the NULL attribute, or alter the table and add the NULL attribute. In a create statement, it would resemble.
为了允许 TIMESTAMP 可以为空，请使用 NULL 属性创建它，或更改表并添加 NULL 属性。在 create 语句中，它类似于。
CREATE TABLE t1 (tsvalue TIMESTAMP NULL, ... );
Inserting a NULLvalue into a TIMESTAMPfield with the NULL attribute set will result in the field being set to NULL instead of to NOW().
在设置了 NULL 属性的TIMESTAMP字段中插入NULL值将导致该字段被设置为 NULL 而不是 NOW()。
回答by Eric Leschinski
You can insert and update NULL into a MySQL timestamp.
您可以将 NULL 插入并更新到 MySQL 时间戳中。
Create the table:
创建表：
create table penguins(id int primary key auto_increment, the_time timestamp NULL);
Insert some rows:
插入一些行：
insert into penguins(the_time) values (now());
insert into penguins(the_time) values (null);
insert into penguins(the_time) values (0);
insert into penguins(the_time) values ('1999-10-10 01:02:03');
Which prints:
哪个打印：
select * from penguins
1 2015-01-23 15:40:36
2
3 0000-00-00 00:00:00
4 1999-10-10 01:02:03
The column called the_timewith datatype: timestampin 2nd row has the value NULL.
the_time使用数据类型调用的列：timestamp第 2 行的值为 NULL。
回答by Balkrishan Nagpal
You need to change the default value for the hireDate column. Default value should be null
您需要更改hiringDate 列的默认值。默认值应为空
This
这个
create table temp(id int, hireDate timestamp default 0);
Should be this:
应该是这样的：
create table temp(id int, hireDate timestamp null);
回答by druud62
Never store any application-value into a MySQL timestamp-type column. Use the timestamp-type only for database-server-side dynamic timestamping (see the DEFAULT and ON UPDATE column attributes). Make that explicit, by calling the columns 'mysql_row_created_at' and 'mysql_row_updated_at'.
永远不要将任何应用程序值存储到 MySQL 时间戳类型列中。仅将时间戳类型用于数据库服务器端动态时间戳（请参阅 DEFAULT 和 ON UPDATE 列属性）。通过调用列“mysql_row_created_at”和“mysql_row_updated_at”来明确这一点。
Be aware that MySQL physically stores its timestamp-type as a numerical UNIX-epoch-delta, so it has an implicit timezone of UTC, which makes it immune to timezone changes on the session (AKA connection) level.
请注意，MySQL 在物理上将其时间戳类型存储为数字 UNIX-epoch-delta，因此它具有 UTC 的隐式时区，这使其不受会话（AKA 连接）级别上的时区更改的影响。
Stay away from the 'datetime' type, even more so when storing any value that has a known timezone. The 'datetime' type is much like a string of packed numbers. There are no timezone details stored with that.
远离 'datetime' 类型，在存储任何具有已知时区的值时更是如此。'datetime' 类型很像一串压缩数字。没有存储时区详细信息。
The 'date' type is generally fine to use. Be aware though that in some contexts it will be intepreted as a datetime at midnight (of the active timezone), which will then lead to all 'datetime' related confusions that you should avoid.
'date' 类型通常很好用。请注意，在某些情况下，它会被解释为（活动时区的）午夜的日期时间，这将导致您应该避免的所有与“日期时间”相关的混淆。
For any application-side date-time-values, use an 'int unsigned', a 'bigint' (signed), a 'double' (signed) or a decimal(N,P), to simply store UNIX-epoch-deltas. Make sure to show the resolution in a column name suffix, if it is not seconds.
对于任何应用程序端日期时间值，使用“int unsigned”、“bigint”（有符号）、“double”（有符号）或小数（N,P）来简单地存储 UNIX-epoch-deltas . 如果不是秒，请确保在列名后缀中显示分辨率。
Examples:
例子：
`mysql_row_created_at` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
`mysql_row_updated_at` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`epoch` int unsigned NOT NULL,
`epoch_ms` bigint NOT NULL,
`epoch_us` bigint NOT NULL,
`epoch` double NOT NULL,
`epoch6` decimal(16,6),
Treat your database as much as dumb storage as possible. So do any conversions only on the application-side, never on the database-side.
尽可能将您的数据库视为愚蠢的存储。因此，仅在应用程序端进行任何转换，而不要在数据库端进行任何转换。
Good to know about:
很高兴知道：
SELECT @@GLOBAL.TIME_ZONE
, @@SESSION.TIME_ZONE
, @@SESSION.TIMESTAMP
, UNIX_TIMESTAMP(NOW(6))
;
回答by M Khalid Junaid
if you want to insert NULL in the hireDatecoloumn of your table then you have to provide null
如果要在hireDate表的列中插入 NULL，则必须提供 null
INSERT INTO temp (id, hireDate) VALUES(3, null);
INSERT INTO temp (id, hireDate) VALUES(3, null);
Because Datatypeof hireDateis timestampand it should be defined as null when creating column
由于Datatype的hireDate是timestamp，它应该在创建列时被定义为空
