SQLite数据插入 #

-- INSERT 基本语法
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- 示例表结构
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    status INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- SQLite 支持多种插入方式：
-- 1. 指定列插入
-- 2. 全列插入
-- 3. 批量插入
-- 4. 查询插入
-- 5. 默认值插入

-- 指定列名插入
INSERT INTO users (name, email, age)
VALUES ('John', 'john@example.com', 25);

-- 查看结果
SELECT * FROM users;

-- id | name | email              | age | status | created_at
-- ---|------|--------------------|-----|--------|------------------
-- 1  | John | john@example.com   | 25  | 1      | 2024-01-15 10:00:00

-- 插入所有列（按表定义顺序）
INSERT INTO users
VALUES (NULL, 'Jane', 'jane@example.com', 30, 1, CURRENT_TIMESTAMP);

-- AUTOINCREMENT 列使用 NULL
INSERT INTO users
VALUES (NULL, 'Bob', 'bob@example.com', 28, 1, datetime('now'));

-- 使用默认值
INSERT INTO users (name, email, age, status)
VALUES ('Alice', 'alice@example.com', 22, DEFAULT);

-- 省略有默认值的列
INSERT INTO users (name, email, age)
VALUES ('Charlie', 'charlie@example.com', 35);

-- 使用表达式
INSERT INTO users (name, email, age, created_at)
VALUES (
    'David',
    lower('DAVID@EXAMPLE.COM'),
    20 + 5,
    datetime('now', '+1 day')
);

-- 使用子查询
INSERT INTO users (name, email, age)
SELECT 'Eve', 'eve@example.com', AVG(age) FROM users;

-- 一次插入多行
INSERT INTO users (name, email, age) VALUES
    ('User1', 'user1@example.com', 20),
    ('User2', 'user2@example.com', 25),
    ('User3', 'user3@example.com', 30),
    ('User4', 'user4@example.com', 35);

-- 性能优势：
-- 1. 减少SQL解析次数
-- 2. 减少事务开销
-- 3. 批量写入优化

-- 大批量插入优化技巧

-- 1. 使用事务
BEGIN TRANSACTION;
INSERT INTO users (name, email, age) VALUES
    ('User1', 'user1@example.com', 20),
    ('User2', 'user2@example.com', 25),
    -- ... 更多数据
    ('User1000', 'user1000@example.com', 25);
COMMIT;

-- 2. 临时关闭同步
PRAGMA synchronous = OFF;
-- 批量插入
PRAGMA synchronous = NORMAL;

-- 3. 使用 WAL 模式
PRAGMA journal_mode = WAL;

-- 从其他表复制数据
CREATE TABLE users_archive (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    archived_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users_archive (id, name, email)
SELECT id, name, email FROM users WHERE status = 0;

-- 创建备份表
CREATE TABLE users_backup AS SELECT * FROM users;

-- 或先创建结构再插入
CREATE TABLE users_copy LIKE users;
INSERT INTO users_copy SELECT * FROM users;

-- 从多表查询插入
CREATE TABLE order_summary (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    total_amount REAL
);

INSERT INTO order_summary (order_id, customer_name, total_amount)
SELECT 
    o.id,
    c.name,
    SUM(oi.quantity * oi.price)
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, c.name;

-- 使用子查询
INSERT INTO user_stats (user_id, order_count)
SELECT 
    u.id,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id)
FROM users u;

-- INSERT OR REPLACE：存在则替换，不存在则插入
INSERT OR REPLACE INTO users (id, name, email, age)
VALUES (1, 'John Updated', 'john@example.com', 26);

-- 注意：
-- 1. REPLACE 会删除旧行，插入新行
-- 2. AUTOINCREMENT 值会增加
-- 3. 会触发 DELETE 和 INSERT 触发器
-- 4. 其他列的值会丢失（除非在新行中指定）

-- INSERT OR IGNORE：忽略冲突，继续执行
INSERT OR IGNORE INTO users (id, name, email) VALUES
    (1, 'John', 'john@example.com'),   -- 已存在，忽略
    (2, 'Jane', 'jane@example.com'),   -- 插入成功
    (3, 'Bob', 'bob@example.com');     -- 插入成功

-- 查看结果：只有新数据被插入
SELECT * FROM users;

-- UPSERT：更灵活的冲突处理
-- ON CONFLICT DO UPDATE

-- 基本语法
INSERT INTO users (id, name, email, age)
VALUES (1, 'John', 'john@example.com', 26)
ON CONFLICT(id) DO UPDATE SET
    name = excluded.name,
    age = excluded.age;

-- 使用 WHERE 条件
INSERT INTO users (id, name, email, age)
VALUES (1, 'John', 'john@example.com', 26)
ON CONFLICT(id) DO UPDATE SET
    name = excluded.name,
    age = excluded.age
WHERE excluded.age > users.age;

-- ON CONFLICT DO NOTHING
INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON CONFLICT(id) DO NOTHING;

-- 多列冲突
INSERT INTO users (email, name, age)
VALUES ('john@example.com', 'John', 26)
ON CONFLICT(email) DO UPDATE SET
    name = excluded.name,
    age = excluded.age;

-- 对比各种冲突处理方式

-- 1. INSERT OR REPLACE
-- 删除旧行，插入新行
-- 所有未指定的列会变为NULL或默认值

-- 2. INSERT OR IGNORE
-- 忽略冲突行，继续插入其他行
-- 已存在的行保持不变

-- 3. UPSERT (推荐)
-- 存在则更新指定列，不存在则插入
-- 可以精确控制更新哪些列
-- 可以添加WHERE条件

-- 获取最后插入的 ROWID
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');
SELECT last_insert_rowid();

-- 在应用程序中使用
-- Python: cursor.lastrowid
-- PHP: $pdo->lastInsertId()
-- Java: Statement.getGeneratedKeys()

-- RETURNING：插入后返回数据
INSERT INTO users (name, email, age)
VALUES ('John', 'john@example.com', 25)
RETURNING id, name, created_at;

-- 批量插入返回
INSERT INTO users (name, email) VALUES
    ('User1', 'user1@example.com'),
    ('User2', 'user2@example.com')
RETURNING id, name;

-- 返回所有列
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING *;

-- 插入NULL
INSERT INTO users (name, email, age)
VALUES ('Test', 'test@example.com', NULL);

-- 省略列（使用默认值或NULL）
INSERT INTO users (name, email)
VALUES ('Test', 'test@example.com');
-- age 列为 NULL

-- 插入日期时间
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT,
    event_date DATE,
    event_time TIME,
    event_datetime DATETIME
);

-- 使用字符串格式
INSERT INTO events (name, event_date, event_time, event_datetime)
VALUES (
    'Meeting',
    '2024-01-15',
    '10:30:00',
    '2024-01-15 10:30:00'
);

-- 使用函数
INSERT INTO events (name, event_datetime)
VALUES ('Event1', datetime('now'));

INSERT INTO events (name, event_date)
VALUES ('Event2', date('now'));

INSERT INTO events (name, event_time)
VALUES ('Event3', time('now'));

-- 使用时间计算
INSERT INTO events (name, event_datetime)
VALUES ('Event4', datetime('now', '+7 days'));

-- 插入JSON数据
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    attributes TEXT  -- JSON存储为TEXT
);

-- 插入JSON字符串
INSERT INTO products (name, attributes)
VALUES ('Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB"}');

-- 使用JSON函数
INSERT INTO products (name, attributes)
VALUES ('Phone', json_object('brand', 'Apple', 'model', 'iPhone 15'));

-- 插入JSON数组
INSERT INTO products (name, attributes)
VALUES ('Tablet', json_array('iPad', 'Samsung Tab', 'Surface'));

-- 插入二进制数据
CREATE TABLE files (
    id INTEGER PRIMARY KEY,
    name TEXT,
    data BLOB,
    mime_type TEXT
);

-- 插入十六进制数据
INSERT INTO files (name, data, mime_type)
VALUES ('test', X'010203040506070809', 'application/octet-stream');

-- 使用 zeroblob
INSERT INTO files (name, data, mime_type)
VALUES ('empty', zeroblob(1024), 'application/octet-stream');

-- 从文件加载（需要扩展）
-- INSERT INTO files (name, data)
-- SELECT 'file.pdf', readfile('/path/to/file.pdf');

-- 性能对比（插入10000条记录）

-- 方法1：单条插入（最慢）
-- 约 10-30 秒
BEGIN;
INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');
-- ... 重复10000次
COMMIT;

-- 方法2：多行VALUES（较快）
-- 约 1-3 秒
BEGIN;
INSERT INTO users (name, email) VALUES
    ('User1', 'user1@example.com'),
    ('User2', 'user2@example.com'),
    -- ... 更多数据
    ('User10000', 'user10000@example.com');
COMMIT;

-- 方法3：事务 + 批量（推荐）
-- 约 0.5-1 秒
BEGIN;
-- 每1000条一个INSERT
INSERT INTO users (name, email) VALUES ('User1', ...) ... ('User1000', ...);
INSERT INTO users (name, email) VALUES ('User1001', ...) ... ('User2000', ...);
-- ...
COMMIT;

-- 优化配置
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -10000;  -- 10MB

-- 大批量插入
PRAGMA temp_store = MEMORY;

-- 执行批量插入
BEGIN TRANSACTION;
-- 插入操作
COMMIT;

-- 错误：直接拼接SQL
-- INSERT INTO users (name) VALUES ('John'); DROP TABLE users;--')

-- 正确：使用参数化查询
-- Python
-- cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))

-- PHP
-- $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
-- $stmt->execute([$name, $email]);

-- Java
-- PreparedStatement pstmt = conn.prepareStatement("INSERT INTO users VALUES (?, ?)");
-- pstmt.setString(1, name);
-- pstmt.setString(2, email);

-- 使用 CHECK 约束验证数据
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL CHECK(length(name) >= 2),
    email TEXT CHECK(email LIKE '%@%.%'),
    age INTEGER CHECK(age >= 0 AND age <= 150)
);

-- 插入无效数据会失败
INSERT INTO users (name, email, age) VALUES ('A', 'invalid', 200);
-- Error: CHECK constraint failed

-- 错误：UNIQUE constraint failed
INSERT INTO users (id, name) VALUES (1, 'John');

-- 解决方案1：使用 UPSERT
INSERT INTO users (id, name) VALUES (1, 'John')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

-- 解决方案2：使用 INSERT OR REPLACE
INSERT OR REPLACE INTO users (id, name) VALUES (1, 'John');

-- 解决方案3：使用 INSERT OR IGNORE
INSERT OR IGNORE INTO users (id, name) VALUES (1, 'John');

-- 错误：UNIQUE constraint failed: users.email
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

-- 解决方案
INSERT INTO users (name, email) VALUES ('John', 'john@example.com')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

-- 启用外键
PRAGMA foreign_keys = ON;

-- 错误：FOREIGN KEY constraint failed
INSERT INTO orders (user_id, total) VALUES (999, 100);

-- 解决方案：确保外键值存在
INSERT INTO users (id, name) VALUES (999, 'User999');
INSERT INTO orders (user_id, total) VALUES (999, 100);

-- 错误：NOT NULL constraint failed
INSERT INTO users (email) VALUES ('test@example.com');

-- 解决方案：提供所有必需的值
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');

-- 或使用默认值
ALTER TABLE users ADD COLUMN name TEXT NOT NULL DEFAULT 'Anonymous';