Prevent auto increment on MySQL duplicate insert

by

You could modify your INSERT to be something like this:

INSERT INTO tablename (tag)
SELECT $tag
FROM tablename
WHERE NOT EXISTS(
    SELECT tag
    FROM tablename
    WHERE tag = $tag
)
LIMIT 1

Where $tag is the tag (properly quoted or as a placeholder of course) that you want to add if it isn’t already there. This approach won’t even trigger an INSERT (and the subsequent autoincrement wastage) if the tag is already there. You could probably come up with nicer SQL than that but the above should do the trick.

If your table is properly indexed then the extra SELECT for the existence check will be fast and the database is going to have to perform that check anyway.

This approach won’t work for the first tag though. You could seed your tag table with a tag that you think will always end up being used or you could do a separate check for an empty table.

More Related Contents:

  1. How to set initial value and auto increment in MySQL?
  2. Get the new record primary key ID from MySQL insert query?
  3. MySQL AUTO_INCREMENT does not ROLLBACK
  4. How to insert data to MySQL with auto-incremented column(field)?
  5. Why does MySQL autoincrement increase on failed inserts?
  6. Defining Composite Key with Auto Increment in MySQL
  7. “Unknown column in ‘field list'”, but column does exist
  8. Remove duplicate rows in MySQL
  9. MySQL foreign key constraints, cascade delete
  10. How do I see all foreign keys to a table or column?
  11. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  12. Howto: Clean a mysql InnoDB storage engine?
  13. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  14. MySql Table Insert if not exist otherwise update
  15. MySQL InnoDB foreign key between different databases
  16. BULK INSERT in MYSQL
  17. What is MySQL’s default ON DELETE behavior?
  18. How do I repair an InnoDB table?
  19. How to enable INNODB in mysql
  20. MySQL Insert & Joins
  21. How to disable index in innodb
  22. How to add a where clause in a MySQL Insert statement?
  23. How to delete duplicate rows from a MySQL table
  24. How do I delete all the duplicate records in a MySQL table without temp tables
  25. MySQL delete duplicate records but keep latest
  26. How to regex in a MySQL query
  27. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  28. How to remove duplicate entries from a mysql db?
  29. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  30. Incorrect integer value: ” for column ‘id’ at row 1

Leave a Comment