sqlite3自動插入創(chuàng)建時間和更新時間的功能實現
最近在記錄一些簡單的結構化日志信息時，用到了sqlite3數據庫（保存的信息比較簡單，用Mysql，SQL Server，Postgres這些數據庫有點小題大做）。
以前開發(fā)系統(tǒng)時，用Mysql和Postgres比較多，sqlite3接觸不多，這次使用，希望sqlite3也能提供幾個基本的功能，比如：
- 主鍵ID自增
- 插入數據時，自動更新創(chuàng)建時間（
created_at） - 更新數據時，自動更新更新時間（
updated_at）
調查這幾個功能的過程記錄如下。
1. 準備
首先創(chuàng)建一個數據庫，sqlite3數據庫其實就是一個文件。
$ 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，這是我用來記錄賬戶凈值和利潤的表，其中字段的作用不用管，只需要關注 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的自帶命令都是以點號（.）開頭的。
表按照默認的方式創(chuàng)建之后， 發(fā)現插入一條數據很麻煩，需要指定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>
先刪除創(chuàng)建的 position_info，然后重新創(chuàng)建position_info表，創(chuàng)建時指定id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT。
創(chuàng)建完成后，插入兩條數據，插入時不指定ID字段，發(fā)現數據庫會幫我們自動插入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）
接下來，設置創(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...> );
然后插入兩條測試數據：
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也能自動更新，需要加一個監(jiān)聽器，當數據有更新時，更新此數據的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. 總結
最后，創(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)建時間和更新時間的功能實現的詳細內容，更多關于sqlite3自動創(chuàng)建和更新時間的資料請關注腳本之家其它相關文章！
相關文章
這篇文章主要介紹了SQLite教程（五）：數據庫和事務,本文講解了Attach數據庫、Detach數據庫、事務等內容,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了SQLite教程（二）：C/C++接口簡介,本文講解了C/C++接口概述、核心對象和接口、參數綁定等內容,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了SQLite教程（一）：SQLite數據庫介紹,本文講解了什么是SQLite、SQLite的主要優(yōu)點、和RDBMS相比SQLite的一些劣勢、個性化特征等內容,需要的朋友可以參考下2015-05-05
在上一篇隨筆的結尾，我提到了約束， 但是在那里我把它翻譯成了限定符，不太準確，這里先更正一下，應該翻譯成約束更貼切一點。 那么什么是約束呢2013-12-12
這篇文章主要介紹SQLite 內存數據庫的使用方法， 需要的朋友可以參考下2013-12-12
在SQLite中提供了一組用于在線數據庫備份的APIs函數(C接口)，可以很好的解決上述方法存在的不足。通過該組函數，可以將源數據庫中的內容拷貝到另一個數據庫，同時覆蓋目標數據庫中的數據2013-12-12
本文給大家分享的是個人在實際項目中對于sqlite的一次優(yōu)化的記錄，非常簡單實用，希望對大家學習sqlite能夠有所幫助。2016-02-02
這兩天用了用SQLite,感覺比SQL CE好用多了,最主要的是性能好多了.這一點對手持設備來講很重要.閑話少說,下來將怎么在WM用.NET CF訪問SQLite.2009-11-11
這篇文章主要介紹了SQLite中重置自動編號列的方法,本文講解了3種情況和其對應解決方法,需要的朋友可以參考下2015-03-03
System.Data.SQLite是SQLite的加強版，它可以無需.NET Framework支持，由于它內部包含了一個ADO.NET 2.0引擎，所以.NET開發(fā)人員可以利用System.Data.SQLite方便地開發(fā).NET程序。2011-02-02