mysql中INSERT INTO语句插入数据的常见方式

mysql批量插入有四种方式：1. insert into values适用于小量已知数据；2. insert into select用于跨表导入；3. insert ignore/on duplicate key update处理重复键；4. load data infile最快，适合百万级本地文件导入。

INSERT INTO VALUES 一次性插入单行或多行数据

最基础的写法，适合已知全部字段值、数据量不大的场景。注意字段顺序必须和表定义一致，或显式指定列名避免出错。

常见错误：列数和值数量不匹配，报错 Column count doesn't match value count；插入 NULL 到 NOT NULL 字段导致失败。

• 推荐始终显式列出列名，尤其当表结构后续可能增加字段时
• 多行插入比多次单行 INSERT 更快，MySQL 会合并为一个事务批次处理
• 单次插入行数不宜过多（如超过 1000 行），否则可能触发 max_allowed_packet 限制

INSERT INTO users (name, email, created_at) 
VALUES 
('Alice', 'alice@example.com', NOW()),
('Bob', 'bob@example.com', NOW()),
('Charlie', 'charlie@example.com', NOW());

INSERT INTO SELECT 从其他表批量导入数据

适用于迁移、归档、统计汇总等场景，本质是把 SELECT 查询结果作为数据源插入目标表。

关键点：目标表字段类型、长度、约束需兼容查询结果；若目标表有自增主键，SELECT 中通常不提供该列（除非显式设为 NULL 或具体值并关闭 sql_mode=NO_AUTO_VALUE_ON_ZERO）。

VIVA

一个免费的AI创意视觉设计平台

下载

• 不会触发源表的触发器，但会触发目标表的 INSERT 触发器
• 执行期间会对源表加读锁（InnoDB 下通常是快照读，影响较小；MyISAM 则可能阻塞写）
• 可配合 WHERE、JOIN、聚合函数使用，但要注意目标列数与 SELECT 列数严格一致

INSERT INTO user_archive (id, name, email, archived_at)
SELECT id, name, email, NOW() 
FROM users 
WHERE last_login < DATE_SUB(NOW(), INTERVAL 2 YEAR);

INSERT IGNORE 和 ON DUPLICATE KEY UPDATE 处理重复键冲突

当插入数据可能违反 UNIQUE 约束或主键时，两者策略不同：INSERT IGNORE 直接跳过冲突行；ON DUPLICATE KEY UPDATE 则更新已有记录。

容易忽略的坑：IGNORE 会静默吞掉所有警告（包括非主键冲突的其他问题），不利于排查；UPDATE 子句中不能引用被更新行的旧值做计算（如 count = count + 1 是合法的，但 updated_at = old.updated_at 不行）。

• INSERT IGNORE 返回的 affected_rows 为 0 表示跳过，1 表示插入成功，2 表示“本应插入但因重复被忽略”（仅部分 MySQL 版本）
• ON DUPLICATE KEY UPDATE 只响应定义了 UNIQUE 或 PRIMARY KEY 的列冲突，普通索引无效
• 若表有多个唯一键，任一触发都会执行 UPDATE，需确认业务逻辑是否预期如此

INSERT INTO stats (date, page, views) 
VALUES ('2024-06-01', '/home', 120)
ON DUPLICATE KEY UPDATE views = views + 1;

使用 LOAD DATA INFILE 批量导入大文件数据

这是 MySQL 原生最快的批量插入方式，适合从 CSV、TSV 等文本文件导入数十万行以上数据。比拼接 SQL 插入快 20 倍以上。

权限和路径限制最多：需要 FILE 权限；文件必须位于数据库服务器本地（不是客户端）；默认只允许 /var/lib/mysql-files/ 等白名单目录（由 secure_file_priv 控制）。

• 字段分隔符、行结束符、转义字符需与文件格式严格匹配，否则解析错位
• 建议先用 SHOW VARIABLES LIKE 'secure_file_priv' 查看允许路径
• 导入前确保目标表无外键约束或临时禁用（SET FOREIGN_KEY_CHECKS = 0），否则速度骤降

LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, email, @dummy_created_at)
SET created_at = STR_TO_DATE(@dummy_created_at, '%Y-%m-%d %H:%i:%s');