之前我們介紹了：MySQL數據庫TIMESTAMP設置默認值的靈活運用，本次我們接著上次的內容介紹幾個MySQL數據庫TIMESTAMP設置默認值的幾個應用實例，希望能夠對您有所幫助。
#1查看表定義，可以看到b列有個屬性ON UPDATE CURRENT_TIMESTAMP，導致更新數據時，即便未涉及到該列，該列數據也被自動更新。另一方面，c列默認值是'0000-00-00 00:00:00'，實際插入已經被自動賦值為current_timestamp。
- root@localhost:test >show create table dj1\G
- *************************** 1. row ***************************
- Table: dj1
- Create Table: CREATE TABLE `dj1` (
- `a` char(1) COLLATE utf8_bin DEFAULT NULL,
- `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
- UNIQUE KEY `dj1_idx_u1` (`b`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
- 1 row in set (0.00 sec)
#2創建表dj2，列b不帶自動更新屬性。
- root@localhost:test >CREATE TABLE `dj2` (
- -> `a` char(1) COLLATE utf8_bin DEFAULT NULL,
- -> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
- -> `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
- -> UNIQUE KEY `dj1_idx_u1` (`b`)
- -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
- Query OK, 0 rows affected (0.01 sec)
#3插入dj2測試數據
- root@localhost:test >insert into dj2 values (1,null,null);
- Query OK, 1 row affected (0.00 sec)
- root@localhost:test >insert into dj2 values (2,null,null);
- Query OK, 1 row affected (0.00 sec)
#4查看dj2數據
- root@localhost:test >select * from dj2;
- +------+---------------------+---------------------+
- | a | b | c |
- +------+---------------------+---------------------+
- | 1 | 2009-09-09 14:02:55 | 2009-09-09 14:02:55 |
- | 2 | 2009-09-09 14:03:00 | 2009-09-09 14:03:00 |
- +------+---------------------+---------------------+
- 2 rows in set (0.00 sec)
#5dj2上創建唯一索引
- root@localhost:test >create unique index dj2_idx_u1 on dj2(b);
- Query OK, 2 rows affected (0.02 sec)
- Records: 2 Duplicates: 0 Warnings: 0
- #更新數據成功
- root@localhost:test >update dj2 set a=9;
- Query OK, 2 rows affected (0.00 sec)
- Rows matched: 2 Changed: 2 Warnings: 0
- root@localhost:test >select * from dj2;
- +------+---------------------+---------------------+
- | a | b | c |
- +------+---------------------+---------------------+
- | 9 | 2009-09-09 14:02:55 | 2009-09-09 14:02:55 |
- | 9 | 2009-09-09 14:03:00 | 2009-09-09 14:03:00 |
- +------+---------------------+---------------------+
- 2 rows in set (0.00 sec)
#6創建表dj3，b列默認值為CURRENT_TIMESTAMP，c列默認值為CURRENT_TIMESTAMP帶自動更新屬性，報錯，不允許行為。
- root@localhost:test >CREATE TABLE `dj3` (
- -> `a` char(1) COLLATE utf8_bin DEFAULT NULL,
- -> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
- -> `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- -> UNIQUE KEY `dj1_idx_u1` (`b`)
- -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
- ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
#7創建表dj4，b列默認值為CURRENT_TIMESTAMP，c列默認值為'0000-00-00 00:00:00'帶自動更新屬性，報錯，不允許行為。
- root@localhost:test >CREATE TABLE `dj4` (
- -> `a` char(1) COLLATE utf8_bin DEFAULT NULL,
- -> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
- -> `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
- -> UNIQUE KEY `dj1_idx_u1` (`b`)
- -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
- ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
#8創建表dj5，b列默認值為CURRENT_TIMESTAMP帶自動更新屬性，c列默認值為CURRENT_TIMESTAMP，報錯，不允許行為。
- root@localhost:test >CREATE TABLE `dj5` (
- -> `a` char(1) COLLATE utf8_bin DEFAULT NULL,
- -> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- -> `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
- -> UNIQUE KEY `dj1_idx_u1` (`b`)
- -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
- ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
以上就是MySQL數據庫TIMESTAMP設置默認值的幾個應用實例，本文就介紹到這裡，希望本次的介紹能夠對您有所收獲！
