MySQL INSERT IGNORE 导入实验：跳过、截断与警告不能混算

导入任务加上 INSERT IGNORE 后不再报错，业务却发现商品名被截短、数量变成上限，还有几行完全没进表。IGNORE 的含义不是“只忽略重复键”，它会改变多类错误的处理方式。

这类问题最危险的地方，是任务可以顺利结束。没有普通异常，不代表数据按原样保存；受影响行数也不能告诉你哪些行被修正、哪些行被跳过。导入验收必须回到警告和实际落库值。

用一批数据同时制造三类结果

实验固定 MySQL 8.4、InnoDB。在独立库里明确设置严格模式，结束后再恢复当前会话设置，避免把不同模式的结果混在一起。

CREATE DATABASE ignore_import_lab;
USE ignore_import_lab;
SET @original_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

CREATE TABLE imported_product (
    id BIGINT NOT NULL PRIMARY KEY,
    product_code VARCHAR(8) NOT NULL,
    title VARCHAR(8) NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL,
    UNIQUE KEY uk_product_code (product_code)
) ENGINE=InnoDB;
INSERT INTO imported_product VALUES (1, 'SKU-01', 'cup', 2);

先单独试一条普通严格写入，验证过长文本会报错：

-- 预期失败：title超过8个字符。
INSERT INTO imported_product
VALUES (30, 'SKU-30', 'very-long-title', 1);

再提交带 IGNORE 的混合批次：

INSERT IGNORE INTO imported_product VALUES
    (2, 'SKU-01', 'duplicate', 1),
    (3, 'SKU-03', '12345678901', 2),
    (4, 'SKU-04', 'book', 70000),
    (5, 'SKU-05', NULL, 1);
SHOW WARNINGS;

必须在同一连接、紧邻语句读取警告，不要先归还连接池。示例分别包含重复业务键、过长文本、无符号小整数溢出，以及非空文本的 NULL。

查询实际值：

SELECT id, product_code, title, CHAR_LENGTH(title) AS title_length,
       quantity
FROM imported_product
ORDER BY id;

重复键那行会被跳过；某些不合法值则可能被调整后插入，例如文本截短、数量裁到类型范围、非空列使用隐式默认。实际警告与结果都要保存，不能因为语句返回成功就把输入文件整批标记为“原样导入”。

本例的 SMALLINT UNSIGNED 上限来自类型定义，不是某个测试平台的性能数字。业务最大数量若远低于类型上限，即便没有发生数据库溢出，数据也未必合法，这需要另一层明确规则。

“跳过”和“修正后写入”应该分开验收

重复业务键通常意味着这行没有新增。数值越界却可能留下一个不同于输入的新值。两者都产生警告时，任务若只累计“警告行数”，仍不知道最终数据是否正确。

同一行还可能同时触发多个问题。比如示例重复键行的 title 也过长，数据库处理顺序会影响能观察到哪些条件，不能把每条警告等同于一条独立坏记录。警告总数、异常输入数和跳过行数不是天然相等。

这也说明为什么只比较输入行数和受影响行数，不够做对账。必须能按稳定的行标识或业务键核对哪些对象存在、值是否一致。没有行级标识的导入文件，一旦批次里出现重复和转换，恢复对应关系会很困难。

若业务只想对重复请求做幂等，使用 IGNORE 时需要同时审视其他列：是否可能截断、转换或补默认值。它不是一个限定在唯一键错误上的局部开关。

警告读取本身也有范围

SHOW WARNINGS 查看的是当前会话相关诊断，不是整个数据库永久保存的导入错误表。换连接、执行会改变诊断的语句，或者超出服务器保存的条件数量，都可能让你拿不到原来的明细。

MySQL 的 max_error_count 影响可以保存并展示的诊断数量。一个非常大的批次可能有更多条件，但返回明细只保留一部分，因此不能依赖“SHOW WARNINGS 只看到十条”推导“只有十行异常”。

导入程序应立即读取驱动暴露的警告或受支持诊断信息，并将批次标识、源行标识和错误类别保存到自己的结果中。不要为了获得全部错误，把连接上的敏感原始数据无差别写入公共日志。

批次大小也由这个要求反向约束。越大的单语句不只影响事务与网络，还让错误定位、重试和对账更难。不能把“一个 INSERT 塞得下”当成最佳批次尺寸。

用严格装载，让问题停在输入边界

如果不能接受任何字段被自动修正，更清晰的选择通常是普通严格写入，加上显式输入校验和行级结果。重复业务键究竟是重复请求、合法合并还是冲突，应由业务规则分别处理。

对复杂文件，可以先把原始文本装进受控暂存表，保留原始行标识，再分类验证和转换。下面是本实验可用的最小暂存结构：

CREATE TABLE import_stage (
    batch_id BIGINT NOT NULL,
    source_line BIGINT NOT NULL,
    product_code_text VARCHAR(100) NULL,
    title_text VARCHAR(200) NULL,
    quantity_text VARCHAR(100) NULL,
    validation_error VARCHAR(200) NULL,
    PRIMARY KEY (batch_id, source_line)
) ENGINE=InnoDB;

INSERT INTO import_stage VALUES
    (101, 1, 'SKU-03', '12345678901', '2', NULL),
    (101, 2, 'SKU-04', 'book', '70000', NULL),
    (101, 3, 'SKU-06', 'pen', '3', NULL);

暂存字段故意比目标宽，也保留文本数量。它不是让错误无限进入系统，而是把“原始载荷”与“已经通过业务验证的目标值”分开，避免数据库先截短以后丢掉修正依据。

先标记容易确定的长度问题和空值：

UPDATE import_stage
SET validation_error = CASE
    WHEN product_code_text IS NULL OR product_code_text = ''
        THEN 'missing product code'
    WHEN CHAR_LENGTH(product_code_text) > 8 THEN 'product code too long'
    WHEN title_text IS NULL THEN 'missing title'
    WHEN CHAR_LENGTH(title_text) > 8 THEN 'title too long'
    ELSE NULL
END
WHERE batch_id = 101;

这只是结构检查的第一步，不应立刻把其余行全当合法。数量仍需按明确的整数语法、取值范围和业务上限验证，再转换；业务键是否已经存在也要单独核对。不能对任意文本先 CAST 成数字，再用转换后的零值猜原输入是否合法。

暂存表不是绕开最终约束的理由

两个导入任务可能同时通过“目标里还没有这个 SKU”的预检查，最终插入仍可能冲突。目标唯一键必须保留，导入程序应能把最终失败映射回源行，而不是为了跑完任务临时去掉唯一性。

同样，暂存验证通过到正式写入之间，目标状态可能改变。幂等操作要核对重复请求内容是否一致；合法合并要明确字段优先级；真正冲突则应保留可处理结果。这三种情况不适合都归成“忽略”。

如果一次业务要求整个批次原子成功，所有校验、写入与失败回滚应围绕这份事务要求设计。若允许部分成功，则必须公开哪几行成功、哪几行失败，以及再次提交如何避免重复。这是导入接口契约，不能交给一个 SQL 修饰词隐式决定。

把本例暂存批次走到正式写入

为让数量验证可重复，本例规定数量文本只接受一至五个十进制数字，不接受符号、小数或空文本；取值还必须落在目标类型范围内。实际业务若需要更严格的数量上限，应在这里继续收窄，不能因为字段能存六万就允许采购六万件。

UPDATE import_stage
SET validation_error = CASE
    WHEN quantity_text IS NULL THEN 'missing quantity'
    WHEN quantity_text NOT REGEXP '^[0-9]{1,5}$'
        THEN 'invalid integer syntax'
    WHEN CAST(quantity_text AS UNSIGNED) > 65535
        THEN 'quantity out of range'
    ELSE NULL
END
WHERE batch_id = 101 AND validation_error IS NULL;

UPDATE import_stage AS s
JOIN imported_product AS p ON p.product_code = s.product_code_text
SET s.validation_error = 'product code already exists'
WHERE s.batch_id = 101 AND s.validation_error IS NULL;

语法先验证，再在受控长度内转换，避免用 CAST 把任意无效输入变成可疑零值。本次样本最终只有源行三符合条件；前两行仍保留原始内容和错误原因。

目标表当前采用显式 id，所以演示为这个唯一合格样本分配一个明确编号，不把编号生成问题混进验证流程：

INSERT INTO imported_product(id, product_code, title, quantity)
SELECT 100, product_code_text, title_text,
       CAST(quantity_text AS UNSIGNED)
FROM import_stage
WHERE batch_id = 101 AND source_line = 3
  AND validation_error IS NULL;

SELECT * FROM imported_product WHERE id = 100;

这里的固定编号仅属于独立样本，正式批次应使用已定义的合法编号方案，并保持源行与目标对象的映射。再次执行最后一条 INSERT 会按普通唯一约束报冲突，不应静默宣称重复执行又新增成功。

如果需要全批合法行装载，应先解决批次内业务键重复、目标并发冲突和编号分配，再使用严格写入。暂存校验不能取代最终约束；它的价值是让失败能定位到原始行，而不是让所有错误都消失。

最后再次查询暂存结果，确认异常保留了原始内容：

SELECT source_line, product_code_text, title_text,
       quantity_text, validation_error
FROM import_stage WHERE batch_id = 101 ORDER BY source_line;
SET SESSION sql_mode = @original_sql_mode;

恢复了会话模式以后再归还连接。这个实验的有效结论不是“永远别用 IGNORE”，而是只在明确接受它对错误类别的处理时使用，并把跳过与调整后的写入都纳入验收。