mysql 5.5與5.6 timestamp 字段 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP的區別

本文章來給各位同學介紹關於mysql 5.5與5.6 timestamp 字段 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP的區別,希望此教程對各位有幫助。

mysql5.6數據庫:

 代碼如下 復制代碼 [BIGHD]([email protected]) [cm]> SELECT version();
+------------+
| version()  |
+------------+
| 5.6.13-log |
+------------+
1 ROW IN SET (0.02 sec)
 
[BIGHD]([email protected]) [cm]> ALTER TABLE s__uk ADD `last_op_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 ROWS affected (0.99 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
[BIGHD]([email protected]) [cm]> SELECT id, last_op_time FROM s__uk  LIMIT 1;
+----+---------------------+
| id | last_op_time        |
+----+---------------------+
|  1 | 2013-10-10 10:17:57 |
+----+---------------------+
1 ROW IN SET (0.00 sec)
 

 
 
mysql5.5數據庫:

 代碼如下 復制代碼
(root@localhost) [cm]> SELECT version();
+------------+
| version()  |
+------------+
| 5.5.15-log |
+------------+
1 ROW IN SET (0.00 sec)
 
(root@localhost) [cm]> ALTER TABLE s__uk ADD `last_op_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 2523 ROWS affected (0.99 sec)
Records: 2523  Duplicates: 0  Warnings: 0
 
(root@localhost) [cm]> SELECT last_op_time FROM s__uk  LIMIT 1;
+---------------------+
| last_op_time        |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 ROW IN SET (0.00 sec)

另外，有沒有考慮過如果只更新這個字段會怎樣？會是指定的時間，還是自動生成的時間？

 代碼如下 復制代碼 (root@localhost) [cm]> UPDATE s__uk SET last_op_time='2012-12-12 00:00:00' WHERE id=1;
Query OK, 1 ROW affected (0.07 sec)
ROWS matched: 1  Changed: 1  Warnings: 0
 
(root@localhost) [cm]> SELECT id, last_op_time FROM s__uk  LIMIT 1;
+----+---------------------+
| id | last_op_time        |
+----+---------------------+
|  1 | 2012-12-12 00:00:00 |
+----+---------------------+
1 ROW IN SET (0.01 sec)

猜對了木有？

PS:
如何判斷字段中是否有多字節字符（比如中文）?

CHARACTER_LENGTH(value) = length(value) 則不包含多字節字符。

length為字節Bytes長度， CHARACTER_LENGTH為字符characters長度。

PS2:
mongodb find()

之後美化/格式化輸出的函數是：
db.tbl.find().pretty()