背景

 之前介绍《MQTT，弱网之友》时提到过开发了一个客户端时用到了 MQTT 协议，同样的，客户端也都需要一个数据库支撑，但是由于 MySQL 比较重，性价比不高。有一个更好的选择，则是天降猛男 SQLite。

介绍

 SQLite 是一个轻量级的数据库管理系统，遵守ACID，非常适合于需要一个简单且独立的数据库解决方案的应用。

 据不完全统计，SQLite 是世界上使用最多的数据库。说到这里大家可能觉得惊讶，但如果按照部署实例算可能还真是。

 一个普通的手机里，除了系统以外，许多第三方应用程序，如社交媒体、邮件客户端、游戏等，都可能内嵌 SQLite 数据库来存储用户数据、缓存、应用设置等，有的应用还会有多个实例。

 因此，一个普通的手机就可能存在数十甚至上百个 SQLite 实例。

优势

 SQLite 是一个 用 C 语言编写的轻量级数据库管理系统，主打一个轻量，简单列一下特性。

• 轻量级：SQLite 不需要单独的服务器进程或系统资源，数据库存储在一个单一的磁盘文件中，这使得它非常适合于小型应用和移动设备
• 无需配置：SQLite 不需要复杂的安装和配置过程，可以很容易地集成到各种应用程序中。比如 Java 只需要在 pom.xml 引入配置即可
• 嵌入式：SQLite 是一个嵌入式的库，不作为一个独立的服务运行，而是直接嵌入到应用程序中
• 事务性：支持 ACID 事务，确保数据的一致性和完整性
• 零配置：无需运行数据库服务器或进行复杂的配置，SQLite 数据库文件可以被多个进程共享访问
• 适合嵌入式系统：由于其轻量级和简单性，SQLite 经常用于嵌入式系统和移动应用

应用场景

• 移动应用：移动应用通常需要一个轻量级的数据库来存储本地数据，SQLite 因其小体积和无需服务器的特性非常适合
• 桌面应用：需要本地数据库存储的桌面应用
• 嵌入式系统：嵌入式系统和物联网设备
• 独立应用：不需要网络数据库连接的独立应用
• 轻量级 Web 应用：不需要复杂事务处理或高并发访问的应用
• 边缘计算：边缘设备上运行 SQLite，仅将重要更改同步到云端，可以有效降低网络带宽的使用，并提高数据处理的实时性

不适合场景

• 高并发应用：SQLite 不适合需要高并发写入操作的应用场景
• 分布式系统：SQLite 是一个文件基的数据库，不适合分布式系统或需要跨多个服务器共享数据库的场景
• 复杂的事务处理：SQLite 处理复杂的事务业务相对其他数据库较弱
• 需要数据库高级功能：如存储过程等，不支持
• 多用户实时协作：类似并发操作

语法

 SQLite 遵循 SQL 标准的大部分基本语法和功能，如数据定义语言（DDL）、数据操纵语言（DML）、数据查询语言（DQL）等，所以大多数场景下使用跟其他数据库语法基本一致。

SQL 示例

-- 表创建
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;
-- 索引创建
CREATE INDEX index_name ON table_name (column_name);
-- 视图创建
CREATE VIEW v_users_over_25 AS
SELECT name, age FROM users WHERE age > 25;
-- 触发器创建
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>

创建数据库链接

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 是数据库文件，路径可配置
        try (Connection connection = DriverManager.getConnection(url)) {
            // 连接成功，可以执行数据库操作
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

上面执行完后，就会在配置路径下生成一个 SQLiteDatabase.db 文件

如果有安装 SQLite3 的话，也可以通过命令进入指定数据库

sqlite3 SQLiteDatabase.db

之后就可以对数据库进行操作

创建表

创建一个名为 users 的表，该表包含 id、name 和 age 三个字段

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();
    }
}

另外说明

 一般项目使用 SQLite 时，也会整合 SpringBoot、DAO 框架、数据库连接池组件等配套，很少会单独使用。

 上面只是为了举例，篇幅有限不一一列举。

使用过程中遇到的问题

时间类型处理问题

SQLite 没有像 MySQL 一样的 DATETIME 数据类型，容易出现时间格式不一致或解析错误的问题

解决：在存储和查询时间数据时，尽量使用统一的时间格式，如 ISO 8601 标准格式（YYYY-MM-DD HH:MM:SS）

查询速度较慢

1. 尽量优化数据量，定期清理不必要的数据，优化数据表结构，避免 db 文件过大
2. 优化查询语句；为经常查询的列创建索引，以提高查询效率
3. SQLite 在并发访问方面的性能相对较弱，容易出现数据库锁问题，所以尽量减少并发访问，避免长时间的事务操作

DB 文件损坏

某些极端情况下可能会导致 DB 文件损坏，比如断电、文件系统异常、硬件问题等

解决：定期备份、备份恢复

最后

 SQLite 作为一个轻量级数据库，对于轻量级应用、移动应用、小型项目的快速开发等场景，SQLite 无疑是最佳选择。但是面对高并发、大规模数据、复杂事务等场景时，SQLite 则不那么适合，毕竟术业有专攻。

 还是那句话，选择组件时需要结合整个项目和公司环境考虑，没有一套方案能够适合所有场景。

参考

SQLite: https://www.sqlite.org/docs.html

阅读原文：原文链接