php - utf8_unicode_ci - utf 8 collation




Qual Γ© o melhor agrupamento para usar no MySQL com PHP? (8)

Eu estou querendo saber se existe uma "melhor" escolha para collation no MySQL para um site geral onde você não tem 100% de certeza do que será inserido? Eu entendo que todas as codificações devem ser as mesmas, como MySQL, Apache, HTML e qualquer coisa dentro do PHP.

No passado, eu defini o PHP como saída em "UTF-8", mas qual agrupamento corresponde ao MySQL? Eu estou pensando que é um dos UTF-8, mas eu usei utf8_unicode_ci , utf8_general_ci e utf8_bin antes.


A principal diferença é a precisão de classificação (ao comparar caracteres na linguagem) e o desempenho. O único especial é o utf8_bin, que serve para comparar caracteres em formato binário.

utf8_general_ci é um pouco mais rápido que utf8_unicode_ci , mas menos preciso (para ordenação). A linguagem específica utf8 encoding (como utf8_swedish_ci ) contém regras de idioma adicionais que as tornam mais precisas para classificar para esses idiomas. Na maior parte do tempo, uso o utf8_unicode_ci (prefiro precisão a pequenas melhorias de desempenho), a menos que tenha uma boa razão para preferir um idioma específico.

Você pode ler mais sobre conjuntos de caracteres unicode específicos no manual do MySQL - http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html


A resposta aceita sugere definitivamente o uso de utf8_unicode_ci, e enquanto para novos projetos isso é ótimo, eu queria relacionar minha recente experiência contrária apenas para o caso de poupar alguém algum tempo.

Como utf8_general_ci é o agrupamento padrão para Unicode no MySQL, se você quiser usar utf8_unicode_ci, você acaba tendo que especificá-lo em muitos lugares.

Por exemplo, todas as conexões de cliente não só têm um conjunto de caracteres padrão (faz sentido para mim), mas também um agrupamento padrão (ou seja, o agrupamento sempre será padronizado para utf8_general_ci para unicode).

Provavelmente, se você usar utf8_unicode_ci para seus campos, seus scripts que se conectam ao banco de dados precisarão ser atualizados para mencionar explicitamente o agrupamento desejado - caso contrário, consultas usando strings de texto podem falhar quando sua conexão estiver usando o agrupamento padrão.

O resultado é que ao converter um sistema existente de qualquer tamanho para Unicode / utf8, você pode acabar sendo forçado a usar utf8_general_ci por causa da maneira como o MySQL lida com os padrões.


Esteja muito, muito ciente desse problema que pode ocorrer ao usar o utf8_general_ci .

O MySQL não fará distinção entre alguns caracteres em instruções select, se o agrupamento utf8_general_ci for usado. Isso pode levar a erros muito desagradáveis ​​- especialmente por exemplo, onde nomes de usuários estão envolvidos. Dependendo da implementação que usa as tabelas de banco de dados, esse problema pode permitir que usuários mal-intencionados criem um nome de usuário correspondente a uma conta de administrador.

Esse problema se expõe no mínimo nas primeiras versões 5.x - não tenho certeza se esse comportamento foi alterado posteriormente.

Não sou DBA, mas, para evitar esse problema, sempre uso o utf8-bin vez de um caso insensível.

O script abaixo descreve o problema por exemplo.

-- 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;


No seu arquivo de upload do banco de dados, adicione a seguinte linha antes de qualquer linha:

SET NAMES utf8;

E seu problema deve ser resolvido.


Os agrupamentos afetam como os dados são classificados e como as sequências são comparadas entre si. Isso significa que você deve usar o agrupamento que a maioria dos usuários espera.

Exemplo da documentation :

utf8_general_ci também é satisfatório tanto para o alemão quanto para o francês, exceto que 'ß' é igual a 's', e não a 'ss'. Se isso for aceitável para sua aplicação, então você deve usar utf8_general_ci porque é mais rápido. Caso contrário, use utf8_unicode_ci porque é mais preciso.

Então - isso depende da sua base de usuários esperada e de quanto você precisa da classificação correta . Para uma base de usuários em inglês, utf8_general_ci deve ser suficiente, para outros idiomas, como o sueco, foram criados agrupamentos especiais.


Para o caso destacado por Guus, sugiro fortemente usar utf8_unicode_cs (case sensitive, strict matching, ordenando corretamente na maior parte das vezes) em vez de utf8_bin (correspondência estrita, ordenação incorreta).

Se o campo se destina a ser pesquisado, em vez de corresponder a um usuário, use utf8_general_ci ou utf8_unicode_ci. Ambos são insensíveis a maiúsculas e minúsculas, um corresponderá casualmente ('ß' é igual a 's' e não a 'ss'). Há também versões específicas de idioma, como utf8_german_ci, onde a correspondência de perda é mais adequada para o idioma especificado.

[Editar - quase 6 anos depois]

Eu não recomendo mais o conjunto de caracteres "utf8" no MySQL e, em vez disso, recomendo o conjunto de caracteres "utf8mb4". Eles correspondem quase inteiramente, mas permitem um pouco mais de caracteres unicode.

Realisticamente, o MySQL deve ter atualizado o conjunto de caracteres "utf8" e respectivos agrupamentos para corresponder à especificação "utf8", mas um conjunto de caracteres separado e respectivos agrupamentos para não impactar a designação de armazenamento para aqueles que já usam seu conjunto incompleto "utf8" .


É melhor usar o conjunto de caracteres utf8mb4 com o agrupamento utf8mb4_unicode_ci .

O conjunto de caracteres, utf8 , suporta apenas uma pequena quantidade de pontos de código UTF-8, cerca de 6% dos caracteres possíveis. utf8 suporta apenas o Basic Multilingual Plane (BMP). Existem 16 outros planos. Cada plano contém 65.536 caracteres. utf8mb4 suporta todos os 17 planos.

O MySQL irá truncar os caracteres UTF-8 de 4 bytes, resultando em dados corrompidos.

O conjunto de caracteres utf8mb4 foi introduzido no MySQL 5.5.3 em 2010-03-24.

Algumas das mudanças necessárias para usar o novo conjunto de caracteres não são triviais:

  • Alterações podem precisar ser feitas no adaptador de banco de dados do aplicativo.
  • As alterações precisarão ser feitas no my.cnf, incluindo a configuração do conjunto de caracteres, o agrupamento e a mudança do innodb_file_format para o Barracuda
  • Instruções SQL CREATE podem precisar incluir: ROW_FORMAT=DYNAMIC
    • DINÂMICO é necessário para índices em VARCHAR (192) e maiores.

NOTA: Mudar para o Barracuda do Antelope , pode requerer o reinício do serviço MySQL mais de uma vez. innodb_file_format_max não muda até que o serviço MySQL seja reiniciado para: innodb_file_format = barracuda .

O MySQL usa o antigo formato de arquivo Antelope InnoDB. Barracuda suporta formatos de linha dinâmicos, que serão necessários se você não deseja acertar os erros de SQL para criar índices e chaves depois de alternar para o conjunto de caracteres: utf8mb4

  • # 1709 - O tamanho da coluna do índice é muito grande. O tamanho máximo da coluna é de 767 bytes.
  • # 1071 - A chave especificada era muito longa; o tamanho máximo da chave é de 767 bytes

O seguinte cenário foi testado no MySQL 5.6.17: Por padrão, o MySQL é configurado assim:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope

Pare seu serviço MySQL e adicione as opções ao seu 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

Exemplo de instrução 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;
  • Você pode ver o erro # 1709 gerado para INDEX contact_idx (contact) se ROW_FORMAT=DYNAMIC for removido da instrução CREATE.

NOTA: Alterar o índice para limitar os primeiros 128 caracteres no contact elimina a necessidade de usar o Barracuda com ROW_FORMAT=DYNAMIC

INDEX contact_idx (contact(128)),

Observe também: quando diz que o tamanho do campo é VARCHAR(128) , isso não é 128 bytes. Você pode usar caracteres de 128, 4 bytes ou 128 caracteres de 1 byte.

Esta instrução INSERT deve conter o caractere 'poo' de 4 bytes na linha 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πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©πŸ’©', '', '');

Você pode ver a quantidade de espaço usado pela last coluna:

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
+--------------------+---------------------+

No seu adaptador de banco de dados, você pode querer definir o conjunto de caracteres e o agrupamento para sua conexão:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

No PHP, isso seria definido para: \PDO::MYSQL_ATTR_INIT_COMMAND

Referências:





collation