技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8 INSERT 插入数据详解:单行、批量与查询结果写入

MySQL8专栏272 次阅读

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 不仅能插入一条记录,还能完成批量写入、查询结果迁移和冲突更新。基础写法虽然简单,但字段顺序、默认值、唯一约束、事务与安全性都会影响最终结果。养成明确字段、绑定参数、检查受影响行数和正确处理异常的习惯,才能让数据写入稳定可靠。

天天晨练不仅为了健身,也为了体现生命的倔强

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读