Saltar al contenido

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.
example.com/schools/new-york-art-school

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.


ALTER TABLE schools ADD slug VARCHAR(128) NULL;

Then, generate slugs using school names.

UPDATE schools SET slug = replace(trim(lower(name)), ' ', '-');

Use the following to double-check the slug has only alphabets, numbers or dashes.

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.

UPDATE schools SET
slug = lower(name),
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.

ALTER TABLE schools ADD UNIQUE (slug);

FUENTE: http://theoryapp.com/generate-slug-url-in-mysql/

Si te ha interesado este artículo y deseas un apoyo o asesoría en algún requerimiento, envíame un mensaje a: (info@juliopari.com) o sino a través de Linkedin: https://www.linkedin.com/in/juliopari/