MySQL 8.0 - Unexpected Behavior with INSERT IGNORE and Unique Indexes in a Batch Insert
Could someone explain Hey everyone, I'm running into an issue that's driving me crazy... I'm encountering an issue with MySQL 8.0 where using `INSERT IGNORE` in a batch insert does not behave as I expected when there are unique indexes involved. Specifically, I have a table `users` defined as follows: ```sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100) ); ``` I attempted to insert multiple records at once: ```sql INSERT IGNORE INTO users (email, name) VALUES ('alice@example.com', 'Alice'), ('bob@example.com', 'Bob'), ('alice@example.com', 'Alice Duplicate'); ``` I expected that the second entry for 'alice@example.com' would simply be ignored due to the unique constraint. However, after running the above statement, the result shows that only the first two records were inserted, and the second attempt for 'alice@example.com' did not generate any errors or warnings. I verified this with: ```sql SELECT * FROM users; ``` The output was: ``` +----+---------------------+-------+ | id | email | name | +----+---------------------+-------+ | 1 | alice@example.com | Alice | | 2 | bob@example.com | Bob | +----+---------------------+-------+ ``` I would have expected a warning or some indication that the duplicate email was detected, but there was nothing. I tried checking the results of `SHOW WARNINGS`, but it returned an empty set. I also experimented with `INSERT...ON DUPLICATE KEY UPDATE` to see if I could capture duplicates, but it still didn’t give me an output for the ignored records. Here’s what I tried: ```sql INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice'), ('bob@example.com', 'Bob') ON DUPLICATE KEY UPDATE name = VALUES(name); ``` This correctly updated the existing entry without errors, but I still want to understand why `INSERT IGNORE` did not notify me about the ignored record. Is there a way to see ignored records or better handle this situation? Am I missing some configuration or best practice here? Any guidance would be appreciated! I'm using Sql stable in this project. What would be the recommended way to handle this? I'm working in a Ubuntu 22.04 environment. I'd be grateful for any help.