{"id":29009,"date":"2024-04-19T06:12:21","date_gmt":"2024-04-19T05:12:21","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/mysql-uuid-smackdown-uuid-vs-int-en-claves-primarias\/"},"modified":"2024-06-03T17:43:51","modified_gmt":"2024-06-03T16:43:51","slug":"mysql-uuid-smackdown-uuid-vs-int-en-claves-primarias","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/","title":{"rendered":"MySQL UUID Smackdown: UUID vs. INT in Primary Keys"},"content":{"rendered":"<p>When designing databases, one of the most important decisions is the choice of data type for primary keys (PK). Traditionally, using integer (INT) values has been the norm due to its efficiency and simplicity. However, with the increasing need for scalability and system distribution, the UUID (Universal Unique Identifier) data type has gained popularity. This article delves into the use of UUIDs as a primary key in MySQL, analyzing its benefits and difficulties compared to traditional INT values.<\/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\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#%C2%BFQue_es_un_UUID\" >What is a UUID?<\/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\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#Implementacion_de_UUID_en_MySQL\" >UUID implementation in MySQL<\/a><\/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\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#Ventajas_de_usar_UUID_como_Clave_Primaria\" >Advantages of using UUID as Primary Key<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#1_Unicidad_Global\" >1. Global Uniqueness<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#2_Escalabilidad_y_Replicacion\" >2. Scalability and Replication<\/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\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#3_Desacoplamiento_de_Datos\" >3. Data Decoupling<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#Desventajas_de_los_UUIDs\" >Disadvantages of UUIDs<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/blog\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#1_Uso_de_Espacio\" >1. Use of Space<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/nelkodev.com\/en\/blog\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#2_Rendimiento\" >2. Performance<\/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\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#3_Complejidad\" >3. Complexity<\/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\/mysql-uuid-smackdown-uuid-vs-int-in-primary-keys\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFQue_es_un_UUID\"><\/span>What is a UUID?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A UUID is a 128-bit number that is generated in a way that is unique not only within a database but, ideally, in any context. This is due to the combination of several factors such as the MAC address of the computer, the date and time of its generation, among other methods depending on the algorithm used (such as, for example, <code>uuid1<\/code>, <code>uuid4<\/code> among others). UUIDs are represented by 32 hexadecimal characters and divided into five groups separated by hyphens, following this format: <code>123e4567-e89b-12d3-a456-426614174000<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Implementacion_de_UUID_en_MySQL\"><\/span>UUID implementation in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To use UUIDs in MySQL as a primary key, we must first understand how to generate them. MySQL offers a function called <code>UUID()<\/code> which returns a version 1 UUID as a 36-character string (including hyphens):<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT UUID();<\/code><\/pre>\n<p>To store a UUID, you need a field with the data type <code>CHAR(36)<\/code> or, to optimize storage, convert the UUID to a binary format using the function <code>UNHEX(REPLACE(UUID(),&#039;-&#039;,&#039;&#039;))<\/code> and save it in a field <code>BINARY(16)<\/code>.<\/p>\n<p>Example of table with UUID as PK:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE users ( id BINARY(16) PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) ); INSERT INTO users(id, name, email) VALUES (UNHEX(REPLACE(UUID(),&#039;-&#039;,&#039;&#039;)), &#039;Juan Perez&#039;, &#039;juanperez@mail.com&#039;);<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Ventajas_de_usar_UUID_como_Clave_Primaria\"><\/span>Advantages of using UUID as Primary Key<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"1_Unicidad_Global\"><\/span>1. Global Uniqueness<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The biggest benefit of UUIDs is that they ensure global uniqueness, allowing data integration and synchronization between various databases without duplication conflicts.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_Escalabilidad_y_Replicacion\"><\/span>2. Scalability and Replication<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>UUIDs are ideal for distributed systems. They facilitate data replication between multiple databases without the need to coordinate primary keys, which is a common challenge with auto-incremental IDs.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"3_Desacoplamiento_de_Datos\"><\/span>3. Data Decoupling<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Using UUIDs allows information to be decoupled, for example by avoiding exposing the record creation sequence, which may be desirable for privacy or security reasons.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Desventajas_de_los_UUIDs\"><\/span>Disadvantages of UUIDs<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"1_Uso_de_Espacio\"><\/span>1. Use of Space<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>UUIDs require more space than typical INTs (16 bytes vs. 4 bytes per INT value). This can increase disk space and memory usage, affecting overall performance.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_Rendimiento\"><\/span>2. Performance<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>UUIDs can lead to index fragmentation due to their random nature, especially in large volumes of data. This can result in a degradation in data access times compared to sequential increments of INTs.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"3_Complejidad\"><\/span>3. Complexity<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The generation, storage and manipulation of UUIDs are more complex compared to simple auto-incremental INT values. This can lead to an increase in code complexity and learning curve for developers.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Using UUIDs as primary keys in MySQL offers clear advantages in terms of scalability and management in distributed environments, but also brings challenges in terms of performance and resource management. The choice between UUID and INT will depend on the specific needs of the project, considering factors such as the amount of data, the nature of the application, and the deployment environment.<\/p>\n<p>For more information about databases and development, I invite you to visit <a href=\"https:\/\/nelkodev.com\/en\/\">my blog<\/a> and if you have any questions or need assistance, do not hesitate to contact me through <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">my contact page<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"<p>When designing databases, one of the most important decisions is the choice of data type for primary keys (PK). Traditionally, the use of integer (INT) values has been the norm due to their efficiency and simplicity. However, with the increasing need for scalability and the need for more complex data types, the need for more complex data types has become more and more difficult.<\/p>","protected":false},"author":1,"featured_media":29010,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2033],"tags":[205,295,2189,639,2074,352,2094,2092,2194,2093,1502],"class_list":["post-29009","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-mysql-data-types","tag-blog","tag-claves","tag-curso","tag-data","tag-int","tag-mysql","tag-primarias","tag-smackdown","tag-types","tag-uuid","tag-vs"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29009","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=29009"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29009\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29010"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29009"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29009"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29009"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}