Expresiones Regulares y Slugs en SQL y MySQL: Casos de Uso Reales

MySQL column generation is a powerful tool that allows you to transform and manipulate data efficiently. These columns can be virtual or stored, depending on our specific needs. Mastering this functionality allows us to create more intelligent and optimized databases for modern web applications.

How to create SEO-friendly slugs with MySQL?

When developing web applications, we often need to create SEO-friendly URLs. These URLs, known as "slugs", are usually derived from the title or name of a product, article or resource. Instead of using an external programming language to generate these slugs, we can take advantage of MySQL functions to create them directly in our database.

Let's take a look at our product table:

SELECT name, slug FROM products LIMIT 10;

The slug stored in the table converts spaces to dashes, changes uppercase to lowercase, and removes special characters. For example, "Heavy Duty Steel Computer" becomes "heavy-duty-steel-computer".

How to transform text with MySQL functions?

To generate our own slug, we need to apply several transformations to the text:

1. Convert to lowercase: we use the LOWER() function.

SELECT name, LOWER(name) AS generated FROM products LIMIT 10;

2. Replace spaces with hyphens: We can use regular expressions with REGEXP_REPLACE().

SELECT name, REGEXP_REPLACE(LOWER(name), ' ', '-') AS generated FROM products LIMIT 10;

3. Remove special characters: Replace everything that is not alphanumeric in English

SELECT name, REGEXP_REPLACE( REGEXP_REPLACE(name, '[^a-z[:space:]]', '_'), '[: space:]', '-' ) AS generatedFROM products LIMIT 10;

This regular expression replaces any character that is not between a-z and is not a space with an underscore, and then replaces spaces with dashes.

Virtual or stored column?

Once we have our expression for generating slugs, we must decide whether to implement it as a virtual or stored column:

• Virtual column: it is calculated each time it is queried.
• Stored column: It is calculated and stored when the record is inserted or updated.

To decide, we must consider the frequency of data change. In the case of slugs, they only change when the product name changes, so a stored column is more efficient:

ALTER TABLE productsADD COLUMN slug_generated VARCHAR(100)AS ( LOWER( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE(name, '[^a-z[:space:]]', '_'), '[: space:]', '-' ) ))STORED AFTER slug;

With this instruction, we create a new column that:

• Is automatically generated based on the product name
• Is physically stored in the table
• Is updated only when the product name changes

We can verify that it works correctly by updating a product name:

UPDATE products SET name = 'Hi, how are you?' WHERE product_id = 9;SELECT product_id, name, slug, slug_generated FROM products LIMIT 10;

We will see that slug_generated is automatically updated with the value "hello_how_are_you-".

What other text manipulation functions does MySQL provide?

MySQL provides a wide range of functions for manipulating text:

• LENGTH(): Gets the length of a string.
• SUBSTRING(): Extracts a part of a string
• CONCAT(): Combines two or more strings
• TRIM(): Removes leading and trailing spaces
• REPLACE(): Replaces all occurrences of a substring.

The power of these functions lies in their execution speed. As we could observe, processing 2,358 records took only 0.01 seconds, which demonstrates the efficiency of MySQL for these operations.

The generated columns allow us to maintain data consistency and avoid redundant calculations in our application. Moreover, since they are integrated into the database, they ensure that all clients accessing the data get the same processed results.

Mastering these data manipulation techniques directly in MySQL can significantly improve the performance and maintainability of our applications. Have you used generated columns in your projects? Share your experience in the comments.