MySQL 8.0: implementing INSERT IGNORE optimization guide as expected with unique constraints
I'm working with a puzzling scenario while using `INSERT IGNORE` in MySQL 8.0... I have a table called `users` structured to ensure the `email` column is unique. The relevant part of my table definition looks like this: ```sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE ); ``` When I try to insert a new user with an existing email using the `INSERT IGNORE` statement, it doesn't seem to ignore the insert as expected. Instead, it returns a result indicating that the row was inserted, but for existing emails, it shows a `0` in the affected rows count, which is confusing. Here’s the code I used: ```sql INSERT IGNORE INTO users (name, email) VALUES ('John Doe', 'john@example.com'); ``` Initially, I expected that if the email already exists, the statement would simply do nothing. However, it seems like `INSERT IGNORE` is not functioning correctly, or I might be misunderstanding its behavior. I have verified that the email indeed exists in the table, and yet the command still executes as if it were successful. To troubleshoot, I've tried using `INSERT ... ON DUPLICATE KEY UPDATE` as an alternative: ```sql INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com') ON DUPLICATE KEY UPDATE name = 'John Doe'; ``` This correctly updates the name when the email already exists, but I was hoping to avoid the update behavior and just ignore the insert altogether. I also checked for triggers or any conflicting settings that might affect this behavior, but nothing stood out. Does anyone know why `INSERT IGNORE` isn't functioning as I expected with the unique constraint and if there are any specific configurations or scenarios in MySQL 8.0 that could lead to such an scenario? For context: I'm using Sql on Windows. What am I doing wrong?