最近在记录一些简单的结构化日志信息时,用到了sqlite3数据库(保存的信息比较简单,用Mysql
,SQL Server
,Postgres
这些数据库有点小题大做)。
以前开发系统时,用Mysql
和Postgres
比较多,sqlite3接触不多,这次使用,希望sqlite3也能提供几个基本的功能,比如:
- 主键ID自增
- 插入数据时,自动更新创建时间(
created_at
) - 更新数据时,自动更新更新时间(
updated_at
)
调查这几个功能的过程记录如下。
1. 准备
首先创建一个数据库,sqlite3数据库其实就是一个文件。
$ 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
三个字段即可。
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
表是否创建。
sqlite> .tables position_info
sqlite3
的自带命令都是以点号(.
)开头的。
表按照默认的方式创建之后, 发现插入一条数据很麻烦,需要指定position_info
表中所有5个字段才能插入成功。
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能够自动增长。
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。
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'))
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...> );
然后插入两条测试数据:
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字段没有变化,一直是插入数据时的那个时间。
更新前:
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
更新第一条数据:
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
字段。
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;
再更新一次数据看看:
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
如下:
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;
以上就是sqlite3自动插入创建时间和更新时间的功能实现的详细内容,更多关于sqlite3自动创建和更新时间的资料请关注IT俱乐部其它相关文章!