{"id":29241,"date":"2024-04-15T01:58:03","date_gmt":"2024-04-15T00:58:03","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/dominando-las-columnas-generadas-en-mysql\/"},"modified":"2024-06-03T17:45:19","modified_gmt":"2024-06-03T16:45:19","slug":"dominando-las-columnas-generadas-en-mysql","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/","title":{"rendered":"Mastering Generated Columns in MySQL"},"content":{"rendered":"<p>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.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#%C2%BFQue_Son_las_Columnas_Generadas\" >What Are Generated Columns?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Uso_Practico_de_Columnas_Generadas\" >Practical Use of Generated Columns<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Definiendo_Columnas_Generadas_en_MySQL\" >Defining Generated Columns in MySQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Beneficios_de_Usar_Columnas_Generadas\" >Benefits of Using Generated Columns<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Optimizacion_de_Rendimiento\" >Performance Optimization<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Reduccion_de_la_Redundancia_de_Datos\" >Reducing Data Redundancy<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Simplificacion_de_Consultas\" >Query Simplification<\/a><\/li><\/ul><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Mejores_Practicas_y_Consideraciones\" >Best Practices and Considerations<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Indexacion_de_Columnas_Generadas\" >Indexing of Generated Columns<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Actualizaciones_y_Mantenimiento\" >Updates and Maintenance<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Casos_de_Uso_Comunes\" >Common Use Cases<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-generated-columns-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFQue_Son_las_Columnas_Generadas\"><\/span>What Are Generated Columns?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>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.<\/p>\n<p>There are two types of columns generated in MySQL:<\/p>\n<ol>\n<li><strong>Stored generated columns<\/strong>: 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.<\/li>\n<li><strong>Virtual generated columns<\/strong>: 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.<\/li>\n<\/ol>\n<h2><span class=\"ez-toc-section\" id=\"Uso_Practico_de_Columnas_Generadas\"><\/span>Practical Use of Generated Columns<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Definiendo_Columnas_Generadas_en_MySQL\"><\/span>Defining Generated Columns in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>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 <code>products<\/code> with the columns <code>price<\/code> y <code>amount<\/code>.<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, price DECIMAL(10,2), quantity INT, total_price DECIMAL(10,2) AS (price * quantity) STORED );<\/code><\/pre>\n<p>In the previous example, <code>total_price<\/code> is a stored generated column that multiplies <code>price<\/code> by <code>amount<\/code> to calculate the total cost of the product.<\/p>\n<p>If we prefer that <code>total_price<\/code> be a virtual column, we would modify the declaration as follows:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">    total_price DECIMAL(10,2) AS (price * quantity)<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Beneficios_de_Usar_Columnas_Generadas\"><\/span>Benefits of Using Generated Columns<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"Optimizacion_de_Rendimiento\"><\/span>Performance Optimization<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>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.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Reduccion_de_la_Redundancia_de_Datos\"><\/span>Reducing Data Redundancy<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>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.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Simplificacion_de_Consultas\"><\/span>Query Simplification<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Generated columns simplify query operations by hiding complex calculation logic behind the column definition. This makes queries cleaner and easier to understand.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Mejores_Practicas_y_Consideraciones\"><\/span>Best Practices and Considerations<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Indexacion_de_Columnas_Generadas\"><\/span>Indexing of Generated Columns<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>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.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Actualizaciones_y_Mantenimiento\"><\/span>Updates and Maintenance<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>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 <code>price<\/code> in our table <code>products<\/code> will automatically update the <code>total_price<\/code> in stored columns, while in virtual columns, the new value is recalculated with each query.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Casos_de_Uso_Comunes\"><\/span>Common Use Cases<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<ul>\n<li><strong>E-commerce<\/strong>: Automatic calculation of total prices, taxes, discounts, etc.<\/li>\n<li><strong>Finance<\/strong>: Computation of balances, interests and other financial indicators automatically.<\/li>\n<li><strong>Reports<\/strong>: Generation of computed fields that are frequently used in reports and control panels.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>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 <a href=\"https:\/\/nelkodev.com\/en\/\">nelkodev.com<\/a> or contact me directly through <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">nelkodev.com\/contact<\/a>. 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.<\/p>","protected":false},"excerpt":{"rendered":"<p>Generated columns in MySQL offer powerful and sophisticated functionality for handling computed data directly at the database level. This article delves 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 the [\u2026]<\/p>","protected":false},"author":1,"featured_media":29242,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2058],"tags":[205,599,2189,1289,2072,48,352,2186,2188,2187],"class_list":["post-29241","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-working-with-tables","tag-blog","tag-columnas","tag-curso","tag-dominando","tag-generadas","tag-las","tag-mysql","tag-tables","tag-with","tag-working"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29241","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=29241"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29241\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29242"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29241"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29241"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29241"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}