MySQL使用INSERT IGNORE仍插入重复值的问题排查

我从TableA读取包含大量重复的数据，需要将唯一记录插入到空表TableB（userComms）。该表包含主键，以及由user_ID、cID、crID、clID、ceID组成的5列联合唯一索引，但使用INSERT IGNORE INTO userComms语句时，TableA中的重复记录仍被插入到TableB中。

TableB（userComms）的结构与索引 #

| userComms | CREATE TABLE `userComms` (
  `commsID` int(11) NOT NULL AUTO_INCREMENT,
  `user_ID` varchar(32) DEFAULT NULL,
  `commsType` int(2) DEFAULT NULL,
  `cID` int(11) DEFAULT NULL,
  `crID` int(11) DEFAULT NULL,
  `clID` int(11) DEFAULT NULL,
  `ceID` int(11) DEFAULT NULL,
  `push` tinyint(1) DEFAULT '1',
  `email` tinyint(1) DEFAULT '1',
  `text` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`commsID`),
  UNIQUE KEY `comm_index` (`user_ID`,`cID`,`crID`,`clID`,`ceID`)
) ENGINE=MyISAM AUTO_INCREMENT=71926 DEFAULT CHARSET=latin1 |

插入操作的PHP代码 #

foreach ($tableA as $rowA) {
  $query = "insert ignore into userComms (user_ID,commsType,cID,crID,clID,ceID,push,email,text) VALUES ($row['user_ID'],$row['commsType'],$row['clubID'],null,null,null,1,1,1)"
   ...
}

实际插入后的重复记录 #

执行查询：

select * from userComms where user_ID='20230723_190383173' and cID=1001 ;

返回结果：

+---------+--------------------+-----------+------+------+------+------+------+-------+------+
| commsID | user_ID            | commsType | cID  | crID | clID | ceID | push | email | text |
+---------+--------------------+-----------+------+------+------+------+------+-------+------+
|   63462 | 20230723_190383173 |         1 | 1001 | NULL | NULL | NULL |    1 |     1 |    1 |
|   71834 | 20230723_190383173 |         1 | 1001 | NULL | NULL | NULL |    1 |     1 |    1 |

问题原因 #

1. MySQL唯一索引对NULL值的处理逻辑 #

MySQL中，NULL值不被视为与任何值相等（包括其他NULL值）。你的联合唯一索引包含crID、clID、ceID三个允许为NULL的字段，当这些字段值为NULL时，MySQL会认为(user_ID, cID, NULL, NULL, NULL)的组合不是重复值，因此不会触发唯一约束，INSERT IGNORE也就无法过滤这类重复记录。

2. PHP代码的潜在错误 #

• 循环变量是$rowA，但SQL拼接时使用的是$row['user_ID']，属于变量名错误，可能导致插入值异常（从实际结果看值是正确的，大概率是笔误）。
• 字符串类型字段user_ID插入时未加单引号，会导致MySQL对字符串进行隐式类型转换，可能破坏索引匹配逻辑，甚至引发语法错误。

解决办法 #

方案1：处理NULL值，让唯一索引生效 #

将crID、clID、ceID的默认值设为非NULL的占位符（比如0），插入时用0代替NULL，确保联合索引的所有字段都有确定值，这样相同组合会被唯一索引识别为重复：

-- 修改表结构，设置默认值并禁止NULL
ALTER TABLE userComms 
MODIFY crID int(11) NOT NULL DEFAULT 0,
MODIFY clID int(11) NOT NULL DEFAULT 0,
MODIFY ceID int(11) NOT NULL DEFAULT 0;

-- 插入时用0代替NULL
INSERT IGNORE INTO userComms (user_ID, commsType, cID, crID, clID, ceID, push, email, text)
VALUES ('用户ID值', 1, 1001, 0, 0, 0, 1, 1, 1);

方案2：修正PHP代码的语法错误 #

确保变量名正确，且字符串字段用单引号包裹，避免隐式转换：

foreach ($tableA as $rowA) {
    // 对字符串字段添加单引号，修正变量名
    $query = "INSERT IGNORE INTO userComms (user_ID, commsType, cID, crID, clID, ceID, push, email, text) 
              VALUES ('{$rowA['user_ID']}', {$rowA['commsType']}, {$rowA['clubID']}, 0, 0, 0, 1, 1, 1)";
    // 执行查询逻辑...
}

方案3：改用INSERT ... ON DUPLICATE KEY UPDATE（需先解决NULL问题） #

如果需要在重复时更新其他字段（而非忽略），可以使用该语句，但前提是唯一索引能触发重复键冲突（即字段无NULL）：

INSERT INTO userComms (user_ID, commsType, cID, crID, clID, ceID, push, email, text)
VALUES ('用户ID值', 1, 1001, 0, 0, 0, 1, 1, 1)
ON DUPLICATE KEY UPDATE push=VALUES(push), email=VALUES(email), text=VALUES(text);

内容的提问来源于stack exchange，提问作者rolinger