如何为SQLite3设置创建时间的默认值并自动更新修改时间?

发表时间: 2022-04-22 20:45

当年学习 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.mtimeBEGIN        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';】

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