加载数据

导学

空表没有价值。本节学习如何向表中插入数据，涵盖单条插入、批量插入、从文件加载三种方式，每个方式都配有完整的建表、插数据、操作前后对比和结果解读。

定义

INSERT：DML（数据操纵语言）语句，用于向表中添加新的行记录。LOAD DATA INFILE 则是 MySQL 提供的高性能批量导入机制，直接从文本文件读取数据。

前置准备：创建测试表

所有示例共用一张 employees 员工表。先创建并观察空表状态。

CREATE DATABASE IF NOT EXISTS company
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE company;

CREATE TABLE IF NOT EXISTS employees (
    emp_id      INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    emp_name    VARCHAR(20) NOT NULL COMMENT '姓名',
    dept        VARCHAR(20) DEFAULT '技术部' COMMENT '部门',
    score       DECIMAL(5,2) COMMENT '绩效分'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

当前数据状态：

SELECT * FROM employees;

Empty set (0.00 sec)

结果解读：employees 表已创建，但没有任何数据。

场景一：单条插入（明确指定列）

操作语句：

INSERT INTO employees (emp_name, dept, score)
VALUES ('大翔', '技术部', 100);

操作后的数据状态：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部100

结果解读：

  • 显式指定列名是推荐写法，即使表结构发生变化（如新增列），只要新增列有默认值，这条语句依然能正常执行
  • emp_id 由数据库自动生成

场景二：单条插入（不指定列）

当前数据状态：表中已有 1 条记录。

SELECT emp_id, emp_name FROM employees;
emp_idemp_name
1大翔

操作语句：

INSERT INTO employees
VALUES (NULL, '白歌', '技术部', NULL);

操作后的数据状态：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

⚠️ 易错点：不指定列名时，VALUES 中的值必须与表定义的列顺序完全一致，且数量一致。如果表结构变更（如中间新增了列），这条语句会直接报错或数据错位。生产环境严禁这种写法。

场景三：批量插入（推荐）

当前数据状态：先清空表，重新演示批量插入。

TRUNCATE TABLE employees;

SELECT emp_id, emp_name FROM employees;

Empty set (0.00 sec)

操作语句：

INSERT INTO employees (emp_name, dept, score)
VALUES
    ('大翔', '技术部', 100),
    ('白歌', '技术部', NULL);

操作后的数据状态：

SELECT * FROM employees ORDER BY emp_id;
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

结果解读：一条 INSERT 插入多行，是批量数据写入的最佳实践。相比循环执行单条 INSERT，它能大幅减少客户端与服务器之间的网络往返（Round Trip）。

场景四：INSERT ... SET 语法

当前数据状态：先清空表，重新演示。

TRUNCATE TABLE employees;

SELECT * FROM employees;

Empty set (0.00 sec)

操作语句：

INSERT INTO employees
SET emp_name = '大翔',
    dept = '技术部',
    score = 100;

操作后的数据状态：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部100

结果解读：INSERT ... SET 适合插入少量列，可读性好，但一次只能插一行。

场景五：INSERT ... SELECT 从其他表导入

当前数据状态：需要创建一张备份表，然后从 employees 中导入数据。先确保源表有数据。

-- 确保 employees 表有规范数据
TRUNCATE TABLE employees;

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

-- 创建备份表（结构与 employees 相同，但不要自增和默认值）
CREATE TABLE employees_backup (
    emp_id      INT PRIMARY KEY COMMENT '员工ID',
    emp_name    VARCHAR(20) NOT NULL COMMENT '姓名',
    dept        VARCHAR(20) COMMENT '部门',
    score       DECIMAL(5,2) COMMENT '绩效分'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT * FROM employees_backup;

Empty set (0.00 sec)

操作语句：

INSERT INTO employees_backup (emp_id, emp_name, dept, score)
SELECT emp_id, emp_name, dept, score
FROM employees;

操作后的数据状态：

SELECT * FROM employees_backup;
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

结果解读：INSERT ... SELECT 适合数据迁移、备份、拆分等场景。它从源表中查询数据，直接插入目标表，是服务器内部操作，无需经过客户端。

场景六：INSERT IGNORE 忽略重复

当前数据状态：先恢复 employees 表为规范数据。

TRUNCATE TABLE employees;
DROP TABLE IF EXISTS employees_backup;

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

SELECT emp_id, emp_name FROM employees;
emp_idemp_name
1大翔
2白歌

操作语句：尝试插入重复的主键。

INSERT INTO employees (emp_id, emp_name, dept, score)
VALUES (1, '大翔克隆', '市场部', 80);

操作后的数据状态：

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

使用 INSERT IGNORE：

INSERT IGNORE INTO employees (emp_id, emp_name, dept, score)
VALUES (1, '大翔克隆', '市场部', 80);

SHOW WARNINGS;
LevelCodeMessage
Warning1062Duplicate entry '1' for key 'PRIMARY'
SELECT emp_id, emp_name FROM employees;
emp_idemp_name
1大翔
2白歌

结果解读：INSERT IGNORE 遇到主键冲突时不会报错，而是生成一条 Warning 并跳过该行。表中仍只有 2 条记录，"大翔克隆"未被插入。

场景七：ON DUPLICATE KEY UPDATE 存在则更新

当前数据状态：

SELECT emp_id, emp_name, score FROM employees WHERE emp_id = 1;
emp_idemp_namescore
1大翔100

操作语句：

INSERT INTO employees (emp_id, emp_name, dept, score)
VALUES (1, '大翔', '技术部', 90)
ON DUPLICATE KEY UPDATE
    score = VALUES(score);

操作后的数据状态：

SELECT emp_id, emp_name, score FROM employees WHERE emp_id = 1;
emp_idemp_namescore
1大翔90

结果解读：ON DUPLICATE KEY UPDATE 实现了"存在则更新、不存在则插入"（Upsert）。emp_id = 1 已存在，所以执行了更新操作，绩效分从 100 调整为 90。

💡 注意：演示结束后，建议将数据恢复为规范状态：

UPDATE employees SET score = 100 WHERE emp_id = 1;

场景八：从 CSV 文件批量加载

假设有一个 /var/lib/mysql-files/employees.csv 文件（内容如下）：

大翔,技术部,100
白歌,技术部,

当前数据状态：

TRUNCATE TABLE employees;

SELECT emp_id, emp_name FROM employees ORDER BY emp_id;

Empty set (0.00 sec)

操作语句：

LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(emp_name, dept, score);

操作后的数据状态：

SELECT * FROM employees ORDER BY emp_id;
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

结果解读：

  • LOAD DATA INFILE 是 MySQL 最快的数据导入方式，比批量 INSERT 快 10-20 倍
  • MySQL 5.7 默认开启了 --secure-file-priv，文件必须放在指定目录下
  • 可通过 SHOW VARIABLES LIKE 'secure_file_priv'; 查看允许的路径

查看安全文件路径：

SHOW VARIABLES LIKE 'secure_file_priv';
Variable_nameValue
secure_file_priv/var/lib/mysql-files/

结果解读：只有 /var/lib/mysql-files/ 目录下的文件才能被 LOAD DATA INFILE 读取。

常见误区

误区正解
"一次插一条更灵活"批量插入大幅减少网络开销，性能提升显著。
LOAD DATA 可以随意读取任何路径受 secure-file-priv 限制，需放在白名单目录。
插入时忽略自增主键会报错AUTO_INCREMENT 列传入 NULL 或不传，会自动生成。

面试考点

Q：批量插入时，一次插入多少行最合适？

没有绝对标准。一般建议单条 INSERT 包含 1000-10000 行。太多会导致单条 SQL 过大，超出 max_allowed_packet 限制；太少则网络往返次数过多。

Q：LOAD DATA INFILE 和 LOAD DATA LOCAL INFILE 的区别？

LOAD DATA INFILE 要求文件在服务器端的 secure-file-priv 目录下；LOAD DATA LOCAL INFILE 允许从客户端本地读取文件上传。后者需要服务器开启 local_infile=1。

Q：插入时违反 UNIQUE 约束会怎样？

报错 Duplicate entry 'xxx' for key 'email'。可用 INSERT IGNORE 忽略错误继续执行，或用 ON DUPLICATE KEY UPDATE 实现"存在则更新、不存在则插入"。

小结

  • INSERT ... VALUES 是最基础的插入方式，批量多行是最佳实践
  • INSERT ... SELECT 用于表间数据迁移
  • LOAD DATA INFILE 是海量数据导入的最高效手段
  • 始终显式指定列名，避免表结构变更导致语句失效
  • INSERT IGNORE 跳过冲突，ON DUPLICATE KEY UPDATE 实现 Upsert

清理测试数据库（可选）：

-- DROP DATABASE IF EXISTS company;

下一章引子：数据已经入库，接下来学习如何从表中查询数据——这是 SQL 最核心的能力。