INSERT 用于向 MySQL 数据表中写入新记录,是最基础也最常用的数据操作语句。除了单行插入,MySQL 8 还支持批量插入、使用 SET 赋值、从查询结果写入,以及在唯一键冲突时执行更新。正确指定字段、使用参数绑定并处理事务,可以避免字段错位、SQL 注入和不完整数据。
一、准备示例数据表
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(120) NOT NULL,
age TINYINT UNSIGNED DEFAULT NULL,
status TINYINT UNSIGNED NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_username (username),
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
其中 id 自动增长,status 和时间字段具有默认值。插入数据时,可以只提供业务必需的字段。
二、INSERT 的基本语法
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
向用户表插入一条记录:
INSERT INTO users (username, email, age)
VALUES ('zhangsan', 'zhangsan@example.com', 28);
字段列表与 VALUES 中的值必须按照位置一一对应。没有提供的字段会使用默认值、自动生成值或 NULL;如果必填字段既没有赋值也没有默认值,插入会失败。
三、为什么建议明确写出字段名
MySQL 允许省略字段列表,但此时必须按照表中全部字段的物理顺序提供值:
-- 不推荐
INSERT INTO users
VALUES (NULL, 'lisi', 'lisi@example.com', 25, 1, NOW(), NOW());
这种写法依赖表结构顺序,一旦新增字段或调整字段,SQL 就可能报错或把数据写错位置。推荐始终明确列出需要写入的字段:
INSERT INTO users (username, email, age)
VALUES ('lisi', 'lisi@example.com', 25);
四、使用默认值和 NULL
1. 省略具有默认值的字段
INSERT INTO users (username, email)
VALUES ('wangwu', 'wangwu@example.com');
此时 status 使用默认值 1,created_at 和 updated_at 使用当前时间,age 为 NULL。
2. 使用 DEFAULT 关键字
INSERT INTO users (username, email, status)
VALUES ('zhaoliu', 'zhaoliu@example.com', DEFAULT);
3. 显式写入 NULL
INSERT INTO users (username, email, age)
VALUES ('sunqi', 'sunqi@example.com', NULL);
只有允许 NULL 的字段才能显式写入 NULL。NULL 表示值未知或不存在,它与空字符串 ''、数字 0 含义不同。
五、插入多条数据
一条 INSERT 可以包含多组 VALUES:
INSERT INTO users (username, email, age)
VALUES
('user01', 'user01@example.com', 20),
('user02', 'user02@example.com', 22),
('user03', 'user03@example.com', NULL);
批量插入通常比逐条执行 INSERT 更高效,因为可以减少网络往返、SQL 解析和事务提交次数。但单批数据也不应无限增大,需要考虑 max_allowed_packet、事务日志、锁持有时间和失败重试成本。实际项目可以根据每行大小分批写入。
六、使用 INSERT ... SET
MySQL 还支持使用 SET 形式插入单条记录:
INSERT INTO users
SET username = 'zhouba',
email = 'zhouba@example.com',
age = 30;
这种写法字段和值紧邻,可读性较好,但属于 MySQL 特有语法,并且不适合批量插入。如果需要兼容其他数据库,优先使用标准的 INSERT ... VALUES。
七、从查询结果插入数据
INSERT ... SELECT 可以把查询结果写入另一张表,常用于归档、迁移和汇总。
CREATE TABLE disabled_users LIKE users;
INSERT INTO disabled_users (id, username, email, age, status, created_at, updated_at)
SELECT id, username, email, age, status, created_at, updated_at
FROM users
WHERE status = 0;
目标字段数量、顺序和类型必须与 SELECT 结果兼容。执行前应先单独运行 SELECT,确认范围和数据量,避免误写大量数据。
八、获取新记录的自增 ID
INSERT INTO users (username, email)
VALUES ('wushi', 'wushi@example.com');
SELECT LAST_INSERT_ID();
LAST_INSERT_ID() 返回当前连接最近生成的自增值,不会被其他连接的并发插入覆盖。应用程序中应使用数据库驱动提供的相应方法,例如 PDO 的 lastInsertId(),不要使用 SELECT MAX(id)。
九、唯一键冲突如何处理
1. 默认行为
username 或 email 已存在时,普通 INSERT 会报 Duplicate entry 错误。默认报错通常是最安全的行为,因为应用程序能够明确知道数据没有写入。
2. INSERT IGNORE
INSERT IGNORE INTO users (username, email)
VALUES ('zhangsan', 'another@example.com');
INSERT IGNORE 会把某些错误降级为警告,冲突记录可能被跳过。它容易隐藏数据质量问题,使用后应检查受影响行数和警告:
SHOW WARNINGS;
3. ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email, age)
VALUES ('zhangsan', 'zhangsan@example.com', 29) AS new
ON DUPLICATE KEY UPDATE
age = new.age,
updated_at = CURRENT_TIMESTAMP;
当主键或唯一索引冲突时,该语句会更新已有记录。使用前必须明确哪个唯一键可能触发冲突,否则同一条 SQL 可能更新到与预期不同的记录。MySQL 8 新版本推荐使用行别名引用待插入值,避免依赖已经弃用的 VALUES(column) 写法。
十、REPLACE INTO 与 INSERT 的区别
REPLACE INTO users (id, username, email)
VALUES (1, 'new_name', 'new@example.com');
REPLACE 发生唯一键冲突时,本质上可能先删除旧记录,再插入新记录,而不是普通 UPDATE。这可能改变自增值、触发删除和插入相关行为,并影响外键引用。一般业务更新应优先使用 UPDATE 或经过审慎设计的 ON DUPLICATE KEY UPDATE。
十一、在程序中使用参数绑定
不要把用户输入直接拼接进 SQL。PHP PDO 可以使用预处理和参数绑定:
$sql = 'INSERT INTO users (username, email, age)
VALUES (:username, :email, :age)';
$stmt = $pdo->prepare($sql);
$stmt->execute([
'username' => $username,
'email' => $email,
'age' => $age,
]);
$userId = (int) $pdo->lastInsertId();
参数绑定可以避免 SQL 注入和引号转义错误,但业务层仍然需要验证用户名长度、邮箱格式和年龄范围。
十二、使用事务保证多表写入一致性
注册用户时如果还要创建资料记录,可以把多条写操作放在同一事务中:
START TRANSACTION;
INSERT INTO users (username, email)
VALUES ('new_user', 'new_user@example.com');
SET @user_id = LAST_INSERT_ID();
INSERT INTO user_profiles (user_id, nickname)
VALUES (@user_id, '新用户');
COMMIT;
任何一步失败时应执行 ROLLBACK。应用程序需要捕获异常,并确保提交或回滚发生在同一个数据库连接上。
十三、常见错误及排查方法
1. Column count doesn't match value count
字段数量和 VALUES 数量不一致。检查每一组 VALUES 是否都与字段列表严格对应。
2. Field doesn't have a default value
遗漏了 NOT NULL 且没有默认值的必填字段。应提供合法值,而不是为了绕过错误随意增加不合理的默认值。
3. Duplicate entry
主键或唯一索引冲突。使用 SHOW INDEX FROM users 确认唯一约束,并判断业务应该报错、更新还是跳过。
4. Data too long for column
写入字符串超过字段长度。应验证输入并检查字段设计,避免关闭严格模式后让数据被静默截断。
5. Incorrect integer/date value
数据类型或格式不正确。例如把空字符串写入整数列,或写入无效日期。应用层应使用正确类型,并保留 MySQL 严格模式。
十四、插入性能优化建议
- 大量数据优先使用合理大小的批量 INSERT,而不是逐条提交。
- 把同一批相关写入放进事务,减少频繁提交开销。
- 只写入需要的字段,不要为默认值重复传参。
- 避免建立无业务价值的重复索引,每次写入都需要维护相关索引。
- 超大规模导入可评估
LOAD DATA,并先在测试环境验证。 - 关注慢日志、锁等待、事务大小和磁盘写入能力。
十五、推荐的 INSERT 编写方式
INSERT INTO users (username, email, age)
VALUES (?, ?, ?);
实际项目中,建议明确字段列表、使用参数绑定、让数据库处理默认值,并根据业务要求决定是否使用事务。唯一键冲突应显式处理,不要用 INSERT IGNORE 掩盖未知错误。
十六、总结
MySQL 8 的 INSERT 不仅能插入一条记录,还能完成批量写入、查询结果迁移和冲突更新。基础写法虽然简单,但字段顺序、默认值、唯一约束、事务与安全性都会影响最终结果。养成明确字段、绑定参数、检查受影响行数和正确处理异常的习惯,才能让数据写入稳定可靠。