- 论坛徽章:
- 0
|
其实问题没有想象的那么简单:
To specify automatic default or updating for a TIMESTAMP column other than the first one, you must suppress the automatic initialization and update behaviors for the first TIMESTAMP column by explicitly assigning it a constant DEFAULT value (for example, DEFAULT 0 or DEFAULT '2003-01-01 00:00:00'). Then for the other TIMESTAMP column, the rules are the same as for the first TIMESTAMP column, except that if you omit both of the DEFAULT and ON UPDATE clauses, no automatic initialization or updating occurs.
Example. These statements are equivalent:
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
Beginning with MySQL 4.1.3, you can set the current time zone on a per-connection basis, as described in Section 5.10.8, “MySQL Server Time Zone Support”. TIMESTAMP values still are stored in UTC, but are converted from the current time zone for storage, and converted back to the current time zone for retrieval. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different than the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.
Beginning with MySQL 4.1.6, you can include the NULL attribute in the definition of a TIMESTAMP column to allow the column to contain NULL values. For example:
CREATE TABLE t (
ts1 TIMESTAMP NULL DEFAULT NULL,
ts2 TIMESTAMP NULL DEFAULT 0,
ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
Before MySQL 4.1.6 (and even as of 4.1.6 if the NULL attribute is not specified), setting the column to NULL sets it to the current timestamp. Note that a TIMESTAMP column which allows NULL values not take on the current timestamp except under one of the following conditions:
Its default value is defined as CURRENT_TIMESTAMP
NOW() or CURRENT_TIMESTAMP is inserted into the column
In other words, a TIMESTAMP column defined as NULL will auto-initialize only if it is created using a definition such as the following:
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
Otherwise — that is, if the TIMESTAMP column is defined to allow NULL values but not using DEFAULT TIMESTAMP, as shown here…
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL);
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
…then you must explicitly insert a value corresponding to the current date and time, for example:
INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
· 要为TIMESTAMP列而不是第1列指定自动默认或更新,必须通过将第1个TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。(例如,DEFAULT 0或DEFAULT'2003-01-01 00:00:00')。然后,对于其它TIMESTAMP列,规则与第1个TIMESTAMP列相同,例外情况是不能忽略DEFAULT和ON UPDATE子句。如果这样做,则不会自动进行初始化或更新。
例如:下面这些语句是等效的:
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
可以对每个连接设置当前的时区,相关描述参见5.10.8节,“MySQL服务器时区支持”。TIMESTAMP值以UTC格式保存,存储时对当前的时区进行转换,检索时再转换回当前的时区。只要时区设定值为常量,便可以得到保存时的值。如果保存一个TIMESTAMP值,应更改时区然后检索该值,它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作time_zone系统变量的值。
可以在TIMESTAMP列的定义中包括NULL属性以允许列包含NULL值。例如:
CREATE TABLE t
(
ts1 TIMESTAMP NULL DEFAULT NULL,
ts2 TIMESTAMP NULL DEFAULT 0,
ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
如果未指定NULL属性,将列设置为NULL设置则会将它设置为当前的时间戳。请注意允许NULL值的TIMESTAMP列不会采用当前的时间戳,除非要么其 默认值定义为CURRENT_TIMESTAMP,或者NOW()或CURRENT_TIMESTAMP被插入到该列内。换句话说,只有使用如下定义创建,定义为 NULL的TIMESTAMP列才会自动更新:
CREATE TABLE t (ts NULLDEFAULT CURRENT_TIMESTAMP);
否则-也就是说,如果使用NULL而不是DEFAULT TIMESTAMP来定义TIMESTAMP列,如下所示...
CREATE TABLE t1 (ts NULL DEFAULT NULL);
CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');
...则必须显式插入一个对应当前日期和时间的值。例如:
INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
各位查过手册没有? |
|