数据插入 #
一、INSERT基础 #
1.1 基本语法 #
sql
-- 基本插入语法
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
-- 示例
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT NOW()
);
-- 插入单行数据
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 (gen_random_uuid(), 'Bob', 'bob@example.com', 30, NOW());
-- 省略列名，按顺序插入所有列
INSERT INTO users
VALUES (gen_random_uuid(), '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 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100),
email VARCHAR(100),
age INT,
backup_time TIMESTAMP DEFAULT NOW()
);
-- 从源表复制数据
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 UUID PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMP,
archived_at TIMESTAMP DEFAULT NOW()
);
-- 归档旧数据
INSERT INTO orders_archive (id, user_id, amount, status, created_at)
SELECT id, user_id, amount, status, created_at
FROM orders
WHERE created_at < NOW() - INTERVAL '1 year';
-- 删除已归档数据
DELETE FROM orders
WHERE created_at < NOW() - INTERVAL '1 year';
3.3 数据转换插入 #
sql
-- 创建汇总表
CREATE TABLE user_stats (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
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 ON CONFLICT #
4.1 存在则更新 #
sql
-- 创建表
CREATE TABLE user_scores (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id BIGINT UNIQUE,
score INT DEFAULT 0,
updated_at TIMESTAMP DEFAULT NOW()
);
-- 插入或更新 (UPSERT)
INSERT INTO user_scores (user_id, score)
VALUES (1, 100)
ON CONFLICT (user_id)
DO UPDATE SET
score = user_scores.score + EXCLUDED.score,
updated_at = NOW();
-- 第一次执行: 插入新记录
-- 第二次执行: 更新已有记录
SELECT * FROM user_scores;
4.2 存在则忽略 #
sql
-- 创建有唯一约束的表
CREATE TABLE emails (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
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 key value violates unique constraint
-- 使用 ON CONFLICT DO NOTHING 忽略
INSERT INTO emails (email, user_id)
VALUES ('test@example.com', 2)
ON CONFLICT DO NOTHING;
-- 不报错，也不插入
-- 查看结果
SELECT * FROM emails;
4.3 批量UPSERT #
sql
-- 批量插入或更新
INSERT INTO user_scores (user_id, score) VALUES
(1, 100),
(2, 200),
(3, 300)
ON CONFLICT (user_id)
DO UPDATE SET
score = EXCLUDED.score,
updated_at = NOW();
-- 使用 EXCLUDED 引用新值
INSERT INTO user_scores (user_id, score) VALUES
(1, 50),
(2, 60)
ON CONFLICT (user_id)
DO UPDATE SET
score = user_scores.score + EXCLUDED.score;
4.4 实际应用场景 #
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 CONFLICT (page_id)
DO UPDATE SET
view_count = page_views.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 CONFLICT (product_id)
DO UPDATE SET
quantity = inventory.quantity + EXCLUDED.quantity;
五、UPSERT语句 #
5.1 UPSERT语法 #
sql
-- UPSERT 是 INSERT ON CONFLICT 的简写
-- 存在则更新，不存在则插入
-- 使用 UPSERT
UPSERT INTO user_scores (user_id, score)
VALUES (1, 500);
-- 等价于
INSERT INTO user_scores (user_id, score)
VALUES (1, 500)
ON CONFLICT (user_id)
DO UPDATE SET score = EXCLUDED.score;
-- 批量 UPSERT
UPSERT INTO user_scores (user_id, score) VALUES
(1, 100),
(2, 200),
(3, 300);
5.2 UPSERT vs INSERT ON CONFLICT #
text
UPSERT vs INSERT ON CONFLICT
┌─────────────────────────────────────────────────────────────┐
│ │
│ UPSERT: │
│ ├── 语法简洁 │
│ ├── 总是更新所有列 │
│ └── 适合简单场景 │
│ │
│ INSERT ON CONFLICT: │
│ ├── 语法灵活 │
│ ├── 可以指定更新哪些列 │
│ ├── 可以使用 DO NOTHING │
│ └── 适合复杂场景 │
│ │
│ 示例对比: │
│ │
│ -- UPSERT: 更新所有列 │
│ UPSERT INTO users (id, name, email) │
│ VALUES (1, 'Alice', 'new@example.com'); │
│ │
│ -- INSERT ON CONFLICT: 只更新 email │
│ INSERT INTO users (id, name, email) │
│ VALUES (1, 'Alice', 'new@example.com') │
│ ON CONFLICT (id) DO UPDATE SET │
│ email = EXCLUDED.email; │
│ │
└─────────────────────────────────────────────────────────────┘
六、导入数据 #
6.1 IMPORT INTO #
sql
-- 从CSV文件导入
IMPORT INTO users (name, email, age)
CSV DATA (
'nodelocal://1/users.csv'
);
-- 带选项导入
IMPORT INTO users (name, email, age)
CSV DATA (
'nodelocal://1/users.csv'
)
WITH
skip = '1', -- 跳过第一行
delimiter = ',', -- 分隔符
quote = '"', -- 引号
nullif = ''; -- 空值处理
-- 从多个文件导入
IMPORT INTO users (name, email, age)
CSV DATA (
'nodelocal://1/users1.csv',
'nodelocal://1/users2.csv'
);
6.2 使用COPY #
sql
-- 从客户端复制数据
COPY users (name, email, age) FROM STDIN WITH CSV HEADER;
Alice,alice@example.com,25
Bob,bob@example.com,30
\.
-- 导出数据
COPY users TO STDOUT WITH CSV HEADER;
七、性能优化 #
7.1 插入性能建议 #
text
插入性能优化建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 使用批量插入 │
│ ├── 单条语句插入多行 │
│ └── 推荐每批 100-1000 行 │
│ │
│ 2. 减少索引数量 │
│ ├── 插入时维护索引有开销 │
│ └── 大量导入前可先删除索引 │
│ │
│ 3. 使用事务 │
│ ├── 多条插入放在一个事务中 │
│ └── 减少事务提交次数 │
│ │
│ 4. 使用 IMPORT │
│ ├── 比 INSERT 更快 │
│ └── 适合大数据量导入 │
│ │
│ 5. 避免热点 │
│ ├── 使用 UUID 代替自增ID │
│ └── 分散写入压力 │
│ │
└─────────────────────────────────────────────────────────────┘
7.2 事务批量插入 #
sql
-- 使用事务批量插入
BEGIN;
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;
八、常见问题 #
8.1 主键冲突 #
sql
-- 问题: 插入重复主键
INSERT INTO users (id, name) VALUES ('uuid-1', 'Duplicate');
-- Error: duplicate key value violates unique constraint
-- 解决方案1: 使用 ON CONFLICT DO NOTHING
INSERT INTO users (id, name) VALUES ('uuid-1', 'Duplicate')
ON CONFLICT (id) DO NOTHING;
-- 解决方案2: 使用 ON CONFLICT DO UPDATE
INSERT INTO users (id, name) VALUES ('uuid-1', 'Duplicate')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
-- 解决方案3: 使用 UPSERT
UPSERT INTO users (id, name) VALUES ('uuid-1', 'Duplicate');
8.2 字段类型不匹配 #
sql
-- 问题: 类型不匹配
INSERT INTO users (name, age) VALUES ('Test', 'not_a_number');
-- Error: invalid input syntax for type int
-- 解决方案: 确保数据类型正确
INSERT INTO users (name, age) VALUES ('Test', 25);
-- 或使用类型转换
INSERT INTO users (name, age) VALUES ('Test', CAST('25' AS INT));
8.3 字段长度超限 #
sql
-- 问题: 字段值超长
INSERT INTO users (name) VALUES ('This is a very long name that exceeds the column limit...');
-- Error: value too long for type character varying(100)
-- 解决方案: 调整字段长度或截断数据
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
-- 或使用函数截断
INSERT INTO users (name) VALUES (SUBSTRING('very long name...', 1, 100));
九、总结 #
INSERT 语句要点：
| 类型 | 说明 |
|---|---|
| 基本插入 | 单行、多行、指定列 |
| 批量插入 | 多行 VALUES，性能优化 |
| INSERT SELECT | 从其他表复制数据 |
| ON CONFLICT | 存在则更新或忽略 |
| UPSERT | 简化的插入或更新 |
| IMPORT | 文件导入 |
下一步，让我们学习数据更新操作！
最后更新：2026-03-27