Skip to content

Instantly share code, notes, and snippets.

@fabeat
Created May 4, 2010 11:02
Show Gist options
  • Save fabeat/389271 to your computer and use it in GitHub Desktop.
Save fabeat/389271 to your computer and use it in GitHub Desktop.
MYSQL SLUG FIX (Unique fix)
MYSQL SLUG FIX
The table 'content' contains pages with a slug. This slug should be unique but it isn't.
unique_fix.sql fixes this problem and makes the slug-column unique.
CREATE TABLE content (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
title VARCHAR( 255 ) NOT NULL ,
slug VARCHAR( 64 ) NOT NULL ,
content TEXT NOT NULL
);
USE database_name;
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 ;
UPDATE content SET slug = UPDATE_SLUG(slug) ORDER BY id DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment