SQLite - 插入查询

使用 SQLite 进行现代数据插入

INSERT 语句用于向表中添加新的数据行。现代 SQLite 提供了强大而高效的数据插入方式，包括处理冲突和一次性插入多行数据。

基本的 INSERT 语法

基本语法要求指定表、要插入的列以及这些列的值。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

最佳实践： 始终明确列出列名。这使您的查询更具可读性，并且能更好地应对未来表结构的变化（例如，添加带有默认值的新列）。

虽然您可以在按正确顺序为每个列提供值时省略列列表，但这种做法很脆弱，不建议用于生产代码。

-- 这种方式不够健壮，应避免使用。
INSERT INTO table_name VALUES (value1, value2, ...);

示例：填充 employees 表

我们从一个示例 employees 表开始。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY, -- 自动递增
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    age INTEGER NOT NULL,
    salary REAL
);

现在，让我们插入一条员工记录：

INSERT INTO employees (name, email, age, salary)
VALUES ('Paul Smith', 'paul.smith@example.com', 32, 75000.00);

插入多行

要在单个高效的语句中插入多行，您可以提供一个逗号分隔的值元组列表。

INSERT INTO employees (name, email, age, salary)
VALUES
    ('Allen Johnson', 'allen.j@example.com', 25, 60000.00),
    ('Teddy Brown', 'teddy.b@example.com', 23, 55000.00),
    ('Mark Davis', 'mark.d@example.com', 25, 95000.00);

处理冲突：UPSERT

如果您尝试插入一条违反 UNIQUE 约束（例如 email）的记录会发生什么？默认情况下，SQLite 会引发错误。然而，现代 SQLite 提供了一个强大的 ON CONFLICT 子句（也称为 “UPSERT”），以优雅地处理这些情况。

该语法允许您在因约束冲突导致插入失败时指定一个操作。

INSERT INTO table_name (column_list)
VALUES (value_list)
ON CONFLICT(target_column) DO UPDATE SET ...;

让我们尝试为“Mark Davis”插入一条新记录，但如果其电子邮件已存在，则更新他的薪水。

-- 这将更新 Mark 的薪水，因为他的电子邮件已存在于表中。
INSERT INTO employees (name, email, age, salary)
VALUES ('Mark Davis', 'mark.d@example.com', 26, 98000.00)
ON CONFLICT(email) DO UPDATE SET
    salary = excluded.salary,
    age = excluded.age;

这里，excluded.salary 指的是尝试执行 INSERT 语句中的薪水值。另一个选项是执行无操作（do nothing）：

-- 如果此电子邮件的用户已存在，则不执行任何操作。
INSERT INTO employees (name, email, age, salary)
VALUES ('Kim West', 'kim.w@example.com', 22, 50000.00)
ON CONFLICT(email) DO NOTHING;

从另一个表填充数据

您可以通过从另一个表选择数据来将其插入到目标表中。这对于数据迁移、归档或创建汇总表非常有用。

假设我们有一个 new_hires（新员工）表，并且我们想将他们导入到我们的主 employees（员工）表中。

CREATE TABLE new_hires (name TEXT, email TEXT, age INTEGER, start_salary REAL);
INSERT INTO new_hires VALUES ('James Miller', 'james.m@example.com', 24, 52000.00);

-- 现在，将数据从 new_hires 插入到 employees 中。
INSERT INTO employees (name, email, age, salary)
SELECT name, email, age, start_salary
FROM new_hires
WHERE start_salary > 50000.00; -- （可选）过滤要插入的数据

实际考量

• 事务：当执行多个 INSERT 语句时，将它们包裹在 BEGIN TRANSACTION; … COMMIT; 块中。这会显著提高速度，并确保所有插入要么全部成功，要么全部失败（原子性）。
• 性能：在单个语句中插入多行比在循环中单独运行 INSERT 语句的性能要好得多。