Generate Slug URL in MySQL
A slug is a short name using human-readable keywords to identify a web page. For example, in the following URL
the slug is new-york-art-school. This post shows how to automatically generate slug using SQL in MySQL.
Suppose we have a table schools a field name, and we wish to generate a slug for each school using the name. First, add one field for the slug.
1 | ALTER TABLE schools ADD slug VARCHAR(128) NULL; |
Then, generate slugs using school names.
1 2 | UPDATE schools SET slug = replace(trim(lower(name)), ' ', '-'); |
Use the following to double-check the slug has only alphabets, numbers or dashes.
1 2 | SELECT * FROM schools WHERE slug NOT RLIKE '^([a-z0-9]+\-)*[a-z0-9]+$'; |
The following query helps to replace special characters like dots, quotes, etc. Sometimes its necessary to run multiple times to remove dashes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | UPDATE schools SET slug = lower(name), slug = replace(slug, '.', ' '), slug = replace(slug, ',', ' '), slug = replace(slug, ';', ' '), slug = replace(slug, ':', ' '), slug = replace(slug, '?', ' '), slug = replace(slug, '%', ' '), slug = replace(slug, '&', ' '), slug = replace(slug, '#', ' '), slug = replace(slug, '*', ' '), slug = replace(slug, '!', ' '), slug = replace(slug, '_', ' '), slug = replace(slug, '@', ' '), slug = replace(slug, '+', ' '), slug = replace(slug, '(', ' '), slug = replace(slug, ')', ' '), slug = replace(slug, '[', ' '), slug = replace(slug, ']', ' '), slug = replace(slug, '/', ' '), slug = replace(slug, '-', ' '), slug = replace(slug, '\'', ''), slug = trim(slug), slug = replace(slug, ' ', '-'), slug = replace(slug, '--', '-');UPDATE schools SET slug = replace(slug, '--', '-'); |
Finally, add unique key to the slug field.
1 | ALTER TABLE schools ADD UNIQUE (slug); |