📊 SQL 入门 Day 16：数据插入技巧

前端阿凡
123 阅读5分钟

📌 今日目标

掌握高级数据插入技巧，告别单条逐行 INSERT，学会高效批量操作与冲突处理。

一、为什么需要「插入技巧」？

场景 1：数据迁移 — 将旧表数据导入新表，改写表结构或字段名

场景 2：ETL 导入 — 从 CSV / API 批量写入数万行数据

场景 3：避免重复 — 插入时检测主键/唯一键冲突，自动更新或忽略

只会 INSERT INTO ... VALUES (...) 的 SQL 是写不坏业务的，但写不出效率。

二、INSERT INTO SELECT：查询结果直接写入

语法

INSERT INTO 目标表 (字段1, 字段2, ...)
SELECT 字段1, 字段2, ...
FROM 源表
WHERE 条件;

示例：从 users 表导入 VIP 用户到 vip_users

-- 创建 vip_users 表
CREATE TABLE vip_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    level VARCHAR(20),
    created_at DATETIME
);

-- 将 users 表中积分 > 1000 的用户插入 vip_users
INSERT INTO vip_users (name, email, level, created_at)
SELECT u.name, u.email, '黄金', u.created_at
FROM users u
WHERE u.points > 1000;

执行结果：

Query OK, 42 rows affected (0.01 sec)
Records: 42  Duplicates: 0  Warnings: 0

注意事项

  • 字段数量和类型必须一一对应
  • 可用表达式、常量、函数，如上面硬编码了 '黄金'
  • 注意字符集一致，避免乱码

三、批量 INSERT：一次写入多行

基础语法

INSERT INTO 表 (字段1, 字段2) VALUES
    (值1a, 值2a),
    (值1b, 值2b),
    (值1c, 值2c);

示例：批量插入订单数据

INSERT INTO orders (user_id, product, amount, status) VALUES
    (101, '笔记本电脑', 5999.00, '已支付'),
    (102, '机械键盘', 899.00, '待支付'),
    (103, '显示器', 2499.00, '已支付'),
    (104, '鼠标垫', 29.90, '已取消'),
    (105, 'USB Hub', 89.00, '待支付');

性能对比

方式写入 1000 行特点
逐条 INSERT~850ms每条独立事务，开销大
批量 INSERT（100 条/批）~45ms单条 SQL，大幅减少网络与日志 IO
LOAD DATA INFILE~10msMySQL 原生高速导入，跳过 SQL 层

经验法则： 单次批量 INSERT 建议 100~500 行，过大可能导致 binlog 膨胀和锁竞争。

四、INSERT IGNORE：跳过重复行

语法

INSERT IGNORE INTO 表 (字段列表) VALUES (值列表);

示例

CREATE TABLE products (
    sku VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

INSERT IGNORE INTO products (sku, name, price) VALUES
    ('SKU001', '无线鼠标', 129.00),
    ('SKU002', '蓝牙耳机', 399.00),
    ('SKU001', '无线鼠标-副本', 99.00);  -- SKU001 已存在，此行被忽略

执行结果：

Query OK, 2 rows affected (0.00 sec)
Records: 3  Duplicates: 1  Warnings: 0

第 3 行因主键重复被跳过，其余 2 行插入成功，不报错。

五、ON DUPLICATE KEY UPDATE：冲突时自动更新

语法

INSERT INTO 表 (字段列表) VALUES (值列表)
ON DUPLICATE KEY UPDATE
    字段1 = 值1,
    字段2 = 值2;

示例：商品库存 upsert

CREATE TABLE inventory (
    sku VARCHAR(20) PRIMARY KEY,
    stock INT NOT NULL,
    updated_at DATETIME
);

-- 插入或更新库存
INSERT INTO inventory (sku, stock, updated_at) VALUES ('SKU001', 50, NOW())
ON DUPLICATE KEY UPDATE
    stock = stock + VALUES(stock),    -- VALUES() 引用本次插入的值
    updated_at = VALUES(updated_at);

多次执行后：

-- 第 1 次执行 → 插入 (SKU001, 50)
-- 第 2 次执行 → 更新 stock = 50 + 50 = 100
-- 第 3 次执行 → 更新 stock = 100 + 50 = 150

VALUES(字段) 是 MySQL 语法，引用本次 INSERT 部分的对应值。MySQL 8.0 起推荐使用别名方式：

INSERT INTO inventory (sku, stock, updated_at) VALUES ('SKU001', 50, NOW()) AS new
ON DUPLICATE KEY UPDATE
    stock = inventory.stock + new.stock,
    updated_at = new.updated_at;

实战：用户积分流水 upsert

INSERT INTO user_points (user_id, total_points, last_updated) VALUES (1001, 200, NOW())
ON DUPLICATE KEY UPDATE
    total_points = total_points + VALUES(total_points),
    last_updated = VALUES(last_updated);

六、REPLACE INTO：删除再插入

语法

REPLACE INTO 表 (字段列表) VALUES (值列表);

示例

REPLACE INTO products (sku, name, price) VALUES
    ('SKU001', '无线鼠标-升级版', 159.00);

⚠️ 与 ON DUPLICATE KEY UPDATE 的区别

行为REPLACEON DUPLICATE KEY UPDATE
冲突时先 DELETE 再 INSERT直接 UPDATE
自增 ID会变化（删除重建）保持不变
触发器DELETE + INSERT 各触发一次仅 UPDATE 触发器
性能较差（删除+插入两倍开销）较好（原地更新）

结论： 绝大多数场景优先用 ON DUPLICATE KEY UPDATE，除非你确实想重置整行数据。

七、各数据库冲突处理语法对照

数据库语法
MySQLON DUPLICATE KEY UPDATE
PostgreSQLON CONFLICT (字段) DO UPDATE SET ...
SQLiteON CONFLICT(字段) DO UPDATE SET ...
SQL ServerMERGE ... WHEN MATCHED THEN UPDATE

八、综合实战：每日销售汇总

-- 销售明细表
CREATE TABLE daily_sales_summary (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    total_qty INT DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0,
    PRIMARY KEY (sale_date, product_id)
);

-- 从订单明细汇总插入，冲突则累加
INSERT INTO daily_sales_summary (sale_date, product_id, total_qty, total_amount)
SELECT
    DATE(order_time),
    product_id,
    SUM(quantity),
    SUM(quantity * price)
FROM order_items
WHERE order_time >= '2026-07-24' AND order_time < '2026-07-25'
GROUP BY DATE(order_time), product_id
ON DUPLICATE KEY UPDATE
    total_qty = daily_sales_summary.total_qty + VALUES(total_qty),
    total_amount = daily_sales_summary.total_amount + VALUES(total_amount);

九、避坑指南 🚧

陷阱说明
批量插入过大单条 SQL 超过 max_allowed_packet（默认 64MB）会报错
忽略 timezoneVALUES(NOW()) 在冲突更新时用的是第二次插入的时间，而非当前时间
主键自增跳跃使用 ON DUPLICATE KEY UPDATE 冲突时自增计数器仍会 +1，造成 ID 空洞
REPLACE 副作用会删除索引外的其他行（外键约束下可能失败）

📝 今日总结

  • INSERT INTO ... SELECT — 数据迁移/复制的利器
  • 批量 INSERT — 大幅提升写入性能
  • INSERT IGNORE — 跳过重复，静默容错
  • ON DUPLICATE KEY UPDATE — "有则更新，无则插入"的 Upsert 模式
  • REPLACE — 慎用，有副作用

思考题： 假设你有一个日志表，每秒写入 1000 条。你会怎么设计插入方案来保证性能？提示：想想批量写入 + 表分区。

avatar
文章
阅读
粉丝