Impact of UNIQUE Indexes in MySQL and Strategies to Avoid Duplicate Key Errors

This article demonstrates how to create a MySQL table with a UNIQUE index on the name column and shows that the index enforces uniqueness while still allowing NULL values.

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

A UNIQUE index can be added later:

ALTER TABLE sc ADD UNIQUE (name);
ALTER TABLE sc ADD UNIQUE KEY `name_score` (`name`,`score`);

-- To drop the unique index:
ALTER TABLE sc DROP INDEX `name`;

Inserting rows shows that duplicate values for the unique column cause an error and also advance the AUTO_INCREMENT counter:

INSERT INTO sc (name,class,score) VALUES ('吕布','一年二班',67);
INSERT INTO sc (name,class,score) VALUES ('赵云','一年二班',90);
INSERT INTO sc (name,class,score) VALUES ('典韦','一年二班',89);
INSERT INTO sc (name,class,score) VALUES ('关羽','一年二班',70);

-- Attempting a duplicate insert:
INSERT INTO sc (name,class,score) VALUES ('吕布','二年二班',77);
-- Error 1062: Duplicate entry '吕布' for key 'name'

Three common strategies to handle duplicate‑key situations are described:

INSERT IGNORE skips rows that would violate a UNIQUE or PRIMARY KEY constraint, leaving the auto‑increment value increased but not raising an error.

REPLACE INTO deletes the existing row that conflicts with the UNIQUE key and inserts the new row, which also changes the auto‑incremented id .

INSERT … ON DUPLICATE KEY UPDATE updates the existing row when a duplicate key is detected; the id stays the same, but the auto‑increment counter still increments.

INSERT IGNORE INTO sc (name,class,score) VALUES ('吕布','二年二班',77);

REPLACE INTO sc (name,class,score) VALUES ('吕布','二年二班',77);

INSERT INTO sc (name,class,score) VALUES ('关羽','二年二班',80) ON DUPLICATE KEY UPDATE score=100;

The article also notes that INSERT … ON DUPLICATE KEY UPDATE can cause deadlocks in InnoDB because the engine first acquires a shared lock on the existing row, then upgrades to an exclusive lock for the update.

To avoid deadlocks, it is recommended to limit the use of this statement on tables with multiple UNIQUE keys and to avoid concurrent transactions that insert identical data.

In conclusion, all three methods prevent insertion failures caused by duplicate UNIQUE or PRIMARY KEY values; INSERT IGNORE simply skips duplicates, while REPLACE INTO and INSERT … ON DUPLICATE KEY UPDATE replace existing rows, with the latter preserving the original id .