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


Me pregunto si existe una "mejor" opción para la compilación en MySQL para un sitio web general en el que no esté 100% seguro de lo que se ingresará. Entiendo que todas las codificaciones deberían ser iguales, como MySQL, Apache, HTML y cualquier cosa dentro de PHP.

En el pasado, he configurado PHP para que salga en "UTF-8", pero ¿qué colación coincide con esto en MySQL? Estoy pensando que es uno de los UTF-8, pero he usado utf8_unicode_ci, utf8_general_ciy utf8_bin antes de.


648
2017-12-15 07:48


origen


Respuestas:


La principal diferencia es la precisión de clasificación (al comparar caracteres en el idioma) y el rendimiento. El único especial es utf8_bin que es para comparar caracteres en formato binario.

utf8_general_ci es algo más rápido que utf8_unicode_ci, pero menos preciso (para clasificar). los codificación de lenguaje específico utf8 (como utf8_swedish_ci) contienen reglas de lenguaje adicionales que las hacen más precisas para ordenarlas en esos idiomas. La mayoría de las veces que uso utf8_unicode_ci (Prefiero precisión o pequeñas mejoras de rendimiento), a menos que tenga una buena razón para preferir un idioma específico.

Puede leer más sobre juegos de caracteres específicos de unicode en el manual de MySQL - http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html


544
2017-12-15 07:58



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

MySQL no distinguirá entre algunos caracteres en sentencias seleccionadas, si el utf8_general_ci se usa la colación Esto puede conducir a errores muy desagradables, especialmente, por ejemplo, cuando se trata de nombres de usuario. Dependiendo de la implementación que utiliza las tablas de la base de datos, este problema podría permitir a los usuarios maliciosos crear un nombre de usuario que coincida con una cuenta de administrador.

Este problema se expone por lo menos en las primeras versiones 5.x. No estoy seguro de si este comportamiento se modificó más adelante.

No soy DBA, pero para evitar este problema, siempre voy con utf8-bin en lugar de uno insensible a mayúsculas y minúsculas

El script a continuación describe el problema con el 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;

108
2018-06-13 11:02



En realidad, es probable que quiera usar utf8_unicode_ci o utf8_general_ci.

  • utf8_general_ci ordena quitando todos los acentos y clasificando 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.


103
2017-12-15 08:02



Lo mejor es usar conjunto de caracteres utf8mb4 con la colación utf8mb4_unicode_ci.

El juego de caracteres, utf8, solo admite una pequeña cantidad de puntos de código UTF-8, alrededor del 6% de los caracteres posibles. utf8 solo admite el plano multilingüe básico (BMP). Hay otros 16 aviones. Cada plano contiene 65,536 caracteres. utf8mb4 admite los 17 aviones

MySQL truncará los caracteres UTF-8 de 4 bytes, dando como resultado datos corruptos.

los utf8mb4 el juego de caracteres se introdujo en MySQL 5.5.3 en 2010-03-24.

Algunos de los cambios necesarios 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.
  • Deberán realizarse cambios en my.cnf, incluido el establecimiento del conjunto de caracteres, la intercalación y el cambio de innodb_file_format a Barracuda.
  • Las sentencias SQL CREATE pueden necesitar incluir: ROW_FORMAT=DYNAMIC
    • DYNAMIC es obligatorio para los índices en VARCHAR (192) y más grandes.

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

MySQL usa el viejo Antelope Formato de archivo InnoDB. Barracuda admite formatos de fila dinámicos, que necesitará si no desea ejecutar los errores de SQL para crear índices y claves después de cambiar al juego de caracteres: utf8mb4

  • # 1709 - Tamaño de columna de índice demasiado grande. El tamaño máximo de columna es 767 bytes.
  • # 1071 - La clave especificada era demasiado larga; la longitud máxima de la clave es 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.cnf existente:

[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 instrucción 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 declaración CREATE.

NOTA: Cambiar el índice para limitarlo a los primeros 128 caracteres en contactelimina 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 caracteres de 4 bytes o 128 caracteres de 1 byte.

Esta INSERT declaración 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', '', '');

Puede ver la cantidad de espacio utilizado por 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


63
2017-08-24 19:57



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

Ejemplo de la documentación:

utf8_general_ci también es satisfactorio   para ambos, alemán y francés, excepto   que 'ß' es igual a 's', y no a   'Ss'. Si esto es aceptable para su   aplicación, entonces deberías usar    utf8_general_ci porque es más rápido   De lo contrario, use utf8_unicode_ci porque   es más preciso

Entonces, depende de su base de usuarios esperados y de cuánto necesita correcto clasificación. 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.


40
2017-12-15 08:04



Básicamente, depende de cómo pienses en 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 una cantidad de caracteres UTF-8. Un personaje tiene una representación binaria, ¿por qué necesita saber el idioma que estás usando? Por lo general, las personas construirán bases de datos para sistemas con alcance para sitios multilingües. Este es el objetivo de usar UTF-8 como conjunto de caracteres. Soy un poco puro, pero creo que los riesgos de errores pesan mucho más que la pequeña ventaja que puede obtener al indexar. Cualquier regla relacionada con el lenguaje debe hacerse en un nivel mucho más alto que el DBMS.

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

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


21
2017-12-07 01:42



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

  • utf8_bin: compare cadenas por valor binario de cada personaje en la cuerda

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

a.k.a. deberá hacer que la búsqueda e indexación de los datos sea más rápida / más eficiente / más útil.


11
2017-12-15 07:55



La respuesta aceptada sugiere de manera bastante definitiva el uso de utf8_unicode_ci, y si bien para los nuevos proyectos es genial, quería relatar mi reciente experiencia contraria en caso de que le ahorre a alguien algo de tiempo.

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

Por ejemplo, todas las conexiones de cliente no solo tienen un juego de caracteres predeterminado (tiene sentido para mí) sino también una intercalación predeterminada (es decir, la intercalación siempre será de forma predeterminada 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; de lo contrario, las consultas que usan cadenas de texto pueden fallar cuando su conexión utiliza 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 usar utf8_general_ci debido a la forma en que MySQL maneja los valores predeterminados.


9
2017-07-30 13:20



Para el caso resaltado por Guus, recomendaría usar utf8_unicode_cs (distinción entre mayúsculas y minúsculas, concordancia estricta, ordenar correctamente en su mayor parte) en lugar de utf8_bin (concordancia estricta, ordenamiento incorrecto).

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

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

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

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


6
2018-05-08 13:27



Encontré estas tablas de colación útiles. http://collation-charts.org/mysql60/. Sin embargo, no estoy seguro de cuál es el utf8_general_ci usado.

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


4
2018-04-12 12:34