How to set a default value as current time

劲松

方法一 定义语句TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
mysql> create table testabc (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> desc testabc;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       |
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       |
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into testabc (str) values ("demo");
Query OK, 1 row affected (0.01 sec)

mysql> select * from testabc;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2011-08-09 09:34:29 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> update testabc set str="demotest";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testabc;            
+----------+---------------------+
| str      | ts                  |
+----------+---------------------+
| demotest | 2011-08-09 09:34:29 |
+----------+---------------------+
1 row in set (0.00 sec)

mysql> alter table testabc modify ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from testabc;
+----------+---------------------+
| str      | ts                  |
+----------+---------------------+
| demotest | 2011-08-09 09:34:29 |
+----------+---------------------+
1 row in set (0.00 sec)

mysql> update testabc set str="demotestthird";    
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testabc;                 
+---------------+---------------------+
| str           | ts                  |
+---------------+---------------------+
| demotestthird | 2011-08-09 09:39:31 |
+---------------+---------------------+
1 row in set (0.00 sec)


方法二 触发器
CREATE TRIGGER `test_INSERT` BEFORE INSERT ON `testabc`
FOR EACH ROW BEGIN
        -- Set the udpate date
    Set new.ts = now();
END;

CREATE TRIGGER `test_UPDATE` BEFORE UPDATE ON `testabc`
FOR EACH ROW BEGIN
        -- Set the udpate date
    Set new.ts = now();
END;

补充说明:
TIMESTAMP字段类型：　
显示宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。TIMESTAMP值不能早于1970或晚于2037。
处理时可使用的函数 now() ,current_date()，此时时间被设置为00:00:00
TIMESTAMP值以UTC格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区。只要时区设定值为常量，便可以得到保存时的值。
如果保存一个TIMESTAMP值，应更改时区然后检索该值，它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。
当前的时区可以用作time_zone系统变量的值。
mysql> show global variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | WST    |
| time_zone        | SYSTEM |
+------------------+--------+

年份值2位表达的范围：
o        00-69范围的年值转换为2000-2069。
o        70-99范围的年值转换为1970-1999。