What is the best collation for my MySQL Database?



I am working in a database which has planned to answer many queries, the case is that I want to know what would be the advantages of using utf8_spanish2_ci, utf16_spanish2_ci and utf32_spanish2_ci?

They wanted me to explain their advantages and their desvantajas as well as which would be more optimal.

It should be noted that do not worry about how the queries are structured because I keep them constantly changing so they are as optimal as possible.

Thank you!

asked by Enmanuel Pascual Jimenez Guzma 10.04.2017 в 22:55

1 answer


First some definitions:

Character set: List of characters with their identification code.

Collations: Set of rules to compare characters in a given character set.

utf8_spanish_ci : Modern Spanish

utf8_spanish2_ci : Traditional Spanish.

In both collations : 'ñ' is an independent letter, between 'n' and 'o'.

Traditional Spanish : 'ch' is a letter, sorted between 'c' and d, and 'll' is a letter that is placed between 'l' and 'm'

It means that if you use the utf8_spanish2_ci there will be two more letters and that both will allow you to have the letter eñe (Ñ) inside your data and to show them.

NO they have nothing to do with the amount of data that will be inserted, they only define which letters will be displayed and which will not, that is, if you use spanish_ci, the EÑE (Ñ) something like "ÑAME" will be shown in the data, otherwise another type of collation would show you: áóñ instead of EÑE (Ñ) and other elements such as accents and words agreed and also you would appear the Character inspector where is the EÑE (Ñ)

Stop by here to see the problems it would generate:

Why the Inspector character ( ) appears in some data obtained from the Database?

I recommend utf_spanish_ci and then encode your HTML documents in utf-8, just like I did in my problem

MYSQL collation When creating the MySQL database, make sure that the string and other fields are in utf8_spanish_ci and the collation of the tables as well.


Some pages I recommend only utf8_spanish_ci for string and the rest   in utf8_unicode_ci, but I do not know how good it is, because I have not   tested.

HTML Content Put <head> of all HTML files:

<meta http-equiv="Content-type" content="text/html; charset=utf-8" />

PHP Content

header("Content-Type: text/html;charset=utf-8"); 
mysql_query("SET NAMES 'utf8'");
answered by 10.04.2017 / 23:09