MySQL Insert row, duplicate: add suffix and reinsert

Question

My question is rather complicated, but I thought I should try.

In short, I want to insert a line with slug (short line with alpha and dash: this-is-a-slug). The problem is that slug is a unique key, and there may be duplicates.

If there is a duplicate, it should be inserted with the modified slug , for example, with the suffix: this-is-a-slug-1, if this does not increase the suffix: this-is-a-slug-2.

Here's the hard part, it needs to be done in MySQL (without using PHP) and preferably in an INSERT statement (without variables, procedures, etc.)

I tried such a simple solution:

 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.

It is expected, however, that this will throw an error telling me that I cannot execute the FROM statement on the UPDATE or something like that.

This is a problem, hope someone can say something about it.

PS: This is used in a really updated RSS news update procedure in which I can easily check the db pool with php and then change it, but it doubles the time of my script: |, so I thought I could make it difficult for mysql , not php.

Answer 1

You can 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 these will 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)