#sql

Posts on x2q tagged sql. Updated as new posts are published.

• Jun 9, 2007

  MySQL: find and remove duplicate rows (2026)

  The old ALTER IGNORE TABLE … ADD UNIQUE trick was removed in MySQL 5.7. The portable way to dedupe now is a self-join DELETE keeping the lowest id; MySQL 8 / MariaDB 10.2+ add a cleaner window-function version. Then add a UNIQUE index so duplicates can't come back.

• Aug 31, 2006

  MySQL: set or reset the AUTO_INCREMENT value (2026)

  ALTER TABLE … AUTO_INCREMENT = N sets the next id a table will hand out. It won't go below the current max id, behaves differently across storage engines on restart, and interacts with auto_increment_increment on replicated/Galera setups. Here's the full picture.