utf8_unicode_ci Qual é o melhor agrupamento para usar no MySQL com PHP?




utf 8 collation (9)

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.

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.


Na verdade, você provavelmente quer usar utf8_unicode_ci ou utf8_general_ci .

  • utf8_general_ci classifica retirando todos os sotaques e classificando como se fosse ASCII
  • utf8_unicode_ci usa a ordem de classificação Unicode, portanto, ordena corretamente em mais idiomas

No entanto, se você estiver usando isso apenas para armazenar texto em inglês, isso não deverá ser diferente.


Para informações textuais UTF-8, você deve usar utf8_general_ci porque ...

  • utf8_bin : compara strings pelo valor binário de cada caractere na string

  • utf8_general_ci : compare strings usando regras gerais de linguagem e usando comparações sem distinção entre maiúsculas e minúsculas

O que se deveria fazer é procurar e indexar os dados mais rapidamente / mais eficientes / mais úteis.


Essencialmente, depende de como você pensa em uma string.

Eu sempre uso o utf8_bin por causa do problema destacado pelo Guus. Na minha opinião, no que diz respeito ao banco de dados, uma string ainda é apenas uma string. Uma string é um número de caracteres UTF-8. Um personagem tem uma representação binária, então por que ele precisa conhecer a linguagem que você está usando? Normalmente, as pessoas estarão construindo bancos de dados para sistemas com o escopo de sites multilíngues. Este é o objetivo de usar o UTF-8 como um conjunto de caracteres. Eu sou um pouco purista, mas acho que os riscos do bug superam em muito a pequena vantagem que você pode obter na indexação. Quaisquer regras relacionadas a idiomas devem ser feitas em um nível muito mais alto do que o DBMS.

Em meus livros, "valor" nunca deveria em um milhão de anos ser igual a "valeu".

Se eu quiser armazenar um campo de texto e fazer uma busca sem distinção entre maiúsculas e minúsculas, usarei as funções de string MYSQL com funções PHP como LOWER () e a função strtolower () do php.


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" .


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;


É 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:


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.





collation