MySQL Insert row, on duplicate: add suffix and re-insert

duplicates, insert, mysql, php, slug

Solution

You could create a trigger that changes the value of NEW.slug before inserting it.

drop trigger if exists changeSlug;

delimiter |

CREATE TRIGGER changeSlug BEFORE INSERT ON slugs
  FOR EACH ROW BEGIN
     declare original_slug varchar(255);
     declare slug_counter int;
     set original_slug = new.slug;
     set slug_counter = 1;
     while exists (select true from slugs where slug = new.slug) do
        set new.slug = concat(original_slug, '-', slug_counter); 
        set slug_counter = slug_counter + 1;
     end while;

  END;
|
delimiter ;

And this would be the results

mysql> insert into slugs values ("dude");
Query OK, 1 row affected (0.00 sec)

mysql> insert into slugs values ("dude");
Query OK, 1 row affected (0.00 sec)

mysql> insert into slugs values ("dude");
Query OK, 1 row affected (0.00 sec)

mysql> select * from slugs;
+--------+
| slug   |
+--------+
| dude   |
| dude-1 |
| dude-2 |
+--------+
3 rows in set (0.00 sec)

Problem

My question is rather complex, but I thought I should give it a try. In short, I want to insert a row with a `slug` (short string with alphas and a dash: this-is-a-slug). The problem is that `slug` is a unique key and there might be duplicates. When there is a duplicate it should be inserted with a modified `slug`, like with a suffix: this-is-a-slug-1, if that fails increase the suffix: this-is-a-slug-2. Here's the tricky part, it should be accomplished in MySQL (no PHP involved) and preferably in a INSERT statement (no variables, procedures etc.) I've tried a simple solution like so: ``` INSERT INTO table (slug) VALUES(IF((SELECT COUNT(slug) FROM table WHERE slug = 'this-is-a-slug') > 0, 'this-is-a-slug-1', 'this-is-a-slug'); ``` This should insert `this-is-a-slug-1` if `this-is-a-slug` exists, or `this-is-a-slug` otherwise. Expectedly, however, this spawns an error telling me that I cannot you a `FROM` statement in an `UPDATE` statement, or something like that. That's the problem, hope anyone can say something about it. P.S.: This is being used in a really heave RSS news update procedure, in which I can easily check the slug in the db with php and then modify it, but that doubles the time of my script :|, so I thought I could make it hard for mysql rather than php.

Original source