SQLite
背景 ?之前介紹《MQTT，弱網(wǎng)之友 》時(shí)提到過開發(fā)了一個(gè)客戶端時(shí)用到了 MQTT 協(xié)議，同樣的，客戶端也都需要一個(gè)數(shù)據(jù)庫支撐，但是由于 MySQL 比較重，性價(jià)比不高。有一個(gè)更好的選擇，則是天降猛男 SQLite。
介紹 ?SQLite 是一個(gè)輕量級(jí)的數(shù)據(jù)庫管理系統(tǒng)，遵守ACID，非常適合于需要一個(gè)簡(jiǎn)單且獨(dú)立的數(shù)據(jù)庫解決方案的應(yīng)用。
?據(jù)不完全統(tǒng)計(jì)，SQLite 是世界上使用最多的數(shù)據(jù)庫。說到這里大家可能覺得驚訝，但如果按照部署實(shí)例算可能還真是。
?一個(gè)普通的手機(jī)里，除了系統(tǒng)以外，許多第三方應(yīng)用程序，如社交媒體、郵件客戶端、游戲等，都可能內(nèi)嵌 SQLite 數(shù)據(jù)庫來存儲(chǔ)用戶數(shù)據(jù)、緩存、應(yīng)用設(shè)置等，有的應(yīng)用還會(huì)有多個(gè)實(shí)例。
?因此，一個(gè)普通的手機(jī)就可能存在數(shù)十甚至上百個(gè) SQLite 實(shí)例。
移動(dòng)端 APP
優(yōu)勢(shì) ?SQLite 是一個(gè) 用 C 語言編寫的輕量級(jí)數(shù)據(jù)庫管理系統(tǒng)，主打一個(gè) 輕量 ，簡(jiǎn)單列一下特性。
輕量級(jí)：SQLite 不需要單獨(dú)的服務(wù)器進(jìn)程或系統(tǒng)資源，數(shù)據(jù)庫存儲(chǔ)在一個(gè)單一的磁盤文件中，這使得它非常適合于小型應(yīng)用和移動(dòng)設(shè)備 無需配置：SQLite 不需要復(fù)雜的安裝和配置過程，可以很容易地集成到各種應(yīng)用程序中。比如 Java 只需要在 pom.xml 引入配置即可 嵌入式：SQLite 是一個(gè)嵌入式的庫，不作為一個(gè)獨(dú)立的服務(wù)運(yùn)行，而是直接嵌入到應(yīng)用程序中 事務(wù)性：支持 ACID 事務(wù)，確保數(shù)據(jù)的一致性和完整性 零配置：無需運(yùn)行數(shù)據(jù)庫服務(wù)器或進(jìn)行復(fù)雜的配置，SQLite 數(shù)據(jù)庫文件可以被多個(gè)進(jìn)程共享訪問 適合嵌入式系統(tǒng)：由于其輕量級(jí)和簡(jiǎn)單性，SQLite 經(jīng)常用于嵌入式系統(tǒng)和移動(dòng)應(yīng)用 應(yīng)用場(chǎng)景 移動(dòng)應(yīng)用：移動(dòng)應(yīng)用通常需要一個(gè)輕量級(jí)的數(shù)據(jù)庫來存儲(chǔ)本地?cái)?shù)據(jù)，SQLite 因其小體積和無需服務(wù)器的特性非常適合 桌面應(yīng)用：需要本地?cái)?shù)據(jù)庫存儲(chǔ)的桌面應(yīng)用 嵌入式系統(tǒng)：嵌入式系統(tǒng)和物聯(lián)網(wǎng)設(shè)備 獨(dú)立應(yīng)用：不需要網(wǎng)絡(luò)數(shù)據(jù)庫連接的獨(dú)立應(yīng)用 輕量級(jí) Web 應(yīng)用：不需要復(fù)雜事務(wù)處理或高并發(fā)訪問的應(yīng)用 邊緣計(jì)算：邊緣設(shè)備上運(yùn)行 SQLite，僅將重要更改同步到云端，可以有效降低網(wǎng)絡(luò)帶寬的使用，并提高數(shù)據(jù)處理的實(shí)時(shí)性 不適合場(chǎng)景 高并發(fā)應(yīng)用：SQLite 不適合需要高并發(fā)寫入操作的應(yīng)用場(chǎng)景 分布式系統(tǒng)：SQLite 是一個(gè)文件基的數(shù)據(jù)庫，不適合分布式系統(tǒng)或需要跨多個(gè)服務(wù)器共享數(shù)據(jù)庫的場(chǎng)景 復(fù)雜的事務(wù)處理：SQLite 處理復(fù)雜的事務(wù)業(yè)務(wù)相對(duì)其他數(shù)據(jù)庫較弱 需要數(shù)據(jù)庫高級(jí)功能：如存儲(chǔ)過程等，不支持 多用戶實(shí)時(shí)協(xié)作：類似并發(fā)操作 語法 ?SQLite 遵循 SQL 標(biāo)準(zhǔn)的大部分基本語法和功能，如數(shù)據(jù)定義語言（DDL）、數(shù)據(jù)操縱語言（DML）、數(shù)據(jù)查詢語言（DQL）等，所以大多數(shù)場(chǎng)景下使用跟其他數(shù)據(jù)庫語法基本一致。
簡(jiǎn)單函數(shù)調(diào)用
SQL 示例 -- 表創(chuàng)建 CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL , age INTEGER ); -- 列增加 ALTER TABLE table_name ADD COLUMN column_name column_data_type; -- 表刪除 DROP TABLE IF EXISTS table_name; -- 增刪改查 INSERT INTO users ( name , age) VALUES ( 'wushihong' , 30 ); DELETE FROM users WHERE name = 'wushihong' ; UPDATE users SET age = 31 WHERE name = 'wushihong' ; SELECT name , age FROM users WHERE age > 25 ORDER BY age ASC LIMIT 10 ; -- 索引創(chuàng)建 CREATE INDEX index_name ON table_name (column_name); -- 視圖創(chuàng)建 CREATE VIEW v_users_over_25 AS SELECT name , age FROM users WHERE age > 25 ; -- 觸發(fā)器創(chuàng)建 CREATE TRIGGER trg_users_after_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO users_audit (user_id, operation) VALUES (NEW.id, 'INSERT' ); END ; 程序示例（Java） 安裝 < dependency > < groupId > org.xerial </ groupId > < artifactId > sqlite-jdbc </ artifactId > < version > 3.36.0.3 </ version > </ dependency > 創(chuàng)建數(shù)據(jù)庫鏈接 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class SQLiteDemo { public static void main (String[] args) { String url = "jdbc:sqlite:SQLiteDatabase.db" ; // SQLiteDatabase.db 是數(shù)據(jù)庫文件，路徑可配置 try (Connection connection = DriverManager.getConnection(url)) { // 連接成功，可以執(zhí)行數(shù)據(jù)庫操作 } catch (SQLException e) { e.printStackTrace(); } } } 上面執(zhí)行完后，就會(huì)在配置路徑下生成一個(gè) SQLiteDatabase.db 文件
如果有安裝 SQLite3 的話，也可以通過命令進(jìn)入指定數(shù)據(jù)庫
sqlite3 SQLiteDatabase.db 之后就可以對(duì)數(shù)據(jù)庫進(jìn)行操作
sqlite3 操作 db
創(chuàng)建表 創(chuàng)建一個(gè)名為 users 的表，該表包含 id、name 和 age 三個(gè)字段
String sql = "CREATE TABLE IF NOT EXISTS users (" + "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "name TEXT NOT NULL, " + "age INTEGER NOT NULL)" ; try (Statement statement = connection.createStatement()) { statement.execute(sql); } catch (SQLException e) { e.printStackTrace(); } 增刪改查 // 新增用戶 private static void insertUser (Connection conn, String name, int age) throws SQLException { String sql = "INSERT INTO users(name, age) VALUES(?, ?)" ; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString( 1 , name); pstmt.setInt( 2 , age); pstmt.executeUpdate(); } } // 查詢用戶 private static Map<String, Object> getUserById (Connection conn, int id) throws SQLException { String sql = "SELECT id, name, age FROM users WHERE id = ?" ; try (PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt( 1 , userId); ResultSet rs = pstmt.executeQuery()) { Map<String, Object> resp = new HashMap<>(); while (rs.next()) { resp.put( "id" , rs.getInt( "id" ); resp.put( "name" , rs.getString( "name" )); resp.put( "age" , rs.getInt( "age" )); } return resp; } } // 更新用戶 private static void updateUser (Connection conn, String name, int age) throws SQLException { String sql = "UPDATE users SET age = ? WHERE name = ?" ; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setInt( 1 , age); pstmt.setString( 2 , name); pstmt.executeUpdate(); } } // 刪除用戶 private static void deleteUser (Connection conn, String name) throws SQLException { String sql = "DELETE FROM users WHERE name = ?" ; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString( 1 , name); pstmt.executeUpdate(); } } 另外說明 ?一般項(xiàng)目使用 SQLite 時(shí)，也會(huì)整合 SpringBoot、DAO 框架、數(shù)據(jù)庫連接池組件等配套，很少會(huì)單獨(dú)使用。
?上面只是為了舉例，篇幅有限不一一列舉。
使用過程中遇到的問題 時(shí)間類型處理問題 SQLite 沒有像 MySQL 一樣的 DATETIME 數(shù)據(jù)類型，容易出現(xiàn)時(shí)間格式不一致或解析錯(cuò)誤的問題
解決：在存儲(chǔ)和查詢時(shí)間數(shù)據(jù)時(shí)，盡量使用統(tǒng)一的時(shí)間格式，如 ISO 8601 標(biāo)準(zhǔn)格式（YYYY-MM-DD HH:MM:SS）
查詢速度較慢 盡量?jī)?yōu)化數(shù)據(jù)量，定期清理不必要的數(shù)據(jù)，優(yōu)化數(shù)據(jù)表結(jié)構(gòu)，避免 db 文件過大 優(yōu)化查詢語句；為經(jīng)常查詢的列創(chuàng)建索引，以提高查詢效率 SQLite 在并發(fā)訪問方面的性能相對(duì)較弱，容易出現(xiàn)數(shù)據(jù)庫鎖問題，所以盡量減少并發(fā)訪問，避免長(zhǎng)時(shí)間的事務(wù)操作 DB 文件損壞 某些極端情況下可能會(huì)導(dǎo)致 DB 文件損壞，比如斷電、文件系統(tǒng)異常、硬件問題等
解決：定期備份、備份恢復(fù)
最后 ?SQLite 作為一個(gè)輕量級(jí)數(shù)據(jù)庫，對(duì)于輕量級(jí)應(yīng)用、移動(dòng)應(yīng)用、小型項(xiàng)目的快速開發(fā)等場(chǎng)景，SQLite 無疑是最佳選擇。但是面對(duì)高并發(fā)、大規(guī)模數(shù)據(jù)、復(fù)雜事務(wù)等場(chǎng)景時(shí)，SQLite 則不那么適合，畢竟術(shù)業(yè)有專攻。
?還是那句話，選擇組件時(shí)需要結(jié)合整個(gè)項(xiàng)目和公司環(huán)境考慮，沒有一套方案能夠適合所有場(chǎng)景。
參考
SQLite: https://www.sqlite.org/docs.html
閱讀原文：原文鏈接
400 186 1886
