{"id":28979,"date":"2024-04-28T17:03:50","date_gmt":"2024-04-28T16:03:50","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/domina-el-uso-de-enum-en-mysql-para-optimizar-tus-bases-de-datos\/"},"modified":"2024-06-03T17:43:40","modified_gmt":"2024-06-03T16:43:40","slug":"domina-el-uso-de-enum-en-mysql-para-optimizar-tus-bases-de-datos","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/","title":{"rendered":"Master the use of ENUM in MySQL to Optimize your Databases"},"content":{"rendered":"<p>MySQL offers a variety of data types to meet different database storage and optimization needs. Among these, the ENUM data type is especially useful when you need to limit the values of a field to a fixed set of options. This article will guide you through everything you need to know about ENUM, from its definition to practical examples of how to implement and optimize it in your projects.<\/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\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#%C2%BFQue_es_ENUM\" >What is ENUM?<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#Ventajas_de_usar_ENUM\" >Advantages of using ENUM<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#Como_Definir_una_Columna_ENUM\" >How to Define an ENUM Column<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#Manipulacion_de_Datos_con_ENUM\" >Data Manipulation with ENUM<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#Insertar_Datos\" >Insert Data<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#Consultar_Datos_con_ENUM\" >Consult Data with ENUM<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#Actualizar_Datos\" >Update data<\/a><\/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\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#Consideraciones_al_Usar_ENUM\" >Considerations When Using ENUM<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-the-use-of-enum-in-mysql-to-optimize-your-databases\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFQue_es_ENUM\"><\/span>What is ENUM?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>ENUM, or enumeration, is a data type in MySQL designed to store a value from a predefined set of string constants. ENUM columns are extremely useful when you know the possible values that a field can take, such as days of the week, order statuses, fixed categories, among others.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Ventajas_de_usar_ENUM\"><\/span>Advantages of using ENUM<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ol>\n<li><strong>Clarity and Restriction<\/strong>: Ensures that the data entered in the column corresponds to the expected values, which can avoid errors in data manipulation.<\/li>\n<li><strong>Storage Efficiency<\/strong>: MySQL stores ENUM values internally as integers, which can be more efficient in terms of storage compared to full text strings.<\/li>\n<li><strong>Ease of Maintenance<\/strong>: Adding a new value to an ENUM type, although it requires altering the table, can be simpler and safer compared to reviewing all the business logic involved if the values were handled in client applications.<\/li>\n<\/ol>\n<h2><span class=\"ez-toc-section\" id=\"Como_Definir_una_Columna_ENUM\"><\/span>How to Define an ENUM Column<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When you define an ENUM column, you specify the allowed values for that column. For example, if you want a column to store the status of an item, you can define it as follows:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE items ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), state ENUM(&#039;Available&#039;, &#039;Out of Stock&#039;, &#039;Discontinued&#039;) NOT NULL );<\/code><\/pre>\n<p>In this example, <code>state<\/code> is an ENUM type column where only the values &#039;Available&#039;, &#039;Out of stock&#039; can be saved. or &#039;Discontinued&#039;. Attempting to insert any other value will result in an error.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Manipulacion_de_Datos_con_ENUM\"><\/span>Data Manipulation with ENUM<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Insertar_Datos\"><\/span>Insert Data<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To insert data into an ENUM column, you can specify the corresponding string value:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO items (name, status) VALUES (&#039;Laptop Gamer&#039;, &#039;Available&#039;);<\/code><\/pre>\n<p>MySQL also allows inserting using the index of the ENUM value, starting with 1:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO items (name, state) VALUES (&#039;Ergonomic Mouse&#039;, 1);<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Consultar_Datos_con_ENUM\"><\/span>Consult Data with ENUM<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>You can query ENUM columns using string values or their indexes:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT * FROM items WHERE status = &#039;Out of stock&#039;; SELECT * FROM items WHERE status = 2;<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Actualizar_Datos\"><\/span>Update data<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Updating a record with ENUM data type is as simple as updating any other type:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">UPDATE articles SET status = &#039;Discontinued&#039; WHERE id = 3;<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Consideraciones_al_Usar_ENUM\"><\/span>Considerations When Using ENUM<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Although ENUM is very useful, it has its challenges and limitations:<\/p>\n<ul>\n<li><strong>Rigidity<\/strong>: Once a column has been defined as ENUM, changing the possible values involves modifying the column definition, which can be inconvenient in large databases.<\/li>\n<li><strong>Performance on Large Data Sets<\/strong>: If you have a very large set of possible values (for example, more than 100), ENUM might not be the best choice due to the way MySQL handles searching for values.<\/li>\n<li><strong>Ambiguity in the Indexes<\/strong>: Using numeric indexes to represent states can make the code more difficult to understand and maintain.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The ENUM data type in MySQL is a powerful tool for controlling and validating the values of your data, ensuring that it conforms to a predefined set of options. Although it has some limitations and challenges, when used properly, it can make your databases more efficient and your applications more robust and maintainable.<\/p>\n<p>For more resources on database optimization and management, visit <a href=\"https:\/\/nelkodev.com\/en\/\">nelkodev.com<\/a>. And if you have questions or need specific advice, do not hesitate to contact me through my contact page. <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">contact<\/a>.<\/p>\n<p>I hope this article has given you a clear and practical understanding of how to work with ENUM in MySQL. With this tool, you will be better equipped to design and maintain your databases effectively and efficiently.<\/p>","protected":false},"excerpt":{"rendered":"<p>MySQL offers a variety of data types to meet different database storage and optimization needs. Among these, the ENUM data type is especially useful when you need to limit the values of a field to a fixed set of options. This article will guide you through everything you need to [\u2026]<\/p>","protected":false},"author":1,"featured_media":28980,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2033],"tags":[354,205,2189,639,353,1473,2073,352,571,60,122,2194,512],"class_list":["post-28979","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-mysql-data-types","tag-bases","tag-blog","tag-curso","tag-data","tag-datos","tag-domina","tag-enum","tag-mysql","tag-optimizar","tag-para","tag-tus","tag-types","tag-uso"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/28979","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=28979"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/28979\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/28980"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=28979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=28979"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=28979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}