Quel est le meilleur classement à utiliser pour MySQL avec PHP? [fermé]

Être très, très conscient de ce problème qui peut se produire lors de l'utilisation de utf8_general_ci.

MySQL ne distinguera pas certains caractères dans les instructions select, si le classement utf8_general_ci est utilisé. Cela peut conduire à des bugs très désagréables-en particulier par exemple, où les noms d'utilisateur sont impliqués. Selon l'implémentation qui utilise les tables de base de données, ce problème pourrait permettre aux utilisateurs malveillants de créer un nom d'utilisateur correspondant à un compte administrateur.

Ce problème s'expose à tout le moins au début 5.X versions-Je ne suis pas sûr si ce comportement a changé plus tard.

Je ne suis pas DBA, mais pour éviter ce problème, je vais toujours avec utf8-bin au lieu d'un problème insensible à la casse.

Le script ci-dessous décrit le problème par exemple.

-- 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 Fait, vous voudrez probablement utiliser utf8_unicode_ci ou utf8_general_ci.

• utf8_general_ci trie en enlevant tous les accents et en triant comme S'il s'agissait D'ASCII
• utf8_unicode_ci utilise L'ordre de tri Unicode, donc il trie correctement dans plus de langues

Cependant, si vous utilisez uniquement ceci pour stocker du texte anglais, ceux-ci ne devraient pas différer.

Il est préférable d'utiliser le jeu de caractères utf8mb4, avec le classement utf8mb4_unicode_ci.

Le jeu de caractères, utf8, ne prend en charge qu'une petite quantité de points de code UTF-8, soit environ 6% des caractères possibles. utf8 ne prend en charge que le plan multilingue de base (BMP). Il y a 16 autres avions. Chaque plan contient 65 536 caractères. utf8mb4 prend en charge les 17 plans.

MySQL tronquera les caractères UTF-8 de 4 octets, ce qui entraînera des données corrompues.

Le jeu de caractères utf8mb4 a été introduit dans MySQL 5.5.3 sur 2010-03-24.

Certains des changements requis pour utiliser le nouveau jeu de caractères ne sont pas négligeables:

• des modifications peuvent être nécessaires dans votre adaptateur de base de données d'application.
• Modifications devront être apportées à mon.cnf, y compris la définition du jeu de caractères, le classement et la commutation innodb_file_format en Barracuda
• les instructions SQL CREATE peuvent devoir inclure: ROW_FORMAT=DYNAMIC
  • DYNAMIC est requis pour les index sur VARCHAR (192) et plus grandes.

Remarque: passer à Barracuda à partir de Antelope peut nécessiter le redémarrage du service MySQL plus d'une fois. {[16] } ne change qu'après le redémarrage du service MySQL à: innodb_file_format = barracuda.

MySQL utilise l'ancien format de fichier Antelope InnoDB. Barracuda prend en charge les formats de lignes dynamiques, dont vous aurez besoin si vous ne voulez pas frapper les erreurs SQL pour créer des index et des clés après avoir passé au jeu de caractères: utf8mb4

• # 1709-Taille de la colonne D'Index aussi grand. La taille maximale de la colonne est de 767 octets.
• # 1071-la clé spécifiée était trop longue; la longueur maximale de la clé est de 767 octets

Le scénario suivant a été testé sur MySQL 5.6.17: Par défaut, MySQL est configuré comme ceci:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope

Arrêtez votre service MySQL et ajoutez les options à votre my existant.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

Exemple D'instruction 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;

• Vous pouvez voir l'erreur # 1709 générée pour INDEX contact_idx (contact) si ROW_FORMAT=DYNAMIC est supprimé de la création déclaration.

Remarque: changer l'index pour limiter aux 128 premiers caractères sur contact élimine l'exigence d'utiliser Barracuda avec ROW_FORMAT=DYNAMIC

INDEX contact_idx (contact(128)),

Notez également: quand il dit que la taille du champ est VARCHAR(128), ce n'est pas 128 octets. Vous pouvez utiliser ont 128, 4 caractères d'octet ou 128, 1 caractères d'octet.

Cette instruction INSERT doit contenir le caractère 'poo' de 4 octets dans la ligne 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', '', '');

Vous pouvez voir la quantité d'espace utilisée par le last colonne:

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

Dans votre adaptateur de base de données, vous pouvez définir le jeu de caractères et le classement de votre connexion:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

En PHP, cela serait défini pour: \PDO::MYSQL_ATTR_INIT_COMMAND

Références:

• Mysql 5.6 Manuel de référence: limites sur les Tables InnoDB
• comment prendre en charge Unicode complet dans les bases de données MySQL

Les classements affectent la façon dont les données sont triées et comment les chaînes sont comparées les unes aux autres. Cela signifie que vous devez utiliser le classement à la plupart de vos utilisateurs attendent.

Exemple de la documentation :

utf8_general_ci est également satisfaisant en allemand et en français, à l'exception de que ‘ß’ est égal à ‘s’, et de ne pas le "ss". Si cela est acceptable pour votre application, alors vous devriez utiliser utf8_general_ci parce que c'est plus rapide. Sinon, utilisez utf8_unicode_ci car il est plus précis.

Donc-cela dépend de votre base d'utilisateurs attendue et de combien vous avez besoin de trier correctement. Pour une base d'utilisateurs en anglais, utf8_general_ci devrait suffire, pour d'autres langues, comme le suédois, des collations spéciales ont été créées.

Essentiellement, cela dépend de la façon dont vous pensez à une chaîne.

J'utilise toujours utf8_bin à cause du problème mis en évidence par Guus. À mon avis, en ce qui concerne la base de données, une chaîne est toujours juste une chaîne. Une chaîne est un nombre de caractères UTF-8. Un caractère a une représentation binaire alors pourquoi a-t-il besoin de connaître la langue que vous utilisez? Habituellement, les gens vont construire des bases de données pour les systèmes avec la portée pour les sites multilingues. C'est le point de l'ensemble de utiliser UTF-8 comme jeu de caractères. Je suis un peu puriste mais je pense que les risques de bogue l'emportent largement sur le léger avantage que vous pouvez obtenir sur l'indexation. Toutes les règles liées à la langue doivent être faites à un niveau beaucoup plus élevé que le SGBD.

Dans mes livres "valeur" ne devrait jamais dans un million d'années égal à "valúe".

Si je veux stocker un champ de texte et faire une recherche insensible à la casse, j'utiliserai des fonctions de chaîne MYSQL avec des fonctions PHP telles que LOWER () et la fonction PHP strtolower ().

Pour les informations textuelles UTF-8, vous devez utiliser utf8_general_ci parce que...

• utf8_bin: comparer des chaînes par le valeur binaire de chaque caractère dans la chaîne

• utf8_general_ci: comparer les chaînes en utilisant des règles linguistiques générales et utilisation de comparaisons insensibles à la casse

A. k. a. Il devrait rendre la recherche et l'indexation des données plus rapides / plus efficaces/plus utiles.

La réponse acceptée suggère assez définitivement d'utiliser utf8_unicode_ci, et tandis que pour les nouveaux projets c'est génial, je voulais raconter ma récente expérience contraire au cas où cela économiserait du temps.

Parce que utf8_general_ci est le classement par défaut pour Unicode dans MySQL, si vous voulez utiliser utf8_unicode_ci alors vous finissez par devoir le spécifier dans un lot de lieux.

Par exemple, toutes les connexions client ont non seulement un jeu de caractères par défaut (cela a du sens pour moi) mais aussi un classement par défaut (c'est-à-dire que le classement sera toujours par défaut utf8_general_ci pour unicode).

Probablement, si vous utilisez utf8_unicode_ci pour vos champs, vos scripts qui se connectent à la base de données devront être mis à jour pour mentionner explicitement le classement souhaité-sinon les requêtes utilisant des chaînes de texte peuvent échouer lorsque votre connexion utilise le classement par défaut.

Le résultat est que lors de la conversion d'un système existant de n'importe quelle taille en Unicode / utf8, vous pouvez finir par être obligé de utilisez utf8_general_ci en raison de la façon dont MySQL gère les valeurs par défaut.

Pour le cas mis en évidence par Guus, je suggère fortement d'utiliser utf8_unicode_cs (sensible à la casse, correspondance stricte, ordonnant correctement pour la plupart) au lieu de utf8_bin (correspondance stricte, ordre incorrect).

Si le champ est destiné à être recherché, par opposition à matched pour un utilisateur, utilisez utf8_general_ci ou utf8_unicode_ci. Les deux sont insensibles à la casse, on va perdre la correspondance ('ß’ est égal à 's' , et non à’ss'). Il existe également des versions spécifiques à la langue, comme utf8_german_ci où la correspondance lose est plus appropriée pour la langue spécifiée.

[Modifier-près de 6 ans plus tard]

Je ne recommande plus le jeu de caractères" utf8 " sur MySQL, et recommande plutôt le jeu de caractères "utf8mb4". Ils correspondent presque entièrement, mais permettent un peu (beaucoup) plus de caractères unicode.

De façon réaliste, MySQL aurait dû mettre à jour le jeu de caractères "utf8" et les collations respectives pour correspondre à la spécification" utf8", mais à la place, un caractère séparé définir et collations respectives pour ne pas affecter la désignation de stockage pour ceux qui utilisent déjà leur jeu de caractères "utf8" incomplet.

J'ai trouvé ces tableaux de classement utiles. http://collation-charts.org/mysql60/. Je ne suis pas sûr de savoir quel est le utf8_general_ci utilisé.

Par exemple, voici le graphique pour utf8_swedish_ci. Il montre les caractères qu'il interprète comme identiques. http://collation-charts.org/mysql60/mysql604.utf8_swedish_ci.html

Dans votre fichier de téléchargement de base de données, ajoutez la ligne suivante avant toute ligne:

SET NAMES utf8;

Et votre problème devrait être résolu.