Automatic slug fields with MySQL triggers

Let's say you website that displays some user information. The data comes from a MySQL database table Persons.

All records are created/updated by a separate management application. The website show details for a specific user by using the ID parameter from query string: www.domain.com/person?id=1

This is a bit old-fashioned and not so SEO-friendly. A better way of implementing routing is using a slug with a pattern: www.domain.com/person/<firstname>-<lastname>. So when a user visits /person/john-doe the code should use 'john-doe' as lookup key which doesn't exist in the database (yet). This is called a Slug and requires to be added as a new field in the database, so that it can be quertied. We can be added it via SQL script:

alter table mydb.persons add Slug varchar(128) null;
alter table mydb.persons add unique (Slug);

Now we have a new field added to the table, but still empty. How to populate it for existing records and newly inserted records?

One way is to solve this is updating the management application when a person is updated/saved to include the slug in the query send to the database. And depending how much records there are, you can put a little effort to create an SQL script to update all existing record to populate the SLUG field.

But there is an alternative way. Since the Slug can be derived from the firstname and lastname we could go for database-only solution: MySQL database triggers!

We need a trigger for INSERTS and a trigger for UPDATES. Since we want them to use some logic for formatting the slug you can create a MySQL function and call the function in both triggers. Let's create the function mydb.generate_slug:

# Create MySQL Stored Function to generate SLUGS
DROP FUNCTION IF EXISTS mydb.generate_slug;

CREATE FUNCTION mydb.generate_slug(source varchar(128)) RETURNS varchar(128)
DETERMINISTIC
BEGIN
	SET source = lower(source);
	SET source = replace(source, '.', ' ');
	SET source = replace(source, '\'', '-');
	SET source = replace(source,'š','s');
	SET source = replace(source,'Ð','Dj');
	SET source = replace(source,'ΕΎ','z');
	SET source = replace(source,'Þ','B');
	SET source = replace(source,'ß','Ss');
	SET source = replace(source,'à','a');
	SET source = replace(source,'á','a');
	SET source = replace(source,'â','a');
	SET source = replace(source,'ã','a');
	SET source = replace(source,'ä','a');
	SET source = replace(source,'å','a');
	SET source = replace(source,'æ','a');
	SET source = replace(source,'ç','c');
	SET source = replace(source,'è','e');
	SET source = replace(source,'é','e');
	SET source = replace(source,'ê','e');
	SET source = replace(source,'ë','e');
	SET source = replace(source,'ì','i');
	SET source = replace(source,'í','i');
	SET source = replace(source,'î','i');
	SET source = replace(source,'ï','i');
	SET source = replace(source,'ð','o');
	SET source = replace(source,'ñ','n');
	SET source = replace(source,'ò','o');
	SET source = replace(source,'ó','o');
	SET source = replace(source,'ô','o');
	SET source = replace(source,'õ','o');
	SET source = replace(source,'ö','o');
	SET source = replace(source,'ø','o');
	SET source = replace(source,'ù','u');
	SET source = replace(source,'ú','u');
	SET source = replace(source,'û','u');
	SET source = replace(source,'ý','y');
	SET source = replace(source,'ý','y');
	SET source = replace(source,'þ','b');
	SET source = replace(source,'ÿ','y');
	SET source = replace(source,'ƒ','f');
	SET source = replace(source, 'œ', 'oe');
	SET source = replace(source, '€', 'euro');
	SET source = replace(source, '$', 'dollars');
	SET source = replace(source, '£', '');
	SET source = trim(source);
	SET source = replace(source, ' ', '-');
	SET source = replace(source, '--', '-');
	return source;
END

As you can see we filter out special characters, so it can be used as part of an URL pattern. See W3C specifitions for details.

Before we start implementing a trigger, we want to test drive this function first. The following script updates the slug field for every record by using the function with the FullName field as input:

update mydb.persons set slug = mydb.generate_slug(FullName);

Note: there is no check on input, since FullName is a mandatory non-empty field.

After running this you can verify the slug fields and double-check if the slug only consist of alphanumeric or dashes. The following script should not return any matches:

SELECT * FROM mydb.persons WHERE slug NOT RLIKE '^([a-z0-9]+\-)+[a-z0-9]+$' OR slug IS NULL OR slug = '';

And to visually compare and see the results you could run this:

SELECT Id, FullName, Slug FROM mydb.persons;

So now all records contain a auto generated slug and the function we created is working as a charm. Next stop, create the INSERT trigger for making sure when a new person is added it automatically gets a valid SLUG:

DROP TRIGGER IF EXISTS mydb.insert_slug;
CREATE TRIGGER mydb.insert_slug BEFORE INSERT ON mydb.persons FOR EACH ROW Set NEW.Slug = mydb.generate_slug(NEW.FullName);

And the UPDATE trigger for handling updates. When FullName changes, the Slug should be updated as well:

DROP TRIGGER IF EXISTS mydb.update_slug;
CREATE TRIGGER mydb.update_slug BEFORE UPDATE ON mydb.persons FOR EACH ROW Set NEW.Slug = mydb.generate_slug(NEW.FullName);

That's it! We now have the final solution:

  • Every person record has a slug field with correct value.
  • New or updated person records automatically receive their slug field values.
  • No code has to be updated for the management application.




 

 

 

Add comment