快捷導(dǎo)航

sqlite3自動插入創(chuàng)建時間和更新時間的功能實現(xiàn)

更新時間：2024年06月11日 09:01:31 作者：databook

最近在記錄一些簡單的結(jié)構(gòu)化日志信息時,用到了sqlite3數(shù)據(jù)庫（保存的信息比較簡單,用Mysql,SQL Server,Postgres這些數(shù)據(jù)庫有點(diǎn)小題大做）,這次使用,希望sqlite3也能提供幾個基本的功能,自動插入創(chuàng)建時間和更新時間,需要的朋友可以參考下

1. 準(zhǔn)備

首先創(chuàng)建一個數(shù)據(jù)庫，sqlite3數(shù)據(jù)庫其實就是一個文件。

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

這里不需要管 test.db 文件存不存在，如果不存在，會自動創(chuàng)建的。

創(chuàng)建一張表 position_info，這是我用來記錄賬戶凈值和利潤的表，其中字段的作用不用管，只需要關(guān)注 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...> );

創(chuàng)建之后，通過sqlite3的命令查看position_info表是否創(chuàng)建。

sqlite> .tables
position_info

sqlite3的自帶命令都是以點(diǎn)號（.）開頭的。

表按照默認(rèn)的方式創(chuàng)建之后，發(fā)現(xiàn)插入一條數(shù)據(jù)很麻煩，需要指定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

其實，我希望實現(xiàn)的是插入和更新時，只關(guān)注equity和profit_loss兩個字段，其他3個字段由數(shù)據(jù)庫自動管理。

類似：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>

先刪除創(chuàng)建的 position_info，然后重新創(chuàng)建position_info表，創(chuàng)建時指定id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT。

創(chuàng)建完成后，插入兩條數(shù)據(jù)，插入時不指定ID字段，發(fā)現(xiàn)數(shù)據(jù)庫會幫我們自動插入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. 創(chuàng)建時間（created_at）

接下來，設(shè)置創(chuàng)建時間（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...> );

然后插入兩條測試數(shù)據(jù)：

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

現(xiàn)在，我們只要關(guān)注equity和profit_loss就可以了。

4. 更新時間（updated_at）

經(jīng)過上面的改造之后，插入數(shù)據(jù)沒有問題了，但是更新數(shù)據(jù)時還有一個瑕疵。
更新數(shù)據(jù)時，updated_at字段沒有變化，一直是插入數(shù)據(jù)時的那個時間。

更新前：

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

更新第一條數(shù)據(jù)：

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

第一條數(shù)據(jù)的equity和profit_loss雖然更新成功了，但是它的updated_at沒有更新，還是插入時的2024-06-09 16:40:52。

為了讓updated_at也能自動更新，需要加一個監(jiān)聽器，當(dāng)數(shù)據(jù)有更新時，更新此數(shù)據(jù)的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;

再更新一次數(shù)據(jù)看看：

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

更新數(shù)據(jù)時，updated_at也更新了，變成2024-06-09 16:49:28，與created_at不再一樣。

5. 總結(jié)

最后，創(chuàng)建一個帶有自增ID，自動插入創(chuàng)建時間和更新時間的完整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自動插入創(chuàng)建時間和更新時間的功能實現(xiàn)的詳細(xì)內(nèi)容，更多關(guān)于sqlite3自動創(chuàng)建和更新時間的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: