最近在记录一些简单的结构化日志信息时，用到了sqlite3数据库（保存的信息比较简单，用Mysql，SQL Server，Postgres这些数据库有点小题大做）。
以前开发系统时，用Mysql和Postgres比较多，sqlite3接触不多，
这次使用，希望sqlite3也能提供几个基本的功能，比如：
- 主键ID自增
- 插入数据时，自动更新创建时间（
created_at） - 更新数据时，自动更新更新时间（
updated_at）
调查这几个功能的过程记录如下。
1. 准备
首先创建一个数据库，sqlite3数据库其实就是一个文件。
powershell
复制代码
$ sqlite3.exe test.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite>
这里不需要管 test.db 文件存不存在，如果不存在，会自动创建的。
创建一张表 position_info，这是我用来记录账户净值和利润的表，其中字段的作用不用管，
只需要关注 id，created_at，updated_at三个字段即可。
powershell
复制代码
sqlite> CREATE TABLE IF NOT EXISTS position_info (
(x1...> id INTEGER NOT NULL PRIMARY KEY,
(x1...> equity REAL NOT NULL,
(x1...> profit_loss REAL NOT NULL,
(x1...> created_at TEXT NOT NULL,
(x1...> updated_at TEXT NOT NULL
(x1...> );
创建之后，通过sqlite3的命令查看position_info表是否创建。
powershell
复制代码
sqlite> .tables
position_info
sqlite3的自带命令都是以点号（.）开头的。
表按照默认的方式创建之后， 发现插入一条数据很麻烦，
需要指定position_info表中所有5个字段才能插入成功。
powershell
复制代码
sqlite> INSERT INTO position_info(id, equity,
(x1...> profit_loss, created_at, updated_at)
...> VALUES(1, 10, 2,
(x1...> "2024-06-09 10:10:10", "2024-06-09 10:10:10");
sqlite> .headers on
sqlite> SELECT * FROM position_info;
id|equity|profit_loss|created_at|updated_at
1|10.0|2.0|2024-06-09 10:10:10|2024-06-09 10:10:10
其实，我希望实现的是插入和更新时，只关注equity和profit_loss两个字段，其他3个字段由数据库自动管理。
类似：INSERT INTO position_info(equity, profit_loss) VALUES(10, 2);
下面开始改造。
2. 主键ID自增
首先，让主键ID能够自动增长。
powershell
复制代码
sqlite> drop table position_info;
sqlite> CREATE TABLE IF NOT EXISTS position_info (
(x1...> id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
(x1...> equity REAL NOT NULL,
(x1...> profit_loss REAL NOT NULL,
(x1...> created_at TEXT NOT NULL,
(x1...> updated_at TEXT NOT NULL
(x1...> );
sqlite> select * from position_info;
sqlite>
先删除创建的 position_info，然后重新创建position_info表，
创建时指定id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT。
创建完成后，插入两条数据，插入时不指定ID字段，发现数据库会帮我们自动插入ID。
powershell
复制代码
sqlite> INSERT INTO position_info(equity,
(x1...> profit_loss, created_at, updated_at)
...> VALUES(10, 2,
(x1...> "2024-06-09 10:10:10", "2024-06-09 10:10:10");
sqlite> INSERT INTO position_info(equity,
(x1...> profit_loss, created_at, updated_at)
...> VALUES(100, 20,
(x1...> "2024-06-09 11:11:11", "2024-06-09 11:11:11");
sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|10.0|2.0|2024-06-09 10:10:10|2024-06-09 10:10:10
2|100.0|20.0|2024-06-09 11:11:11|2024-06-09 11:11:11
3. 创建时间（created_at）
接下来，设置创建时间（created_at）和更新时间（updated_at）自动插入：DEFAULT (DATETIME('now', 'localtime'))
powershell
复制代码
sqlite> drop table position_info;
sqlite> CREATE TABLE IF NOT EXISTS position_info (
(x1...> id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
(x1...> equity REAL NOT NULL,
(x1...> profit_loss REAL NOT NULL,
(x1...> created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
(x1...> updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
(x1...> );
然后插入两条测试数据：
powershell
复制代码
sqlite> INSERT INTO position_info(equity, profit_loss)
...> VALUES(10, 2);
sqlite>
sqlite> INSERT INTO position_info(equity, profit_loss)
...> VALUES(100, 20);
sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|10.0|2.0|2024-06-09 16:40:52|2024-06-09 16:40:52
2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53
现在，我们只要关注equity和profit_loss就可以了。
4. 更新时间（updated_at）
经过上面的改造之后，插入数据没有问题了，但是更新数据时还有一个瑕疵。
更新数据时，updated_at字段没有变化，一直是插入数据时的那个时间。
更新前：
powershell
复制代码
sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|10.0|2.0|2024-06-09 16:40:52|2024-06-09 16:40:52
2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53
更新第一条数据：
powershell
复制代码
sqlite> UPDATE position_info SET equity=500, profit_loss=100
...> WHERE id = 1;
sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|500.0|100.0|2024-06-09 16:40:52|2024-06-09 16:40:52
2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53
第一条数据的equity和profit_loss虽然更新成功了，但是它的updated_at没有更新，还是插入时的2024-06-09 16:40:52。
为了让updated_at也能自动更新，需要加一个监听器，当数据有更新时，更新此数据的updated_at字段。
powershell
复制代码
sqlite> CREATE TRIGGER IF NOT EXISTS trigger_position_info_updated_at AFTER UPDATE ON position_info
...> BEGIN
...> UPDATE position_info SET updated_at = DATETIME('now', 'localtime') WHERE rowid == NEW.rowid;
...> END;
再更新一次数据看看：
powershell
复制代码
sqlite> UPDATE position_info SET equity=1000, profit_loss=300
...> WHERE id = 1;
sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|1000.0|300.0|2024-06-09 16:40:52|2024-06-09 16:49:28
2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53
更新数据时，updated_at也更新了，变成2024-06-09 16:49:28，与created_at不再一样。
5. 总结
最后，创建一个带有自增ID，自动插入创建时间和更新时间的完整SQL如下：
powershell
复制代码
CREATE TABLE IF NOT EXISTS position_info (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
equity REAL NOT NULL,
profit_loss REAL NOT NULL,
created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);
CREATE TRIGGER IF NOT EXISTS trigger_position_info_updated_at AFTER UPDATE ON position_info
BEGIN
UPDATE position_info SET updated_at = DATETIME('now', 'localtime') WHERE rowid == NEW.rowid;
END;
