创建时间（并利用触发器自动更新修改时间）

当年学习 MySQL 的时候，就发现数据库里的日期特别麻烦。最近学习 SQLite3，发现坑更多了……

设置默认的创建时间

几乎每张数据表都有「创建时间」这一栏，一般默认为插入数据的时间，自动生成。对于 MySQL，只需要【DEFAULT CURRENT_TIMESTAMP()】就可以了，但在 SQLite 里，就没这么简单。

首先，在 SQLite 的世界里，【CURRENT_TIMESTAMP()】返回的是 UTC 的时间戳，所以如果想要记录本地时间，你得先用 DATETIME 函数，用【DATETIME(CURRENT_TIMESTAMP, 'localtime')】转一道。

然后，你如果想当然地写【DEFAULT DATETIME(CURRENT_TIMESTAMP, 'localtime')】，就会直接报错！翻查了半天才发现，在 SQLite 的世界里，所有函数都必须用小括号圈起来！

所以说，想要设置默认的时间戳、且是本地时间，就得老老实实写成【DEFAULT (DATETIME(CURRENT_TIMESTAMP, 'localtime'))】。在 Navicat 里的「默认」栏里，也得把括号打上。就问你惊不惊喜、意不意外？

在SQLite里设置默认时间戳

在Navicat中设置默认时间戳

设置自动更新的修改时间

在 MySQL 里，对于类型为时间戳的数据栏，只要简单地设置【ON UPDATE CURRENT_TIMESTAMP】，每次数据行有更改时，这个时间戳就会自动更新为当前时间，简直不要太方便。但在 SQLite 的世界里，事情就没那么简单了。

简而言之，SQLite 没有这种捷径，想要实现，只能创建触发器（TRIGGER）——顿时觉得这个世界好不真实……

先贴一张官方的创建触发器的语法图，你们感受一下：

创建触发器（CREATE TRIGGER）的语法

眼尖的你可能会发现，语法中有个【FOR EACH ROW】，似乎在【AFTER UPDATE OF】后面就不用把所有数据栏的名称逐一写上了？太天真了！【FOR EACH ROW】也包括这个需要自动更新的「修改时间」，这样写的话，每次更新完「修改时间」，这个触发器就会立即发作，从而陷入万劫不复的死循环之中。

所以，需要追加一个限定条件【WHEN NEW.mtime = OLD.mtime】，意思是说，仅当 mtime 没变化的时候，触发器才有效。如果是 mtime 发生变化，则不会引发触发器的动作。此处用 NEW 和 OLD 区别触发器生效前后的对比。

UPDATE 语句同时需要添加【WHERE aid = OLD.aid】，表示 UPDATE 动作所作用的数据行，是触发器生效前数据发生变化的那一行。

CREATE TRIGGER a_mtime AFTER UPDATE ON a FOR EACH ROW WHEN NEW.mtime = OLD.mtime BEGIN UPDATE a SET mtime = (DATETIME(CURRENT_TIMESTAMP, 'localtime')) WHERE aid = OLD.aid; END

查看数据库里已创建的触发器

触发器保存在一个名为 sqlite_master 的数据表里，且 type = 'trigger'，代码则保存在 sql 数据栏里。如下图所示：

sqlite_master 表的数据结构

因此，查看整个数据库中共有多少个触发器，只需【SELECT name FROM sqlite_master WHERE type = 'trigger';】。而查看某个触发器的代码，只需【SELECT sql FROM sqlite_master WHERE name = 'trigger_name';】

感觉触发器这玩意儿还挺难，得多练习一下，巩固一下知识点才行。