{"id":29051,"date":"2024-05-03T10:10:07","date_gmt":"2024-05-03T09:10:07","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/guia-completa-sobre-colaciones-en-mysql-configuracion-y-uso\/"},"modified":"2024-06-03T17:44:06","modified_gmt":"2024-06-03T16:44:06","slug":"guia-completa-sobre-colaciones-en-mysql-configuracion-y-uso","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/complete-guide-on-collations-in-mysql-configuration-and-use\/","title":{"rendered":"Complete Guide to Collations in MySQL: Configuration and Use"},"content":{"rendered":"<p>Collations in MySQL are essential to understanding how text data is handled within the database. Setting character sets and collations correctly can be critical to the performance and accuracy of your queries and data storage. In this article, we&#039;ll explore in depth what collations are, how they affect data handling, and how you can properly configure them at different levels of your MySQL server: from the entire server down to individual columns in your tables.<\/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-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-on-collations-in-mysql-configuration-and-use\/#%C2%BFQue_es_una_Colacion\" >What is a Collation?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-on-collations-in-mysql-configuration-and-use\/#Configuracion_del_Servidor_de_MySQL\" >MySQL Server Configuration<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-on-collations-in-mysql-configuration-and-use\/#Configuracion_de_Bases_de_Datos\" >Database Configuration<\/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\/complete-guide-on-collations-in-mysql-configuration-and-use\/#Configuracion_de_Tablas\" >Table Configuration<\/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\/complete-guide-on-collations-in-mysql-configuration-and-use\/#Configuracion_de_Columnas\" >Column Configuration<\/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\/complete-guide-on-collations-in-mysql-configuration-and-use\/#Problemas_Comunes_y_Soluciones\" >Common Problems and Solutions<\/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\/complete-guide-on-collations-in-mysql-configuration-and-use\/#Conclusiones\" >Conclusions<\/a><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"%C2%BFQue_es_una_Colacion\"><\/span>What is a Collation?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>A collation is a set of rules that defines how characters are compared and ordered in a database. MySQL uses collations along with character sets to determine how these operations should be performed. Each collation is closely tied to a specific character set and determines things like case sensitivity and character accent sensitivity.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Configuracion_del_Servidor_de_MySQL\"><\/span>MySQL Server Configuration<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Setting character sets and collations at the server level defines the default behavior for all databases and tables that do not have specific settings. This is done through system variables. For example, to set the default character set and collation on the server, you can use the following commands in your MySQL console:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SET GLOBAL character_set_server = &#039;utf8mb4&#039;; SET GLOBAL collation_server = &#039;utf8mb4_unicode_ci&#039;;<\/code><\/pre>\n<p>These commands configure the server to use <code>utf8mb4<\/code> like the character set and <code>utf8mb4_unicode_ci<\/code> as the default collation, which are recommended for broad compatibility with Unicode characters.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Configuracion_de_Bases_de_Datos\"><\/span>Database Configuration<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Each database in MySQL can have its own character set and collation settings, which may differ from the server&#039;s default settings. To specify or change the configuration of a database, you can use the following SQL command:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;<\/code><\/pre>\n<p>If the database already exists and you want to change its configuration, you can use:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Configuracion_de_Tablas\"><\/span>Table Configuration<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Just like databases, each table within MySQL can have its own character set and collation settings. When creating or modifying tables, you can specify these settings as follows:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE my_table ( id INT, text VARCHAR(100) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;<\/code><\/pre>\n<p>To modify an existing table:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Configuracion_de_Columnas\"><\/span>Column Configuration<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>For even greater granularity, MySQL allows character sets and collations to be specified at the level of individual columns in tables. This is especially useful for tables that store multilingual data. Here is an example of how to define these attributes for a specific column:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">ALTER TABLE my_table MODIFY COLUMN text VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Problemas_Comunes_y_Soluciones\"><\/span>Common Problems and Solutions<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>Bad string comparison:<\/strong> This occurs when the collations of the compared columns differ. Make sure that the columns involved in comparison operations have compatible collations.<\/p>\n<p><strong>Incorrect order of operations <code>ORDER BY<\/code><\/strong>: Similar to the previous problem, if the collation is not appropriate, the ordering of the data may not be as expected. Checking and adjusting the collation of the column can solve this problem.<\/p>\n<p><strong>Errors when changing snacks:<\/strong> If you encounter problems when trying to change the column collation, you may need to check for existing data that cannot be easily converted to the new character set or collation without loss of information.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Conclusiones\"><\/span>Conclusions<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Correctly configuring character sets and collations in MySQL is essential to ensure the correct functioning of databases, especially in multilingual environments. We have seen how these settings can be set at different levels, providing flexibility depending on the specific needs of each part of your database. If you want to continue learning more about optimizing your databases, don&#039;t forget to explore the rest of the content on <a href=\"https:\/\/nelkodev.com\/en\/\">nelkodev.com<\/a>!<\/p>\n<p>For any questions or queries, you can always contact me through the contact page. <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">contact<\/a>. I&#039;m here to help you learn MySQL!<\/p>","protected":false},"excerpt":{"rendered":"<p>Las colaciones en MySQL son fundamentales para entender c\u00f3mo se manejan los datos de texto dentro de la base de datos. Establecer correctamente los conjuntos de caracteres y las colaciones puede ser decisivo para el rendimiento y la precisi\u00f3n de las consultas y almacenamiento de datos. En este art\u00edculo, exploraremos a fondo qu\u00e9 son las [&hellip;]<\/p>","protected":false},"author":1,"featured_media":29052,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185],"tags":[205,2120,500,572,2189,2199,358,352,74,512],"class_list":["post-29051","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","tag-blog","tag-colaciones","tag-completa","tag-configuracion","tag-curso","tag-globalization","tag-guia","tag-mysql","tag-sobre","tag-uso"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29051","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=29051"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29051\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29052"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}