{"id":29103,"date":"2024-04-04T23:35:37","date_gmt":"2024-04-04T22:35:37","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/aprendiendo-a-usar-binary-en-mysql-para-datos-de-longitud-fija\/"},"modified":"2024-06-03T17:44:27","modified_gmt":"2024-06-03T16:44:27","slug":"aprendiendo-a-usar-binary-en-mysql-para-datos-de-longitud-fija","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/learning-to-use-binary-in-mysql-for-fixed-length-data\/","title":{"rendered":"Learning to use BINARY in MySQL for Fixed Length Data"},"content":{"rendered":"<p>When it comes to storing information efficiently in our databases, the type of data we choose is crucial. One of the types that can often cause confusion is <code>BINARY<\/code>. In MySQL, <code>BINARY<\/code> It is perfect for storing fixed-length byte data, which is especially useful in certain contexts such as manipulating images, files or any data in binary format where accuracy is essential.<\/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\/learning-to-use-binary-in-mysql-for-fixed-length-data\/#%C2%BFQue_es_BINARY_en_MySQL\" >What is BINARY in MySQL?<\/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\/learning-to-use-binary-in-mysql-for-fixed-length-data\/#Ventajas_de_Utilizar_BINARY\" >Advantages of Using BINARY<\/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\/learning-to-use-binary-in-mysql-for-fixed-length-data\/#%C2%BFCuando_usar_BINARY\" >When to use BINARY?<\/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\/learning-to-use-binary-in-mysql-for-fixed-length-data\/#Implementando_BINARY_en_MySQL\" >Implementing BINARY in MySQL<\/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\/learning-to-use-binary-in-mysql-for-fixed-length-data\/#Creando_la_tabla_con_BINARY\" >Creating the table with BINARY<\/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\/learning-to-use-binary-in-mysql-for-fixed-length-data\/#Insertando_Datos\" >Inserting Data<\/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\/learning-to-use-binary-in-mysql-for-fixed-length-data\/#Consultando_Datos\" >Consulting 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\/learning-to-use-binary-in-mysql-for-fixed-length-data\/#Mejores_Practicas_y_Consejos\" >Best Practices and Tips<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFQue_es_BINARY_en_MySQL\"><\/span>What is BINARY in MySQL?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In MySQL, <code>BINARY<\/code> is a data type that allows storing strings of bytes of fixed length. Unlike the type <code>VARBINARY<\/code>, which stores strings of variable lengths, <code>BINARY<\/code> ensures that the length of the stored data is constant, padding with null bytes (0x00) at the end of the string if necessary to meet the specified length.<\/p>\n<p>The decision to use <code>BINARY<\/code> over other types of binary or text data should be based on the need to maintain a constant length and precise handling of the data, critical aspects in applications that depend on the accuracy of the stored bytes, such as in access control systems through fingerprints or applications that handle machine code directly.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ventajas_de_Utilizar_BINARY\"><\/span>Advantages of Using BINARY<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The use of <code>BINARY<\/code> brings with it several key advantages:<\/p>\n<ul>\n<li><strong>Data Consistency<\/strong>: Being of fixed length, operations on this data are predictable, making it easier to optimize queries.<\/li>\n<li><strong>Storage Efficiency<\/strong>: By not needing additional storage for string size, as is the case with <code>VARBINARY<\/code>, <code>BINARY<\/code> can be slightly more efficient in terms of storage when the exact length of the data is known in advance.<\/li>\n<li><strong>Simplified Comparison<\/strong>: Comparisons between strings <code>BINARY<\/code> They are faster and more direct, since no length adjustment is required during them.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFCuando_usar_BINARY\"><\/span>When to use BINARY?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><code>BINARY<\/code> It is ideal in situations where:<\/p>\n<ul>\n<li>The integrity and accuracy of the data is a priority.<\/li>\n<li>A fixed length is required, as in identification codes or binary keys.<\/li>\n<li>The data does not vary in size, making the use of variable data types unnecessary.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Implementando_BINARY_en_MySQL\"><\/span>Implementing BINARY in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To show how it can be implemented <code>BINARY<\/code> in MySQL, we will follow an example step by step. Suppose we are designing a database for a security system that stores an exact 16-byte security code for each device.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Creando_la_tabla_con_BINARY\"><\/span>Creating the table with BINARY<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE security_devices ( id INT AUTO_INCREMENT PRIMARY KEY, security_code BINARY(16) NOT NULL );<\/code><\/pre>\n<p>In this scheme, <code>security code<\/code> is the column where the codes will be stored. Each stored code will take up exactly 16 bytes, even if the actual code is shorter, filling in the remaining space with null bytes.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Insertando_Datos\"><\/span>Inserting Data<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO security_devices (security_code) VALUES (HEX(&#039;123456789ABCDEF&#039;));<\/code><\/pre>\n<p>Here, we are inserting a hexadecimal code that is less than 16 bytes. MySQL will automatically fill the rest with null bytes until the 16 bytes are complete.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Consultando_Datos\"><\/span>Consulting Data<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Querying the data is done in the usual way, but it is important to remember that the comparisons will be exact at the byte level.<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT * FROM security_devices WHERE security_code = HEX(&#039;123456789ABCDEF&#039;);<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Mejores_Practicas_y_Consejos\"><\/span>Best Practices and Tips<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When using <code>BINARY<\/code>, consider the following:<\/p>\n<ul>\n<li><strong>Planning<\/strong>: Make sure that <code>BINARY<\/code> is the right data type for your needs. Evaluate if you really need fixed length.<\/li>\n<li><strong>Null Handling<\/strong>: Note that <code>BINARY<\/code> will pad with null bytes; this can affect how you process and manipulate this data.<\/li>\n<li><strong>Optimization<\/strong>: Take advantage of the speed and consistency of <code>BINARY<\/code> to optimize queries.<\/li>\n<\/ul>\n<p>In summary, <code>BINARY<\/code> is a powerful data type in MySQL for certain contexts that require precision and consistency. Don&#039;t forget to visit my blog at <a href=\"https:\/\/nelkodev.com\/en\/\">NelkoDev<\/a> for more tips and tutorials on MySQL and other development topics. If you have any questions or concerns, 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 it comes to storing information efficiently in our databases, the data type we choose is crucial. One type that can often cause confusion is BINARY. In MySQL, BINARY is perfect for storing fixed-length byte data, which is especially useful in certain contexts [\u2026]<\/p>","protected":false},"author":1,"featured_media":29104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2033],"tags":[1616,2145,205,2189,639,353,2146,2144,352,60,2194,79],"class_list":["post-29103","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-mysql-data-types","tag-aprendiendo","tag-binary","tag-blog","tag-curso","tag-data","tag-datos","tag-fija","tag-longitud","tag-mysql","tag-para","tag-types","tag-usar"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29103","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=29103"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29103\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29104"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}