跳到主要内容

INSERT 插入

INSERT 语句用于向表中插入新数据。本章将详细介绍 INSERT 的各种用法。

基本 INSERT​

插入完整行​

为所有列插入值：

INSERT INTO users VALUES (1, '张三', '[email protected]', 25, '北京');
注意

这种方式不推荐，因为：

  • 列顺序必须与表定义一致
  • 表结构变化时可能出错
  • 可读性差

指定列名插入​

推荐的方式，明确指定列名：

INSERT INTO users (name, email, age, city)
VALUES ('张三', '[email protected]', 25, '北京');

优点：

  • 列顺序可以任意
  • 可以只插入部分列（其他列使用默认值或 NULL）
  • 代码更清晰，易于维护

插入多行​

一次插入多行数据：

INSERT INTO users (name, email, age, city) VALUES
    ('张三', '[email protected]', 25, '北京'),
    ('李四', '[email protected]', 28, '上海'),
    ('王五', '[email protected]', 22, '广州');

多行插入比多次单行插入效率更高，因为：

  • 只需一次网络通信
  • 只需一次语句解析
  • 可以批量优化

插入查询结果​

INSERT ... SELECT​

将查询结果插入表中：

-- 将活跃用户复制到新表
INSERT INTO active_users (name, email)
SELECT name, email FROM users WHERE status = 'active';

-- 创建备份表
INSERT INTO users_backup
SELECT * FROM users WHERE created_at < '2024-01-01';

表复制​

-- 创建表并复制数据
CREATE TABLE users_copy AS SELECT * FROM users;

-- 只复制表结构
CREATE TABLE users_empty LIKE users;

-- 复制部分数据
INSERT INTO users_archive
SELECT * FROM users WHERE created_at < '2023-01-01';

插入默认值​

使用 DEFAULT​

-- 使用默认值
INSERT INTO users (name, email) VALUES ('张三', '[email protected]');

-- 显式使用 DEFAULT
INSERT INTO users (name, email, age, city)
VALUES ('张三', '[email protected]', DEFAULT, DEFAULT);

DEFAULT VALUES​

某些数据库支持：

-- PostgreSQL / SQLite
INSERT INTO users DEFAULT VALUES;

-- MySQL
INSERT INTO users () VALUES ();

插入与主键​

自增主键​

-- 自增主键不需要指定
INSERT INTO users (name, email) VALUES ('张三', '[email protected]');

-- 获取最后插入的 ID
-- MySQL
SELECT LAST_INSERT_ID();

-- PostgreSQL
SELECT currval('users_id_seq');

-- SQL Server
SELECT SCOPE_IDENTITY();

-- SQLite
SELECT last_insert_rowid();

UUID 主键​

-- MySQL
INSERT INTO users (id, name, email)
VALUES (UUID(), '张三', '[email protected]');

-- PostgreSQL
INSERT INTO users (id, name, email)
VALUES (gen_random_uuid(), '张三', '[email protected]');

冲突处理​

MySQL: ON DUPLICATE KEY UPDATE​

INSERT INTO users (id, name, email) VALUES (1, '张三', '[email protected]')
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    email = VALUES(email);

PostgreSQL: ON CONFLICT​

-- 冲突时更新
INSERT INTO users (id, name, email) VALUES (1, '张三', '[email protected]')
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email;

-- 冲突时忽略
INSERT INTO users (id, name, email) VALUES (1, '张三', '[email protected]')
ON CONFLICT (id) DO NOTHING;

SQLite: ON CONFLICT​

-- 冲突时忽略
INSERT OR IGNORE INTO users (id, name, email) VALUES (1, '张三', '[email protected]');

-- 冲突时替换
INSERT OR REPLACE INTO users (id, name, email) VALUES (1, '张三', '[email protected]');

SQL Server: MERGE​

MERGE INTO users AS target
USING (SELECT 1 AS id, '张三' AS name, '[email protected]' AS email) AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email) VALUES (source.id, source.name, source.email);

从文件导入​

MySQL: LOAD DATA​

LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

PostgreSQL: COPY​

COPY users(name, email, age, city)
FROM '/path/to/users.csv'
DELIMITER ','
CSV HEADER;

安全考虑​

使用参数化查询​

防止 SQL 注入：

# Python 示例
cursor.execute(
    "INSERT INTO users (name, email) VALUES (%s, %s)",
    (name, email)
)
// Java 示例
PreparedStatement stmt = conn.prepareStatement(
    "INSERT INTO users (name, email) VALUES (?, ?)"
);
stmt.setString(1, name);
stmt.setString(2, email);
stmt.executeUpdate();

验证输入数据​

-- 使用约束限制数据
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(200) NOT NULL UNIQUE,
    age INT CHECK (age >= 0 AND age <= 150)
);

性能优化​

批量插入​

-- 好的做法：批量插入
INSERT INTO users (name, email) VALUES
    ('张三', '[email protected]'),
    ('李四', '[email protected]'),
    ('王五', '[email protected]');

-- 避免：循环单条插入
-- INSERT INTO users (name, email) VALUES ('张三', '[email protected]');
-- INSERT INTO users (name, email) VALUES ('李四', '[email protected]');
-- INSERT INTO users (name, email) VALUES ('王五', '[email protected]');

禁用索引（大数据量导入）​

-- MySQL
ALTER TABLE users DISABLE KEYS;
-- 执行大量插入
ALTER TABLE users ENABLE KEYS;

-- 或者删除索引后再重建
DROP INDEX idx_email ON users;
-- 执行插入
CREATE INDEX idx_email ON users(email);

事务控制​

-- 大批量插入使用事务
START TRANSACTION;
INSERT INTO users (name, email) VALUES ...;
INSERT INTO users (name, email) VALUES ...;
-- 更多插入语句
COMMIT;

小结​

本章我们学习了：

  1. 基本 INSERT 语法
  2. 插入多行数据
  3. INSERT ... SELECT 查询插入
  4. 插入默认值
  5. 主键处理
  6. 冲突处理（ON DUPLICATE KEY、ON CONFLICT）
  7. 从文件导入
  8. 安全考虑和性能优化

练习​

  1. 向 users 表插入一条新用户记录
  2. 批量插入 5 条产品记录
  3. 将价格低于 100 的产品复制到 cheap_products 表
  4. 使用 ON DUPLICATE KEY UPDATE 实现用户信息更新
  5. 从 CSV 文件导入数据到数据库