方法一 定义语句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。
阅读(1684) | 评论(0) | 转发(0) |