SQLite创建数据库与表的最佳实践

SQLite 简介

SQLite 是一款轻型的数据库，它是遵守 ACID 的关系型数据库管理系统，它包含在一个相对小的 C 库中。与其他数据库管理系统不同，SQLite 不是一个客户端 - 服务器结构的数据库，而是基于文件的数据库。这意味着 SQLite 数据库存储在一个单一的磁盘文件中，非常适合于嵌入式系统以及快速原型开发等场景。SQLite 具有零配置、占用资源少、易于部署等优点，广泛应用于移动应用开发、桌面应用以及一些小型 Web 应用中。

创建 SQLite 数据库

创建数据库文件

在 SQLite 中，创建数据库实际上就是创建一个数据库文件。在大多数编程语言中，通过相应的 SQLite 驱动程序，只需指定要创建的数据库文件名即可。如果该文件不存在，系统会自动创建。例如，在 Python 中使用 sqlite3 模块创建数据库文件的代码如下：

import sqlite3

# 连接到 SQLite 数据库，如果文件不存在则创建
conn = sqlite3.connect('example.db')
conn.close()

上述代码使用 sqlite3.connect() 方法连接到 example.db 数据库文件。如果 example.db 不存在，Python 会自动创建该文件。这里先连接再关闭，因为仅仅是为了展示创建数据库文件的操作。

在 Java 中，可以使用 JDBC 来创建 SQLite 数据库文件。以下是一个简单的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class CreateSQLiteDB {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:example.db";
        try (Connection conn = DriverManager.getConnection(url)) {
            if (conn != null) {
                System.out.println("数据库已创建或已连接。");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

在这个 Java 代码中，通过 DriverManager.getConnection(url) 方法连接到 example.db。如果文件不存在，同样会自动创建。

数据库文件路径的选择

选择合适的数据库文件路径很重要。在桌面应用中，通常将数据库文件放在应用程序的特定数据目录下。例如，在 Windows 系统中，可以放在 %APPDATA%\YourAppName 目录下；在 Linux 系统中，可放在 ~/.config/YourAppName 目录下。这样做有助于将应用程序的数据与系统文件隔离开来，便于管理和维护。

对于移动应用，不同的平台有不同的存储规则。在 Android 中，数据库文件通常存储在应用的私有数据目录中，通过 Context.getDatabasePath() 方法可以获取到数据库文件的路径。例如：

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MyDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "myapp.db";
    private static final int DATABASE_VERSION = 1;

    public MyDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建表的 SQL 语句
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库的逻辑
    }

    public void showDatabasePath() {
        Log.d("DatabasePath", getWritableDatabase().getPath());
    }
}

在上述 Android 代码中，getWritableDatabase().getPath() 可以获取到数据库文件的路径。在 iOS 中，数据库文件一般放在应用的 Documents 目录或者 Library 目录下，使用 NSSearchPathForDirectoriesInDomains 函数来获取这些目录的路径。

创建 SQLite 表

定义表结构

在创建表之前，需要明确表的结构，即确定表中包含哪些列，每列的数据类型是什么，以及是否有主键、外键等约束条件。SQLite 支持多种数据类型，包括 INTEGER（整数）、TEXT（文本）、REAL（浮点数）、BLOB（二进制大对象）等。

例如，要创建一个存储用户信息的表 users，表结构可能如下：

列名	数据类型	说明
id	INTEGER	用户唯一标识，主键
name	TEXT	用户姓名
age	INTEGER	用户年龄
email	TEXT	用户邮箱

创建表的 SQL 语句

基于上述表结构，使用 SQL 语句创建 users 表的代码如下：

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
);

在上述 SQL 语句中：

1. CREATE TABLE 是创建表的关键字。
2. users 是表名。
3. id INTEGER PRIMARY KEY AUTOINCREMENT 定义了 id 列，它是一个整数类型，作为主键并且会自动递增。这意味着每插入一条新记录，id 值会自动加 1。
4. name TEXT NOT NULL 定义了 name 列，数据类型为文本，并且该列不能为空。
5. age INTEGER 定义了 age 列，数据类型为整数。这里没有添加 NOT NULL 约束，所以 age 列可以为空值。
6. email TEXT UNIQUE 定义了 email 列，数据类型为文本，并且该列的值必须唯一，即不能有两条记录的 email 值相同。

主键的选择与设计

主键是表中的一列或多列的组合，其值能够唯一地标识表中的每一行记录。在 SQLite 中，通常建议使用 INTEGER 类型的自增长主键，就像上面 users 表中的 id 列一样。自增长主键的优点是简单、高效，并且能够保证唯一性。

然而，在某些情况下，可能需要使用自然主键，即使用表中具有业务含义的列作为主键。例如，在一个存储产品信息的表中，产品的序列号可能是一个自然主键。但需要注意的是，使用自然主键时，要确保这些值在业务上始终保持唯一且不可更改。否则，在更新数据时可能会带来一些复杂的问题。

如果要使用多个列组合作为主键，可以在创建表时这样定义：

CREATE TABLE orders (
    order_id INTEGER,
    product_id INTEGER,
    PRIMARY KEY (order_id, product_id)
);

在上述 orders 表中，order_id 和 product_id 两列组合作为主键。这意味着只有 order_id 和 product_id 的组合值是唯一的，单独的 order_id 或 product_id 可能会重复。

外键约束的应用

外键用于建立两个表之间的关联关系。例如，有一个 orders 表存储订单信息，一个 products 表存储产品信息，orders 表中的 product_id 列应该关联到 products 表中的 id 列，以确保 orders 表中引用的 product_id 是 products 表中存在的。

首先创建 products 表：

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL
);

然后创建 orders 表，并添加外键约束：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_date TEXT,
    product_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

在 orders 表的创建语句中，FOREIGN KEY (product_id) REFERENCES products(id) 定义了外键约束。这表示 orders 表中的 product_id 列的值必须是 products 表中 id 列已存在的值。如果试图在 orders 表中插入一条 product_id 在 products 表中不存在的记录，SQLite 会抛出一个错误。

外键约束有助于维护数据的一致性和完整性。例如，当删除 products 表中的某个产品记录时，如果 orders 表中存在引用该产品的订单记录，根据外键约束的设置，可以选择级联删除（同时删除 orders 表中相关的订单记录），或者拒绝删除（抛出错误阻止删除操作）。要实现级联删除，可以在创建表时添加 ON DELETE CASCADE 选项：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_date TEXT,
    product_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

这样，当删除 products 表中的某个产品记录时，orders 表中引用该产品的订单记录也会被自动删除。

检查约束（Check Constraint）

检查约束用于限制列中可以输入的值。例如，在 users 表中，可以添加一个检查约束，确保 age 列的值在合理范围内（比如 0 到 120 之间）。修改 users 表的创建语句如下：

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE,
    CHECK (age >= 0 AND age <= 120)
);

在上述语句中，CHECK (age >= 0 AND age <= 120) 就是一个检查约束。如果试图插入一条 age 值不在 0 到 120 之间的记录，SQLite 会拒绝该操作并抛出错误。

检查约束还可以应用于多个列。例如，在一个 employees 表中，有 hire_date 和 termination_date 两列，要确保 termination_date 晚于 hire_date，可以这样定义检查约束：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    hire_date TEXT,
    termination_date TEXT,
    CHECK (termination_date >= hire_date OR termination_date IS NULL)
);

这里使用 CHECK (termination_date >= hire_date OR termination_date IS NULL) 确保要么 termination_date 晚于 hire_date，要么 termination_date 为空（表示员工仍在职）。

默认值的设置

在创建表时，可以为列设置默认值。当插入新记录时，如果没有为该列指定值，就会使用默认值。例如，在 users 表中，可以为 age 列设置默认值为 18：

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER DEFAULT 18,
    email TEXT UNIQUE
);

这样，当插入新用户记录时，如果没有指定 age 的值，age 列将自动设置为 18。

默认值也可以是一个表达式。例如，在一个记录文章发布时间的表 articles 中，可以为 published_date 列设置默认值为当前日期和时间。在 SQLite 中，可以使用 CURRENT_TIMESTAMP 来获取当前时间：

CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    published_date TEXT DEFAULT CURRENT_TIMESTAMP
);

每次插入新文章记录时，如果没有指定 published_date，系统会自动将其设置为当前的日期和时间。

创建临时表

临时表的概念与用途

临时表是一种在 SQLite 会话期间存在的表，会话结束后临时表会自动删除。临时表主要用于存储临时数据，例如在复杂查询中存储中间结果，或者在事务处理过程中临时存储一些数据。

临时表与普通表的区别在于其生命周期和作用范围。普通表存储在数据库文件中，除非显式删除，否则一直存在。而临时表只存在于当前会话中，不同的会话之间无法共享临时表的数据。

创建临时表的 SQL 语句

创建临时表的 SQL 语句与创建普通表类似，只需在 CREATE TABLE 关键字后加上 TEMP 或 TEMPORARY 关键字。例如，创建一个临时表 temp_users，用于临时存储部分用户数据：

CREATE TEMP TABLE temp_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

在上述语句中，CREATE TEMP TABLE 表示创建一个临时表。临时表 temp_users 具有 id 和 name 两列，id 作为主键自动递增，name 列不能为空。

临时表的使用场景

1. 复杂查询的中间结果存储：假设要从一个大型的 customers 表中筛选出满足多个复杂条件的客户，然后对这些客户进行进一步的统计分析。可以先将满足条件的客户数据存储在一个临时表中，这样可以简化后续的查询操作。例如：

-- 创建临时表存储满足条件的客户
CREATE TEMP TABLE temp_customers AS
SELECT * FROM customers
WHERE age > 30 AND city = 'New York';

-- 对临时表中的数据进行统计分析
SELECT COUNT(*) FROM temp_customers;

在上述代码中，首先使用 CREATE TEMP TABLE... AS 语句创建临时表 temp_customers，并将满足 age > 30 且 city = 'New York' 条件的客户数据插入到临时表中。然后可以对临时表中的数据进行各种统计查询。

2. 事务处理中的数据暂存：在一个事务中，可能需要对数据进行一些中间处理，并且这些中间数据不需要永久保存。此时可以使用临时表。例如，在一个转账事务中，可能需要先将转出账户和转入账户的余额读取到临时表中，进行计算后再更新实际的账户余额。

BEGIN TRANSACTION;

-- 创建临时表存储账户余额
CREATE TEMP TABLE temp_accounts (
    account_id INTEGER PRIMARY KEY,
    balance REAL
);

-- 将转出账户和转入账户的余额插入临时表
INSERT INTO temp_accounts (account_id, balance)
SELECT account_id, balance FROM accounts WHERE account_id IN (1, 2);

-- 在临时表中进行余额计算
UPDATE temp_accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE temp_accounts
SET balance = balance + 100
WHERE account_id = 2;

-- 根据临时表中的计算结果更新实际账户余额
UPDATE accounts
SET balance = temp_accounts.balance
FROM temp_accounts
WHERE accounts.account_id = temp_accounts.account_id;

COMMIT;

在上述事务处理过程中，通过临时表 temp_accounts 暂存账户余额，进行计算后再更新实际的 accounts 表中的余额，确保了事务的一致性和数据的准确性。

最佳实践总结

数据库设计原则

1. 遵循范式：数据库设计应尽量遵循范式，通常第一范式（1NF）要求每列都是原子的，即不可再分；第二范式（2NF）在满足 1NF 的基础上，要求每个非主键列完全依赖于主键；第三范式（3NF）在满足 2NF 的基础上，要求每个非主键列不传递依赖于主键。遵循范式可以减少数据冗余，提高数据的一致性和完整性。例如，在设计用户表和订单表时，避免在订单表中重复存储用户的详细信息，而是通过外键关联到用户表。
2. 适度反范式：虽然遵循范式有很多优点，但在某些情况下，适度的反范式可以提高查询性能。例如，在一些查询频繁且对实时性要求不高的场景下，可以在表中适当增加冗余字段。比如在订单表中增加用户的姓名等常用信息，这样在查询订单信息时，不需要每次都通过外键关联到用户表获取姓名，从而减少连接操作，提高查询效率。但要注意，反范式可能会带来数据一致性的问题，需要在设计和维护过程中加以注意。

表结构优化

1. 数据类型选择：根据实际数据的范围和特点选择合适的数据类型。例如，对于表示人的年龄的字段，使用 INTEGER 类型即可，避免使用 REAL 类型造成不必要的空间浪费和精度问题。对于存储固定长度文本的字段，如性别（“男”或“女”），可以使用 CHAR 类型并指定合适的长度，以提高存储效率。
2. 列的顺序：虽然 SQLite 并不依赖于列的顺序来访问数据，但在设计表结构时，将经常一起使用的列放在相邻位置，可以提高查询性能。例如，在一个员工表中，将 employee_id、employee_name、department_id 等经常在查询中一起使用的列放在相邻位置，可能会使查询执行计划更优化。

索引的合理使用

1. 主键索引：SQLite 会自动为主键创建索引，这对于提高查询性能非常重要。因为主键通常用于唯一标识记录，在查询、更新和删除操作中经常会根据主键进行条件匹配，主键索引可以大大加快这些操作的速度。
2. 普通索引：除了主键索引，根据查询需求创建适当的普通索引也能显著提升性能。例如，如果经常根据 users 表中的 email 列进行查询，可以为 email 列创建索引：

CREATE INDEX idx_email ON users (email);

这样在执行 SELECT * FROM users WHERE email = 'example@example.com'; 这样的查询时，SQLite 可以利用 idx_email 索引快速定位到符合条件的记录，而不需要全表扫描。但要注意，索引也会占用额外的存储空间，并且在插入、更新和删除操作时会增加系统开销，因为需要同时更新索引。所以要根据实际应用场景，合理创建索引，避免创建过多不必要的索引。

事务管理

1. 合理使用事务：在进行涉及多个操作的业务逻辑时，要使用事务来确保数据的一致性和完整性。例如，在银行转账操作中，从一个账户扣款和向另一个账户存款这两个操作必须在一个事务中完成。如果其中一个操作失败，整个事务回滚，以保证资金的正确性。

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

2. 事务的隔离级别：SQLite 支持不同的事务隔离级别，如 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。默认的隔离级别是 SERIALIZABLE，它提供了最高的数据一致性，但可能会在高并发场景下导致性能问题。在一些对数据一致性要求不是特别严格的场景下，可以适当降低隔离级别，以提高并发性能。例如，在一些只读查询较多的应用中，可以选择 READ COMMITTED 隔离级别，这样可以减少锁的持有时间，提高并发性能。

性能测试与优化

1. 使用 EXPLAIN 分析查询计划：在 SQLite 中，可以使用 EXPLAIN 关键字来分析查询计划，了解 SQLite 是如何执行查询的。例如，对于查询 SELECT * FROM users WHERE age > 30;，可以使用 EXPLAIN SELECT * FROM users WHERE age > 30; 查看查询计划。通过分析查询计划，可以发现是否使用了索引，是否存在全表扫描等性能问题，从而针对性地进行优化，如创建合适的索引等。
2. 性能测试工具：使用性能测试工具对数据库操作进行压力测试和性能分析。例如，可以使用开源的工具如 JMeter 来模拟大量并发用户对数据库进行读写操作，观察系统的性能指标，如响应时间、吞吐量等。根据性能测试的结果，对数据库设计、表结构、索引等进行优化，以提高系统的整体性能。

通过遵循上述最佳实践，可以在使用 SQLite 创建数据库和表时，设计出高效、稳定且易于维护的数据库结构，满足不同应用场景的需求。无论是小型的桌面应用还是大型的移动应用，合理的数据库设计和优化都能为应用的性能和稳定性提供有力保障。