{"id":17339,"date":"2024-05-29T07:50:29","date_gmt":"2024-05-29T14:50:29","guid":{"rendered":"https:\/\/www.pingcap.com\/?post_type=article&#038;p=17339"},"modified":"2024-06-16T23:14:32","modified_gmt":"2024-06-17T06:14:32","slug":"master-enum-data-type-in-mysql-databases","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/master-enum-data-type-in-mysql-databases\/","title":{"rendered":"Ultimate Guide to ENUM Data Type in MySQL Databases"},"content":{"rendered":"<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Understanding_ENUM\"><\/span>Understanding ENUM<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The ENUM data type is a handy tool in MySQL and other database management systems (DBMS) for defining a column with a pre-defined set of values. These values are enumerated when the table is created, encapsulating the column&#8217;s possible values within those specified at the time of creation. This restriction ensures data consistency and simplifies validation logic by enforcing the rule that only the values listed when defining the column can appear in each row.<\/p>\n\n\n\n<p>ENUM comes with several benefits that make it a preferred choice in specific scenarios. First, it can significantly reduce errors and enhance data integrity by limiting the permissible values to a fixed set defined by the database schema. It simplifies data validation processes and makes querying more straightforward since the range of possible values is known and constrained. Furthermore, ENUM can lead to storage efficiency. Since MySQL internally represents these strings as integers, it can be more space-efficient than storing the string values directly, especially if the strings are relatively long and the number of ENUM elements is small.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">ENUM Data Type in MySQL<\/h3>\n\n\n\n<p>In MySQL, each ENUM value is internally indexed starting from 1. This means that the ENUM values are stored as tiny integers but presented as strings in query results, combining the efficiency of integer indexing with the clarity of descriptive strings. This internal indexing makes ENUM particularly suitable for representing static sets of values, such as status codes, state names, or categories.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Using_ENUM_in_MySQL_Databases\"><\/span><strong>Using ENUM in <\/strong><strong>MySQL<\/strong><strong> Databases<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>ENUM is straightforward to use. When creating a table, you can define an ENUM column as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE example_table (\n    id INT,\n    status ENUM('active', 'inactive', 'pending')\n);<\/code><\/pre>\n\n\n\n<p>This SQL creates a table with an ENUM column named <code>status<\/code>, which can hold three possible values: &#8216;active&#8217;, &#8216;inactive&#8217;, or &#8216;pending&#8217;.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Creating ENUM Columns<\/strong><\/h3>\n\n\n\n<p>Adding an ENUM column to an existing table involves the <code>ALTER TABLE<\/code> statement:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE example_table ADD COLUMN level ENUM('low', 'medium', 'high') NOT NULL;<\/code><\/pre>\n\n\n\n<p>This statement adds a new ENUM column named <code>level<\/code> with possible values &#8216;low&#8217;, &#8216;medium&#8217;, and &#8216;high&#8217; to <code>example_table<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Modifying ENUM Columns<\/strong><\/h3>\n\n\n\n<p>To modify an existing ENUM column, you may use a similar <code>ALTER TABLE<\/code> statement:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE example_table MODIFY COLUMN level ENUM('low', 'medium', 'high', 'critical') NOT NULL;<\/code><\/pre>\n\n\n\n<p>This modifies the <code>level<\/code> column to include a new possible value &#8216;critical&#8217;.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Querying ENUM Columns<\/strong><\/h3>\n\n\n\n<p>Querying an ENUM column is as direct as querying any other type of column:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM example_table WHERE status = 'active';<\/code><\/pre>\n\n\n\n<p>This query returns all rows where the status column has the value &#8216;active&#8217;.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Best_Practices\"><\/span>Best Practices<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">When to Use ENUM<\/h3>\n\n\n\n<p>ENUM is best used when dealing with columns that have a fixed set of values which are unlikely to change over time. Examples might include months of the year, days of the week, states of an order (e.g., &#8216;ordered&#8217;, &#8216;shipped&#8217;, &#8216;delivered&#8217;), or predefined categories.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Common Pitfalls<\/h3>\n\n\n\n<p>Despite its benefits, ENUM should be used judiciously. One of the pitfalls of ENUM is inflexibility; adding or removing a value requires altering the table, which can be cumbersome and risk data integrity for large datasets. Consequently, ENUM is less suitable for values that are subject to frequent changes or expansion.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"ENUM_and_TiDB_Extending_Compatibility_and_Performance\"><\/span><strong>ENUM and <\/strong><strong>\ud2f0DB<\/strong><strong>: Extending Compatibility and Performance<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>While primarily discussing ENUM in the context of MySQL, it&#8217;s essential to note that <a href=\"\/ko\/tidb\/\">\ud2f0DB<\/a>, a highly scalable, MySQL-compatible, distributed SQL database, supports all the <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/data-type-string#string-types\">MySQL string types<\/a>, including ENUM. TiDB&#8217;s compatibility with MySQL ensures that applications using ENUM can migrate to TiDB without altering their data models. Beyond just compatibility, TiDB extends the capabilities of traditional databases by providing horizontal scalability, strong consistency, and high availability, making it an excellent choice for <a href=\"https:\/\/www.pingcap.com\/ko\/blog\/why-distributed-sql-databases-elevate-modern-app-dev\/\">modern applications<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"<p>Understanding ENUM The ENUM data type is a handy tool in MySQL and other database management systems (DBMS) for defining a column with a pre-defined set of values. These values are enumerated when the table is created, encapsulating the column&#8217;s possible values within those specified at the time of creation. This restriction ensures data consistency [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"template":"","class_list":["post-17339","article","type-article","status-publish","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Ultimate Guide to ENUM Data Type in MySQL Databases<\/title>\n<meta name=\"description\" content=\"Master the ENUM data type in MySQL for efficient data storage and retrieval. Benefit from TiDB\u2019s enhanced compatibility for robust data integrity, scalability, and availability\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pingcap.com\/ko\/article\/master-enum-data-type-in-mysql-databases\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Ultimate Guide to ENUM Data Type in MySQL Databases\" \/>\n<meta property=\"og:description\" content=\"Master the ENUM data type in MySQL for efficient data storage and retrieval. Benefit from TiDB\u2019s enhanced compatibility for robust data integrity, scalability, and availability\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/article\/master-enum-data-type-in-mysql-databases\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-17T06:14:32+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1440\" \/>\n\t<meta property=\"og:image:height\" content=\"714\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/\",\"url\":\"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/\",\"name\":\"Ultimate Guide to ENUM Data Type in MySQL Databases\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"datePublished\":\"2024-05-29T14:50:29+00:00\",\"dateModified\":\"2024-06-17T06:14:32+00:00\",\"description\":\"Master the ENUM data type in MySQL for efficient data storage and retrieval. Benefit from TiDB\u2019s enhanced compatibility for robust data integrity, scalability, and availability\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Articles\",\"item\":\"https:\/\/www.pingcap.com\/article\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Ultimate Guide to ENUM Data Type in MySQL Databases\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Ultimate Guide to ENUM Data Type in MySQL Databases","description":"Master the ENUM data type in MySQL for efficient data storage and retrieval. Benefit from TiDB\u2019s enhanced compatibility for robust data integrity, scalability, and availability","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pingcap.com\/ko\/article\/master-enum-data-type-in-mysql-databases\/","og_locale":"ko_KR","og_type":"article","og_title":"Ultimate Guide to ENUM Data Type in MySQL Databases","og_description":"Master the ENUM data type in MySQL for efficient data storage and retrieval. Benefit from TiDB\u2019s enhanced compatibility for robust data integrity, scalability, and availability","og_url":"https:\/\/www.pingcap.com\/ko\/article\/master-enum-data-type-in-mysql-databases\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_modified_time":"2024-06-17T06:14:32+00:00","og_image":[{"width":1440,"height":714,"url":"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_site":"@PingCAP","twitter_misc":{"Est. reading time":"3\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/","url":"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/","name":"Ultimate Guide to ENUM Data Type in MySQL Databases","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"datePublished":"2024-05-29T14:50:29+00:00","dateModified":"2024-06-17T06:14:32+00:00","description":"Master the ENUM data type in MySQL for efficient data storage and retrieval. Benefit from TiDB\u2019s enhanced compatibility for robust data integrity, scalability, and availability","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/article\/master-enum-data-type-in-mysql-databases\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Articles","item":"https:\/\/www.pingcap.com\/article\/"},{"@type":"ListItem","position":3,"name":"Ultimate Guide to ENUM Data Type in MySQL Databases"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]}]}},"card_markup":"        <a class=\"card-article\" href=\"https:\/\/www.pingcap.com\/ko\/article\/master-enum-data-type-in-mysql-databases\/\">            <h3>Ultimate Guide to ENUM Data Type in MySQL Databases<\/h3>            <p>Understanding ENUM The ENUM data type is a handy tool in MySQL and other database management systems (DBMS) for defining a column with a pre-defined set of values. These values are enumerated when the table is created, encapsulating the column&#8217;s possible values within those specified at the time of creation. This restriction ensures data consistency [&hellip;]<\/p>        <\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article\/17339","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/article"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/8"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=17339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}