Unique Slug

gistfile1.txt

DROP FUNCTION IF EXISTS UPDATE_SLUG;
DELIMITER |
CREATE FUNCTION UPDATE_SLUG(old_slug TEXT)
  RETURNS TEXT
    BEGIN
      DECLARE slug_maxlength INT DEFAULT 64;
      DECLARE cut_length INT DEFAULT 4;
      DECLARE new_slug TEXT;
      DECLARE count_duplicate INT;
      DECLARE slugcount INT;
      DECLARE slug_part_maxlength INT;
      DECLARE slug_part TEXT;
      DECLARE count_duplicate_2 INT;
 
      SELECT COUNT(*) INTO count_duplicate FROM content WHERE slug=old_slug;
      IF (count_duplicate > 1) THEN
        SET slugcount = count_duplicate - 1;
        SET slug_part_maxlength = slug_maxlength - cut_length;
        SET slug_part = SUBSTRING(old_slug, 1, slug_part_maxlength);
        SET new_slug = CONCAT(slug_part,'-', slugcount);
        SELECT COUNT(*) INTO count_duplicate_2 FROM content WHERE slug=new_slug LIMIT 1;
        WHILE count_duplicate_2 > 0 DO
          SET slugcount = slugcount + 1;
          SET new_slug = CONCAT(slug_part,'-', slugcount);
          SELECT COUNT(*) INTO count_duplicate_2 FROM content WHERE slug=new_slug LIMIT 1;
        END WHILE;
 
      ELSE
        SET new_slug = old_slug;
      END IF;
      RETURN new_slug;
  END |
DELIMITER ;