经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » SQLite » 查看文章
sqlite3自动插入创建时间和更新时间
来源:cnblogs  作者:wang_yb  时间:2024/6/11 9:56:45  对本文有异议

最近在记录一些简单的结构化日志信息时,用到了sqlite3数据库(保存的信息比较简单,用MysqlSQL ServerPostgres这些数据库有点小题大做)。

以前开发系统时,用MysqlPostgres比较多,sqlite3接触不多,
这次使用,希望sqlite3也能提供几个基本的功能,比如:

  1. 主键ID自增
  2. 插入数据时,自动更新创建时间created_at
  3. 更新数据时,自动更新更新时间updated_at

调查这几个功能的过程记录如下。

1. 准备

首先创建一个数据库,sqlite3数据库其实就是一个文件。

  1. $ sqlite3.exe test.db
  2. SQLite version 3.41.2 2023-03-22 11:56:21
  3. Enter ".help" for usage hints.
  4. sqlite>

这里不需要管 test.db 文件存不存在,如果不存在,会自动创建的。

创建一张表 position_info,这是我用来记录账户净值和利润的表,其中字段的作用不用管,
只需要关注 idcreated_atupdated_at三个字段即可。

  1. sqlite> CREATE TABLE IF NOT EXISTS position_info (
  2. (x1...> id INTEGER NOT NULL PRIMARY KEY,
  3. (x1...> equity REAL NOT NULL,
  4. (x1...> profit_loss REAL NOT NULL,
  5. (x1...> created_at TEXT NOT NULL,
  6. (x1...> updated_at TEXT NOT NULL
  7. (x1...> );

创建之后,通过sqlite3的命令查看position_info表是否创建。

  1. sqlite> .tables
  2. position_info

sqlite3的自带命令都是以点号.)开头的。

表按照默认的方式创建之后, 发现插入一条数据很麻烦,
需要指定position_info表中所有5个字段才能插入成功。

  1. sqlite> INSERT INTO position_info(id, equity,
  2. (x1...> profit_loss, created_at, updated_at)
  3. ...> VALUES(1, 10, 2,
  4. (x1...> "2024-06-09 10:10:10", "2024-06-09 10:10:10");
  5. sqlite> .headers on
  6. sqlite> SELECT * FROM position_info;
  7. id|equity|profit_loss|created_at|updated_at
  8. 1|10.0|2.0|2024-06-09 10:10:10|2024-06-09 10:10:10

其实,我希望实现的是插入和更新时,只关注equityprofit_loss两个字段,其他3个字段由数据库自动管理。
类似:INSERT INTO position_info(equity, profit_loss) VALUES(10, 2);

下面开始改造。

2. 主键ID自增

首先,让主键ID能够自动增长。

  1. sqlite> drop table position_info;
  2. sqlite> CREATE TABLE IF NOT EXISTS position_info (
  3. (x1...> id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  4. (x1...> equity REAL NOT NULL,
  5. (x1...> profit_loss REAL NOT NULL,
  6. (x1...> created_at TEXT NOT NULL,
  7. (x1...> updated_at TEXT NOT NULL
  8. (x1...> );
  9. sqlite> select * from position_info;
  10. sqlite>

先删除创建的 position_info,然后重新创建position_info表,
创建时指定id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

创建完成后,插入两条数据,插入时不指定ID字段,发现数据库会帮我们自动插入ID。

  1. sqlite> INSERT INTO position_info(equity,
  2. (x1...> profit_loss, created_at, updated_at)
  3. ...> VALUES(10, 2,
  4. (x1...> "2024-06-09 10:10:10", "2024-06-09 10:10:10");
  5. sqlite> INSERT INTO position_info(equity,
  6. (x1...> profit_loss, created_at, updated_at)
  7. ...> VALUES(100, 20,
  8. (x1...> "2024-06-09 11:11:11", "2024-06-09 11:11:11");
  9. sqlite> select * from position_info;
  10. id|equity|profit_loss|created_at|updated_at
  11. 1|10.0|2.0|2024-06-09 10:10:10|2024-06-09 10:10:10
  12. 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'))

  1. sqlite> drop table position_info;
  2. sqlite> CREATE TABLE IF NOT EXISTS position_info (
  3. (x1...> id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  4. (x1...> equity REAL NOT NULL,
  5. (x1...> profit_loss REAL NOT NULL,
  6. (x1...> created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  7. (x1...> updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
  8. (x1...> );

然后插入两条测试数据:

  1. sqlite> INSERT INTO position_info(equity, profit_loss)
  2. ...> VALUES(10, 2);
  3. sqlite>
  4. sqlite> INSERT INTO position_info(equity, profit_loss)
  5. ...> VALUES(100, 20);
  6. sqlite> select * from position_info;
  7. id|equity|profit_loss|created_at|updated_at
  8. 1|10.0|2.0|2024-06-09 16:40:52|2024-06-09 16:40:52
  9. 2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

现在,我们只要关注equityprofit_loss就可以了。

4. 更新时间(updated_at)

经过上面的改造之后,插入数据没有问题了,但是更新数据时还有一个瑕疵。
更新数据时,updated_at字段没有变化,一直是插入数据时的那个时间。

更新前:

  1. sqlite> select * from position_info;
  2. id|equity|profit_loss|created_at|updated_at
  3. 1|10.0|2.0|2024-06-09 16:40:52|2024-06-09 16:40:52
  4. 2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

更新第一条数据:

  1. sqlite> UPDATE position_info SET equity=500, profit_loss=100
  2. ...> WHERE id = 1;
  3. sqlite> select * from position_info;
  4. id|equity|profit_loss|created_at|updated_at
  5. 1|500.0|100.0|2024-06-09 16:40:52|2024-06-09 16:40:52
  6. 2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

第一条数据的equityprofit_loss虽然更新成功了,但是它的updated_at没有更新,还是插入时的2024-06-09 16:40:52

为了让updated_at也能自动更新,需要加一个监听器,当数据有更新时,更新此数据的updated_at字段。

  1. sqlite> CREATE TRIGGER IF NOT EXISTS trigger_position_info_updated_at AFTER UPDATE ON position_info
  2. ...> BEGIN
  3. ...> UPDATE position_info SET updated_at = DATETIME('now', 'localtime') WHERE rowid == NEW.rowid;
  4. ...> END;

再更新一次数据看看:

  1. sqlite> UPDATE position_info SET equity=1000, profit_loss=300
  2. ...> WHERE id = 1;
  3. sqlite> select * from position_info;
  4. id|equity|profit_loss|created_at|updated_at
  5. 1|1000.0|300.0|2024-06-09 16:40:52|2024-06-09 16:49:28
  6. 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如下:

  1. CREATE TABLE IF NOT EXISTS position_info (
  2. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  3. equity REAL NOT NULL,
  4. profit_loss REAL NOT NULL,
  5. created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  6. updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
  7. );
  8. CREATE TRIGGER IF NOT EXISTS trigger_position_info_updated_at AFTER UPDATE ON position_info
  9. BEGIN
  10. UPDATE position_info SET updated_at = DATETIME('now', 'localtime') WHERE rowid == NEW.rowid;
  11. END;

原文链接:https://www.cnblogs.com/wang_yb/p/18239784

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号