To prevent duplicates in MySQL tables, use constraints and special INSERT syntax. These methods ensure data integrity at the database level. tutorialspoint
Unique Constraints
Define UNIQUE or PRIMARY KEY on columns (or combinations) during table creation or alteration. This blocks inserts/updates that would create duplicates. stackoverflow
Example:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) UNIQUE,
name VARCHAR(100)
);
-- Or add later:
ALTER TABLE users ADD UNIQUE KEY unique_email (email);Attempting INSERT INTO users (email, name) VALUES ('test@example.com', 'Test'); twice fails with a duplicate key error. stackoverflow
INSERT IGNORE
Ignores duplicate rows silently without error. Requires a UNIQUE/PRIMARY KEY. tencentcloud
Example:
INSERT IGNORE INTO users (email, name) VALUES ('test@example.com', 'Test');First insert succeeds; second skips the duplicate. stackoverflow
ON DUPLICATE KEY UPDATE
Upserts: inserts if new, updates if duplicate. Requires UNIQUE/PRIMARY KEY. stackoverflow
Example:
INSERT INTO users (email, name) VALUES ('test@example.com', 'Updated Name')
ON DUPLICATE KEY UPDATE name = VALUES(name);Updates the existing row’s name on conflict. oneuptime
Application Tips
- Always combine with UNIQUE indexes for enforcement. tutorialspoint
- For bulk inserts, these scale better than app-level checks. reddit
- Test in transactions:
START TRANSACTION; ... COMMIT;. stackoverflow