最近在记录一些简单的结构化日志信息时，用到了sqlite3数据库（保存的信息比较简单，用

1. Mysql

复制代码

，

1. SQL Server

复制代码

，

1. Postgres

复制代码

这些数据库有点小题大做）。
以前开发系统时，用

1. Mysql

复制代码

和

1. Postgres

复制代码

比较多，sqlite3接触不多，这次使用，希望sqlite3也能提供几个基本的功能，比如：

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

  复制代码
  ）
  
• 更新数据时，自动更新更新时间（
  1. 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>

复制代码

这里不需要管

1. test.db

复制代码

文件存不存在，如果不存在，会自动创建的。
创建一张表

1. position_info

复制代码

，这是我用来记录账户净值和利润的表，其中字段的作用不用管，只需要关注

1. id

复制代码

，

1. created_at

复制代码

，

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

复制代码

创建之后，通过

1. sqlite3

复制代码

的命令查看

1. position_info

复制代码

表是否创建。

1. sqlite> .tables
   
2. position_info

复制代码

1. sqlite3

复制代码

的自带命令都是以点号（

1. .

复制代码

）开头的。
表按照默认的方式创建之后， 发现插入一条数据很麻烦，需要指定

1. 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. 
   
6. sqlite> .headers on
   
7. 
   
8. sqlite> SELECT * FROM position_info;
   
9. id|equity|profit_loss|created_at|updated_at
   
10. 1|10.0|2.0|2024-06-09 10:10:10|2024-06-09 10:10:10

复制代码

其实，我希望实现的是插入和更新时，只关注

1. equity

复制代码

和

1. profit_loss

复制代码

两个字段，其他3个字段由数据库自动管理。
类似：

1. 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>

复制代码

先删除创建的

1. position_info

复制代码

，然后重新创建

1. position_info

复制代码

表，创建时指定

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

复制代码

3. 创建时间（created_at）

接下来，设置创建时间（

1. created_at

复制代码

）和更新时间（

1. updated_at

复制代码

）自动插入：

1. 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. 
   
7. sqlite> select * from position_info;
   
8. id|equity|profit_loss|created_at|updated_at
   
9. 1|10.0|2.0|2024-06-09 16:40:52|2024-06-09 16:40:52
   
10. 2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

复制代码

现在，我们只要关注

1. equity

复制代码

和

1. profit_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

复制代码

第一条数据的

1. equity

复制代码

和

1. profit_loss

复制代码

虽然更新成功了，但是它的

1. updated_at

复制代码

没有更新，还是插入时的

1. 2024-06-09 16:40:52

复制代码

。
为了让

1. updated_at

复制代码

也能自动更新，需要加一个监听器，当数据有更新时，更新此数据的

1. 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.    
   
4. sqlite> select * from position_info;
   
5. id|equity|profit_loss|created_at|updated_at
   
6. 1|1000.0|300.0|2024-06-09 16:40:52|2024-06-09 16:49:28
   
7. 2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

复制代码

更新数据时，

1. updated_at

复制代码

也更新了，变成

1. 2024-06-09 16:49:28

复制代码

，与

1. created_at

复制代码

不再一样。

5. 总结

最后，创建一个带有自增ID，自动插入创建时间和更新时间的完整

1. 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. 
   
9. CREATE TRIGGER IF NOT EXISTS trigger_position_info_updated_at AFTER UPDATE ON position_info
   
10. BEGIN
    
11.     UPDATE position_info SET updated_at = DATETIME('now', 'localtime') WHERE rowid == NEW.rowid;
    
12. END;

复制代码

以上就是sqlite3自动插入创建时间和更新时间的功能实现的详细内容，更多关于sqlite3自动创建和更新时间的资料请关注脚本之家其它相关文章！

来源：https://www.jb51.net/database/322451t99.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！