数据插入 #
一、INSERT基础 #
1.1 基本语法 #
sql
-- 基本插入语法
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
-- 示例
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入单行数据
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 25);
-- 查询结果
SELECT * FROM users;
1.2 插入完整行 #
sql
-- 插入所有列
INSERT INTO users (id, name, email, age, created_at)
VALUES (NULL, 'Bob', 'bob@example.com', 30, NOW());
-- 省略列名，按顺序插入所有列
INSERT INTO users
VALUES (NULL, 'Carol', 'carol@example.com', 28, NOW());
-- 使用 DEFAULT 关键字
INSERT INTO users (name, email, age, created_at)
VALUES ('David', 'david@example.com', 35, DEFAULT);
1.3 指定列插入 #
sql
-- 只插入部分列，其他列使用默认值
INSERT INTO users (name, email)
VALUES ('Eve', 'eve@example.com');
-- age 和 created_at 使用默认值
SELECT * FROM users WHERE name = 'Eve';
二、批量插入 #
2.1 多行插入 #
sql
-- 单条语句插入多行
INSERT INTO users (name, email, age) VALUES
('Frank', 'frank@example.com', 32),
('Grace', 'grace@example.com', 27),
('Henry', 'henry@example.com', 29),
('Ivy', 'ivy@example.com', 24);
-- 查询结果
SELECT * FROM users;
2.2 批量插入优化 #
text
批量插入性能优化
┌─────────────────────────────────────────────────────────────┐
│ │
│ 单条插入 vs 批量插入: │
│ │
│ 单条插入 (多条语句): │
│ INSERT INTO users VALUES (...); -- 语句1 │
│ INSERT INTO users VALUES (...); -- 语句2 │
│ INSERT INTO users VALUES (...); -- 语句3 │
│ │
│ 问题: │
│ - 每条语句都有网络开销 │
│ - 每条语句都有事务开销 │
│ - 性能较差 │
│ │
│ ───────────────────────────────────────────────────── │
│ │
│ 批量插入 (单条语句): │
│ INSERT INTO users VALUES (...), (...), (...); │
│ │
│ 优势: │
│ - 单次网络传输 │
│ - 单次事务提交 │
│ - 性能提升 10-100x │
│ │
└─────────────────────────────────────────────────────────────┘
2.3 最佳批量大小 #
sql
-- 推荐批量大小: 100-1000 行每批次
-- 过大可能导致内存问题或超时
-- 示例: 分批插入大量数据
-- 批次1
INSERT INTO users (name, email, age) VALUES
('User001', 'user001@example.com', 20),
('User002', 'user002@example.com', 21),
-- ... 更多行
('User500', 'user500@example.com', 25);
-- 批次2
INSERT INTO users (name, email, age) VALUES
('User501', 'user501@example.com', 26),
('User502', 'user502@example.com', 27),
-- ... 更多行
('User1000', 'user1000@example.com', 30);
三、INSERT SELECT #
3.1 从其他表复制数据 #
sql
-- 创建目标表
CREATE TABLE users_backup (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
backup_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 从源表复制数据
INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users;
-- 带条件复制
INSERT INTO users_backup (name, email, age)
SELECT name, email, age
FROM users
WHERE age > 25;
3.2 表间数据迁移 #
sql
-- 创建归档表
CREATE TABLE orders_archive (
id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMP,
archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 归档旧数据
INSERT INTO orders_archive (id, user_id, amount, status, created_at)
SELECT id, user_id, amount, status, created_at
FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
-- 删除已归档数据
DELETE FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
3.3 数据转换插入 #
sql
-- 创建汇总表
CREATE TABLE user_stats (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT,
total_orders INT,
total_amount DECIMAL(20, 2),
avg_amount DECIMAL(10, 2),
stat_date DATE
);
-- 聚合数据插入
INSERT INTO user_stats (user_id, total_orders, total_amount, avg_amount, stat_date)
SELECT
user_id,
COUNT(*) AS total_orders,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount,
CURRENT_DATE AS stat_date
FROM orders
WHERE status = 'completed'
GROUP BY user_id;
四、INSERT IGNORE #
4.1 忽略重复数据 #
sql
-- 创建有唯一约束的表
CREATE TABLE emails (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) UNIQUE,
user_id BIGINT
);
-- 插入数据
INSERT INTO emails (email, user_id) VALUES ('test@example.com', 1);
-- 普通插入会报错
INSERT INTO emails (email, user_id) VALUES ('test@example.com', 2);
-- Error: Duplicate entry 'test@example.com' for key 'email'
-- 使用 INSERT IGNORE 忽略错误
INSERT IGNORE INTO emails (email, user_id)
VALUES ('test@example.com', 2);
-- Query OK, 0 rows affected (不会报错，但也不插入)
-- 查看结果
SELECT * FROM emails;
4.2 批量忽略重复 #
sql
-- 批量插入时忽略重复
INSERT IGNORE INTO emails (email, user_id) VALUES
('new@example.com', 3),
('test@example.com', 4), -- 重复，被忽略
('another@example.com', 5);
-- 查看结果
SELECT * FROM emails;
五、INSERT ON DUPLICATE KEY UPDATE #
5.1 存在则更新 #
sql
-- 创建表
CREATE TABLE user_scores (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT UNIQUE,
score INT DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 插入或更新
INSERT INTO user_scores (user_id, score)
VALUES (1, 100)
ON DUPLICATE KEY UPDATE
score = score + 100,
updated_at = NOW();
-- 第一次执行: 插入新记录
-- 第二次执行: 更新已有记录
SELECT * FROM user_scores;
5.2 使用VALUES函数 #
sql
-- VALUES() 获取要插入的值
INSERT INTO user_scores (user_id, score)
VALUES (1, 50)
ON DUPLICATE KEY UPDATE
score = VALUES(score), -- 使用新值覆盖
updated_at = NOW();
-- 批量插入或更新
INSERT INTO user_scores (user_id, score) VALUES
(1, 100),
(2, 200),
(3, 300)
ON DUPLICATE KEY UPDATE
score = VALUES(score),
updated_at = NOW();
5.3 实际应用场景 #
sql
-- 计数器场景
CREATE TABLE page_views (
page_id BIGINT PRIMARY KEY,
view_count BIGINT DEFAULT 0,
last_view TIMESTAMP
);
-- 每次访问增加计数
INSERT INTO page_views (page_id, view_count, last_view)
VALUES (1, 1, NOW())
ON DUPLICATE KEY UPDATE
view_count = view_count + 1,
last_view = NOW();
-- 库存更新场景
CREATE TABLE inventory (
product_id BIGINT PRIMARY KEY,
quantity INT DEFAULT 0
);
-- 入库
INSERT INTO inventory (product_id, quantity)
VALUES (1, 100)
ON DUPLICATE KEY UPDATE
quantity = quantity + VALUES(quantity);
六、REPLACE语句 #
6.1 REPLACE语法 #
sql
-- REPLACE: 存在则删除后插入，不存在则直接插入
REPLACE INTO user_scores (user_id, score)
VALUES (1, 500);
-- 注意: REPLACE 会删除旧行再插入新行
-- 自增ID会变化，触发器也会触发
-- 查看结果
SELECT * FROM user_scores;
6.2 REPLACE vs INSERT ON DUPLICATE KEY UPDATE #
text
REPLACE vs INSERT ON DUPLICATE KEY UPDATE
┌─────────────────────────────────────────────────────────────┐
│ │
│ REPLACE: │
│ ├── 存在重复键时: 删除旧行 + 插入新行 │
│ ├── 自增ID会变化 │
│ ├── 触发 DELETE + INSERT 触发器 │
│ └── 可能影响关联数据 │
│ │
│ INSERT ON DUPLICATE KEY UPDATE: │
│ ├── 存在重复键时: 更新旧行 │
│ ├── 自增ID不变 │
│ ├── 触发 UPDATE 触发器 │
│ └── 不影响关联数据 │
│ │
│ 推荐使用: INSERT ON DUPLICATE KEY UPDATE │
│ │
└─────────────────────────────────────────────────────────────┘
七、LOAD DATA导入 #
7.1 基本语法 #
sql
-- 从文件导入数据
LOAD DATA LOCAL INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, email, age);
-- CSV 文件示例:
-- name,email,age
-- Alice,alice@example.com,25
-- Bob,bob@example.com,30
-- Carol,carol@example.com,28
7.2 导入选项 #
sql
-- 完整导入示例
LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS
TERMINATED BY ',' -- 字段分隔符
ENCLOSED BY '"' -- 字段引号
ESCAPED BY '\\' -- 转义字符
LINES
TERMINATED BY '\n' -- 行分隔符
STARTING BY '' -- 行起始符
IGNORE 1 LINES -- 忽略首行(标题)
(name, email, age) -- 列映射
SET created_at = NOW(); -- 设置额外列
7.3 使用TiDB Lightning #
bash
# 对于大数据量导入，推荐使用 TiDB Lightning
# 配置文件 lightning.toml
[lightning]
level = "info"
[tikv-importer]
backend = "local" # 或 "tidb"
[mydumper]
data-source-dir = "/path/to/csv/files"
[checkpoint]
enable = true
# 执行导入
tidb-lightning -config lightning.toml
八、性能优化 #
8.1 插入性能建议 #
text
插入性能优化建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 使用批量插入 │
│ ├── 单条语句插入多行 │
│ └── 推荐每批 100-1000 行 │
│ │
│ 2. 减少索引数量 │
│ ├── 插入时维护索引有开销 │
│ └── 大量导入前可先删除索引 │
│ │
│ 3. 使用事务 │
│ ├── 多条插入放在一个事务中 │
│ └── 减少事务提交次数 │
│ │
│ 4. 关闭约束检查 │
│ ├── 大量导入时可临时关闭 │
│ └── 导入完成后恢复 │
│ │
│ 5. 使用 LOAD DATA │
│ ├── 比 INSERT 更快 │
│ └── 适合大数据量导入 │
│ │
│ 6. 使用 TiDB Lightning │
│ ├── 最快的导入方式 │
│ └── 适合 TB 级数据 │
│ │
└─────────────────────────────────────────────────────────────┘
8.2 事务批量插入 #
sql
-- 使用事务批量插入
START TRANSACTION;
INSERT INTO users (name, email, age) VALUES
('User001', 'user001@example.com', 20),
('User002', 'user002@example.com', 21),
-- ... 更多行
('User500', 'user500@example.com', 25);
INSERT INTO users (name, email, age) VALUES
('User501', 'user501@example.com', 26),
('User502', 'user502@example.com', 27),
-- ... 更多行
('User1000', 'user1000@example.com', 30);
COMMIT;
九、常见问题 #
9.1 主键冲突 #
sql
-- 问题: 插入重复主键
INSERT INTO users (id, name) VALUES (1, 'Duplicate');
-- Error: Duplicate entry '1' for key 'PRIMARY'
-- 解决方案1: 使用 INSERT IGNORE
INSERT IGNORE INTO users (id, name) VALUES (1, 'Duplicate');
-- 解决方案2: 使用 ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name) VALUES (1, 'Duplicate')
ON DUPLICATE KEY UPDATE name = VALUES(name);
-- 解决方案3: 使用 REPLACE
REPLACE INTO users (id, name) VALUES (1, 'Duplicate');
9.2 字段类型不匹配 #
sql
-- 问题: 类型不匹配
INSERT INTO users (name, age) VALUES ('Test', 'not_a_number');
-- Error: Incorrect integer value
-- 解决方案: 确保数据类型正确
INSERT INTO users (name, age) VALUES ('Test', 25);
-- 或使用类型转换
INSERT INTO users (name, age) VALUES ('Test', CAST('25' AS SIGNED));
9.3 字段长度超限 #
sql
-- 问题: 字段值超长
INSERT INTO users (name) VALUES ('This is a very long name that exceeds the column limit...');
-- Error: Data too long for column 'name'
-- 解决方案: 调整字段长度或截断数据
ALTER TABLE users MODIFY name VARCHAR(200);
-- 或使用函数截断
INSERT INTO users (name) VALUES (LEFT('very long name...', 100));
十、总结 #
INSERT 语句要点：
| 类型 | 说明 |
|---|---|
| 基本插入 | 单行、多行、指定列 |
| 批量插入 | 多行 VALUES，性能优化 |
| INSERT SELECT | 从其他表复制数据 |
| INSERT IGNORE | 忽略重复错误 |
| ON DUPLICATE KEY UPDATE | 存在则更新 |
| REPLACE | 删除后插入 |
| LOAD DATA | 文件导入 |
下一步，让我们学习数据更新操作！
最后更新：2026-03-27