Columns generated in MySQL offer powerful and sophisticated functionality for handling computed data directly at the database level. This article dives into how to use these columns to optimize data storage and improve query performance, providing a detailed and practical overview of their implementation.
What Are Generated Columns?
Generated columns, also known as virtual columns or computed columns, are fields in a MySQL database table that, instead of storing data directly, generate their values by executing an expression that can include other columns from the same table. The main advantage is that the values in these columns are always up to date with respect to changes in the base data.
There are two types of columns generated in MySQL:
- Stored generated columns: These columns compute their value and physically store it in the table. As a result, they take up storage space, but their values are always ready to be read without the need for recomputation, which speeds up read operations.
- Virtual generated columns: Unlike stored ones, the values of these columns are not physically stored in the table. The values are recomputed each time they are accessed, which is useful for saving space when the values are queried less frequently.
Practical Use of Generated Columns
Defining Generated Columns in MySQL
Creating a generated column is a simple process. Here I show you how to define each type with practical examples. Suppose we have a table products with the columns price y amount.
CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, price DECIMAL(10,2), quantity INT, total_price DECIMAL(10,2) AS (price * quantity) STORED );In the previous example, total_price is a stored generated column that multiplies price by amount to calculate the total cost of the product.
If we prefer that total_price be a virtual column, we would modify the declaration as follows:
total_price DECIMAL(10,2) AS (price * quantity)Benefits of Using Generated Columns
Performance Optimization
Generated columns can significantly improve query performance especially in scenarios where repetitive calculations of derived data are required. For example, when generating reports or processing large volumes of analytical data.
Reducing Data Redundancy
Using generated columns helps avoid data redundancy. Instead of storing the same computed value in multiple places, we generate it dynamically, ensuring consistency and reducing data synchronization errors.
Query Simplification
Generated columns simplify query operations by hiding complex calculation logic behind the column definition. This makes queries cleaner and easier to understand.
Best Practices and Considerations
Indexing of Generated Columns
The generated columns can be indexed. This is particularly useful for stored generated columns, as it allows you to speed up queries that filter or sort based on these computed fields.
Updates and Maintenance
Although generated columns reduce the need for manual updating of derived data, it is crucial to understand how changes to the underlying fields affect these columns. For example, update the price in our table products will automatically update the total_price in stored columns, while in virtual columns, the new value is recalculated with each query.
Common Use Cases
- E-commerce: Automatic calculation of total prices, taxes, discounts, etc.
- Finance: Computation of balances, interests and other financial indicators automatically.
- Reports: Generation of computed fields that are frequently used in reports and control panels.
Conclusion
Columns generated in MySQL are an extremely useful tool for developers and database administrators, allowing for more efficient and maintainable database design. If you have questions or want to learn more about this topic, feel free to visit nelkodev.com or contact me directly through nelkodev.com/contact. Correctly using the generated columns will not only facilitate your daily work, but will also enhance your applications with smarter and more efficient data management.
English 
Spanish