本站首页 收藏本站
广告位联系

APP正在开发中...
返回顶部
您的位置： 主页 > 数据库 > SQLite >

收藏
分享到

sqlite3自动插入创建时间和更新时间的功能实现

SQLite 来源：互联网 作者：佚名 发布时间：2024-11-20 20:43:18 人浏览
摘要

最近在记录一些简单的结构化日志信息时，用到了sqlite3数据库（保存的信息比较简单，用Mysql，SQL Server，Postgres这些数据库有点小题大做）。 以前开发系统时，用Mysql和Postgres比较多，sqlite3接

最近在记录一些简单的结构化日志信息时，用到了sqlite3数据库（保存的信息比较简单，用Mysql，SQL Server，Postgres这些数据库有点小题大做）。

以前开发系统时，用Mysql和Postgres比较多，sqlite3接触不多，这次使用，希望sqlite3也能提供几个基本的功能，比如：

  • 主键ID自增
  • 插入数据时，自动更新创建时间（created_at）
  • 更新数据时，自动更新更新时间（updated_at）

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

1. 准备

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

1

2

3

4

$  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三个字段即可。

1

2

3

4

5

6

7

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表是否创建。

1

2

sqlite> .tables

position_info

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

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

1

2

3

4

5

6

7

8

9

10

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能够自动增长。

1

2

3

4

5

6

7

8

9

10

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。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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'))

1

2

3

4

5

6

7

8

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...> );

然后插入两条测试数据：

1

2

3

4

5

6

7

8

9

10

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字段没有变化，一直是插入数据时的那个时间。

更新前：

1

2

3

4

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

更新第一条数据：

1

2

3

4

5

6

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字段。

1

2

3

4

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;

再更新一次数据看看：

1

2

3

4

5

6

7

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如下：

1

2

3

4

5

6

7

8

9

10

11

12

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;

版权声明 : 本文内容来源于互联网或用户自行发布贡献，该文观点仅代表原作者本人。本站仅提供信息存储空间服务和不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权， 违法违规的内容， 请发送邮件至2530232025#qq.cn(#换@)举报，一经查实，本站将立刻删除。

您可能感兴趣的文章 :

原文链接 :
相关文章
推荐阅读
最新更新
友情链接
  • 本站所有内容来源于互联网或用户自行发布，本站仅提供信息存储空间服务，不拥有版权，不承担法律责任。如有侵犯您的权益，请您联系站长处理！
  • Copyright © 2017-2022 F11.CN All Rights Reserved. F11站长开发者网 版权所有 | 苏ICP备2022031554号-1 | 51LA统计