¿Cuál es la mejor intercalación para usar en MySQL con PHP? [cerrado]

Sea muy, muy consciente de este problema que puede ocurrir al usar utf8_general_ci.

MySQL no distinguirá entre algunos caracteres en las sentencias select, si se usa la intercalación utf8_general_ci. Esto puede llevar a errores muy desagradables, especialmente por ejemplo, cuando los nombres de usuario están involucrados. Dependiendo de la implementación que utilice las tablas de la base de datos, este problema podría permitir a los usuarios malintencionados crear un nombre de usuario que coincida con una cuenta de administrador.

Este problema se expone por lo menos a principios de las 5.versiones x-No estoy seguro de si este comportamiento cambió más tarde.

No soy DBA, pero para evitar este problema, siempre voy con utf8-bin en lugar de uno que no distingue entre mayúsculas y minúsculas.

El siguiente script describe el problema por ejemplo.

-- first, create a sandbox to play in
CREATE DATABASE `sandbox`;
use `sandbox`;

-- next, make sure that your client connection is of the same 
-- character/collate type as the one we're going to test next:
charset utf8 collate utf8_general_ci

-- now, create the table and fill it with values
CREATE TABLE `test` (`key` VARCHAR(16), `value` VARCHAR(16) )
    CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `test` VALUES ('Key ONE', 'value'), ('Key TWO', 'valúe');

-- (verify)
SELECT * FROM `test`;

-- now, expose the problem/bug:
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get BOTH keys here! MySQLs UTF8 collates that are 
-- case insensitive (ending with _ci) do not distinguish between 
-- both values!
--
-- collate 'utf8_bin' doesn't have this problem, as I'll show next:
--

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get just one key now, as you'd expect.
--
-- This problem appears to be specific to utf8. Next, I'll try to 
-- do the same with the 'latin1' charset:
--

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_general_ci

-- next, convert the values that we've previously inserted
-- in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected). This shows 
-- that the problem with utf8/utf8_generic_ci isn't present 
-- in latin1/latin1_general_ci
--
-- To complete the example, I'll check with the binary collate
-- of latin1 as well:

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected).
--
-- Finally, I'll re-introduce the problem in the exact same 
-- way (for any sceptics out there):

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_generic_ci

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- now, re-check for the problem/bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Two keys.
--

DROP DATABASE sandbox;

En realidad, probablemente quieras usar utf8_unicode_ci o utf8_general_ci.

• utf8_general_ci ordena eliminando todos los acentos y ordenando como si fuera ASCII
• utf8_unicode_ci utiliza el orden de clasificación Unicode, por lo que ordena correctamente en más idiomas

Sin embargo, si solo está usando esto para almacenar texto en inglés, estos no deberían diferir.

Es mejor usar el conjunto de caracteres utf8mb4 con la intercalación utf8mb4_unicode_ci.

El conjunto de caracteres, utf8, solo admite una pequeña cantidad de puntos de código UTF-8, aproximadamente el 6% de los caracteres posibles. utf8 solo soporta el Plano Multilingüe Básico (BMP). Hay otros 16 aviones. Cada plano contiene 65.536 caracteres. utf8mb4 soporta los 17 planos.

MySQL truncará los caracteres UTF-8 de 4 bytes dando como resultado datos dañados.

El conjunto de caracteres utf8mb4 se introdujo en MySQL 5.5.3 en 2010-03-24.

Algunos de los cambios requeridos para usar el nuevo conjunto de caracteres no son triviales:

• Es posible que sea necesario realizar cambios en el adaptador de la base de datos de la aplicación.
• Será necesario realizar cambios en mi.cnf, incluyendo establecer el conjunto de caracteres, la intercalación y cambiar innodb_file_format a Barracuda
• Las instrucciones SQL CREATE pueden necesitar incluir: ROW_FORMAT=DYNAMIC
  • Se requiere DINÁMICA para los índices en VARCHAR(192) y más grande.

NOTA: Cambiar a Barracuda desde Antelope, puede requerir reiniciar el servicio MySQL más de una vez. innodb_file_format_max no cambia hasta después de que el servicio MySQL se haya reiniciado a: innodb_file_format = barracuda.

MySQL utiliza el antiguo formato de archivo Antelope InnoDB. Barracuda admite formatos de fila dinámicos, que necesitará si no desea golpear los errores SQL para crear índices y claves después de cambiar al conjunto de caracteres: utf8mb4

• #1709-El tamaño de la columna del índice también grande. El tamaño máximo de columna es de 767 bytes.
• #1071-La clave especificada era demasiado larga; la longitud máxima de la clave es de 767 bytes

El siguiente escenario ha sido probado en MySQL 5.6.17: Por defecto, MySQL está configurado así:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope

Detenga su servicio MySQL y agregue las opciones a su my existente.cnf:

[client]
default-character-set= utf8mb4

[mysqld]
explicit_defaults_for_timestamp = true
innodb_large_prefix = true
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = true

# Character collation
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

Ejemplo de sentencia SQL CREATE:

CREATE TABLE Contacts (
 id INT AUTO_INCREMENT NOT NULL,
 ownerId INT DEFAULT NULL,
 created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 contact VARCHAR(640) NOT NULL,
 prefix VARCHAR(128) NOT NULL,
 first VARCHAR(128) NOT NULL,
 middle VARCHAR(128) NOT NULL,
 last VARCHAR(128) NOT NULL,
 suffix VARCHAR(128) NOT NULL,
 notes MEDIUMTEXT NOT NULL,
 INDEX IDX_CA367725E05EFD25 (ownerId),
 INDEX created (created),
 INDEX modified_idx (modified),
 INDEX contact_idx (contact),
 PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT=DYNAMIC;

• Puede ver el error #1709 generado para INDEX contact_idx (contact) si ROW_FORMAT=DYNAMIC se elimina de la CREACIÓN instrucción.

NOTA: Cambiar el índice para limitar a los primeros 128 caracteres en contact elimina el requisito de usar Barracuda con ROW_FORMAT=DYNAMIC

INDEX contact_idx (contact(128)),

También tenga en cuenta: cuando dice que el tamaño del campo es VARCHAR(128), eso no es 128 bytes. Puede usar tener 128, 4 caracteres de byte o 128, 1 caracteres de byte.

Esta instrucción INSERT debe contener el carácter 'poo' de 4 bytes en la fila 2:

INSERT INTO `Contacts` (`id`, `ownerId`, `created`, `modified`, `contact`, `prefix`, `first`, `middle`, `last`, `suffix`, `notes`) VALUES
(1, NULL, '0000-00-00 00:00:00', '2014-08-25 03:00:36', '1234567890', '12345678901234567890', '1234567890123456789012345678901234567890', '1234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678', '', ''),
(2, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '', '', '', '', ''),
(3, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '', '', '123', '', '');

Se puede ver la cantidad de espacio utilizado por el last columna:

mysql> SELECT BIT_LENGTH(`last`), CHAR_LENGTH(`last`) FROM `Contacts`;
+--------------------+---------------------+
| BIT_LENGTH(`last`) | CHAR_LENGTH(`last`) |
+--------------------+---------------------+
|               1024 |                 128 | -- All characters are ASCII
|               4096 |                 128 | -- All characters are 4 bytes
|               4024 |                 128 | -- 3 characters are ASCII, 125 are 4 bytes
+--------------------+---------------------+

En su adaptador de base de datos, es posible que desee establecer el conjunto de caracteres y la intercalación para su conexión:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

En PHP, esto se establecería para: \PDO::MYSQL_ATTR_INIT_COMMAND

Referencias:

• Manual de referencia de Mysql 5.6: Límites en las tablas InnoDB
• Cómo soportar Unicode completo en bases de datos MySQL

Las intercalaciones afectan cómo se ordenan los datos y cómo se comparan las cadenas entre sí. Eso significa que debes usar la intercalación que la mayoría de tus usuarios esperan.

Ejemplo de la documentación :

utf8_general_ci también es satisfactorio tanto en alemán como en francés, excepto que 'ß' es igual a 's', y no a "ss". Si esto es aceptable para su aplicación, entonces usted debe utilizar utf8_general_ci porque es más rápido. De lo contrario, use utf8_unicode_ci porque es más preciso.

Por lo tanto, depende de su base de usuarios esperada y de cuánto necesita ordenar correctamente. Para una base de usuarios en inglés, utf8_general_ci debería ser suficiente, para otros idiomas, como el sueco, se han creado colaciones especiales.

Esencialmente, depende de cómo pienses de una cadena.

Siempre uso utf8_bin debido al problema resaltado por Guus. En mi opinión, en lo que respecta a la base de datos, una cadena sigue siendo solo una cadena. Una cadena es un número de caracteres UTF-8. Un personaje tiene una representación binaria, así que ¿por qué necesita saber el idioma que está utilizando? Por lo general, las personas construirán bases de datos para sistemas con el alcance de sitios multilingües. Este es el punto de usando UTF-8 como un conjunto de caracteres. Soy un poco purista, pero creo que los riesgos de error superan en gran medida la ligera ventaja que puede obtener en la indexación. Cualquier regla relacionada con el lenguaje debe hacerse a un nivel mucho más alto que el DBMS.

En mis libros "valor" nunca debería ser igual a "valor"en un millón de años.

Si quiero almacenar un campo de texto y hacer una búsqueda insensible a mayúsculas y minúsculas, usaré funciones de cadena MYSQL con funciones PHP como LOWER() y la función php strtolower().

Para la información textual UTF-8, debe usar utf8_general_ci because...

• utf8_bin: comparar cadenas por el valor binario de cada carácter en la cadena

• utf8_general_ci: comparar cadenas uso de reglas generales de lenguaje y usando comparaciones insensibles a mayúsculas y minúsculas

También conocido como se debe hacer la búsqueda y la indexación de los datos más rápido/más eficiente/más útil.

La respuesta aceptada sugiere bastante definitivamente usar utf8_unicode_ci, y mientras que para nuevos proyectos eso es genial, quería relatar mi reciente experiencia contraria por si le ahorra tiempo a alguien.

Debido a que utf8_general_ci es la intercalación predeterminada para Unicode en MySQL, si desea usar utf8_unicode_ci, entonces debe especificarlo en un lote de lugares.

Por ejemplo, todas las conexiones de cliente no solo tienen un conjunto de caracteres predeterminado (tiene sentido para mí), sino que también una intercalación predeterminada (es decir, la intercalación siempre será utf8_general_ci para unicode).

Probablemente, si usa utf8_unicode_ci para sus campos, sus scripts que se conectan a la base de datos deberán actualizarse para mencionar explícitamente la intercalación deseada otherwise de lo contrario, las consultas que usan cadenas de texto pueden fallar cuando su conexión está utilizando la intercalación predeterminada.

El resultado es que al convertir un sistema existente de cualquier tamaño a Unicode / utf8, puede terminar siendo forzado a use utf8_general_ci debido a la forma en que MySQL maneja los valores predeterminados.

Para el caso resaltado por Guus, yo sugeriría fuertemente usar utf8_unicode_cs (sensible a mayúsculas y minúsculas, coincidencia estricta, ordenando correctamente en su mayor parte) en lugar de utf8_bin (coincidencia estricta, orden incorrecto).

Si el campo está destinado a ser buscado, en lugar de emparejado para un usuario, entonces use utf8_general_ci o utf8_unicode_ci. Ambos son insensibles a mayúsculas y minúsculas, uno coincidirá con pérdidas ('ß' es igual a 's', y no a'ss'). También hay versiones específicas del idioma, como utf8_german_ci donde la coincidencia de pérdida es más adecuada para el lenguaje especificado.

[Editar - casi 6 años después]

Ya no recomiendo el conjunto de caracteres "utf8" en MySQL, y en su lugar recomiendo el conjunto de caracteres "utf8mb4". Coinciden casi por completo, pero permiten un poco (mucho) más caracteres unicode.

Siendo realistas, MySQL debería haber actualizado el conjunto de caracteres" utf8 "y las respectivas intercalaciones para que coincidan con la especificación "utf8", pero en su lugar, un carácter separado establecer y las respectivas intercalaciones para no afectar la designación de almacenamiento para aquellos que ya utilizan su conjunto de caracteres "utf8" incompleto.

Encontré estas tablas de cotejo útiles. http://collation-charts.org/mysql60 / . No estoy seguro de cuál es el utf8_general_ci usado.

Por ejemplo aquí está el gráfico para utf8_swedish_ci. Muestra qué caracteres interpreta como los mismos. http://collation-charts.org/mysql60/mysql604.utf8_swedish_ci.html

En el archivo de carga de su base de datos, agregue la siguiente línea antes de cualquier línea:

SET NAMES utf8;

Y su problema debe ser resuelto.