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


Je me demande s'il y a un "meilleur" choix pour la collation dans MySQL pour un site général où vous n'êtes pas sûr à 100% de ce qui sera écrit? Je comprends que tous les encodages doivent être les mêmes, comme MySQL, Apache, le HTML et tout ce qui est en PHP.

Dans le passé, j'ai mis PHP à sortir en "UTF-8", mais quel collation correspond-il à MySQL? Je pense que c'est l'un des UTF-8, mais j'ai utilisé utf8_unicode_ci, utf8_general_ci, et utf8_bin avant.


648
2017-12-15 07:48


origine


Réponses:


La principale différence est la précision de tri (en comparant les caractères dans la langue) et la performance. Le seul spécial est utf8_bin qui est pour comparer des caractères au format binaire.

utf8_general_ci est un peu plus rapide que utf8_unicode_ci, mais moins précis (pour le tri). le codage utf8 de langue spécifique (tel que utf8_swedish_ci) contiennent des règles de langage supplémentaires qui les rendent les plus précis pour trier ces langues. La plupart du temps j'utilise utf8_unicode_ci (Je préfère la précision aux petites améliorations de performance), sauf si j'ai une bonne raison de préférer une langue spécifique.

Vous pouvez en savoir plus sur des jeux de caractères Unicode spécifiques dans le manuel MySQL - http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html


544
2017-12-15 07:58



Soyez très, très conscient de ce problème qui peut survenir lors de l'utilisation utf8_general_ci.

MySQL ne fera pas la distinction entre certains caractères dans les instructions select, si le utf8_general_ci la collation est utilisée. Cela peut conduire à des bogues 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 à des utilisateurs malveillants de créer un nom d'utilisateur correspondant à un compte d'administrateur.

Ce problème s'expose à tout le moins dans les premières versions 5.x - je ne suis pas sûr si ce comportement a changé plus tard.

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

Le script ci-dessous décrit le problème par l'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;

108
2018-06-13 11:02



En fait, vous voulez probablement utiliser utf8_unicode_ci ou utf8_general_ci.

  • utf8_general_ci trie en enlevant tous les accents et en les triant comme s'il s'agissait d'ASCII
  • utf8_unicode_ci utilise l'ordre de tri Unicode, de sorte qu'il trie correctement dans plusieurs langues

Cependant, si vous ne l'utilisez que pour stocker du texte en anglais, ceux-ci ne doivent pas différer.


103
2017-12-15 08:02



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

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

MySQL tronquera les caractères UTF-8 de 4 octets résultant en des données corrompues.

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

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

  • Des modifications peuvent devoir être apportées à votre adaptateur de base de données d'application.
  • Des changements devront être apportés à my.cnf, y compris la définition du jeu de caractères, le classement et le passage de innodb_file_format à Barracuda
  • Les instructions SQL CREATE peuvent devoir inclure: ROW_FORMAT=DYNAMIC
    • DYNAMIC est requis pour les index sur VARCHAR (192) et plus.

REMARQUE: Passer à Barracuda de Antelope, peut nécessiter de redémarrer le service MySQL plus d'une fois. innodb_file_format_max ne change pas qu'après le redémarrage du service MySQL: innodb_file_format = barracuda.

MySQL utilise l'ancien Antelope Format de fichier InnoDB. Barracuda prend en charge les formats de lignes dynamiques, dont vous aurez besoin si vous ne souhaitez pas utiliser les erreurs SQL pour créer des index et des clés après le basculement vers le jeu de caractères: utf8mb4

  • # 1709 - Taille de la colonne d'index trop grande. La taille maximale des colonnes 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.cnf existant:

[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 l'instruction CREATE.

REMARQUE: modification de l'index pour limiter aux 128 premiers caractères contactélimine l'obligation d'utiliser Barracuda avec ROW_FORMAT=DYNAMIC 

INDEX contact_idx (contact(128)),

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

Ce INSERT déclaration devrait contenir le caractère «caca» 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 pour votre connexion:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

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

Les références:


63
2017-08-24 19:57



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 attendu par la plupart de vos utilisateurs.

Exemple de la Documentation:

utf8_general_ci est également satisfaisant   pour l'allemand et le français, sauf   que «ß» est égal à «s», et non   '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   c'est plus précis.

Donc - cela dépend de votre base d'utilisateurs prévue et de combien vous avez besoin correct tri. Pour une base d'utilisateurs anglais, utf8_general_ci devrait suffire, pour d'autres langues, comme le suédois, des classements spéciaux ont été créés.


40
2017-12-15 08:04



Essentiellement, cela dépend de ce que vous pensez d'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 personnage 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 possibilité de sites multilingues. C'est tout l'intérêt d'utiliser UTF-8 comme jeu de caractères. Je suis un peu puriste mais je pense que le bug risque de l'emporter largement sur le léger avantage que vous pouvez obtenir sur l'indexation. Toutes les règles liées à la langue doivent être effectuées à un niveau beaucoup plus élevé que le SGBD.

Dans mes livres, la "valeur" ne devrait jamais être égale à "valúe" dans un million d'années.

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 ().


21
2017-12-07 01:42



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

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

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

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


11
2017-12-15 07:55



La réponse acceptée suggère de façon assez définitive l'utilisation de utf8_unicode_ci, et alors que pour les nouveaux projets, c'est génial, je voulais raconter ma récente expérience contraire juste au cas où cela ferait gagner du temps à quelqu'un.

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

Par exemple, toutes les connexions clientes ont non seulement un jeu de caractères par défaut (ce qui est logique pour moi), mais également un classement par défaut (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 indiquer 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 lorsque vous convertissez un système existant de n'importe quelle taille en Unicode / utf8, vous pouvez être forcé d'utiliser utf8_general_ci en raison de la façon dont MySQL gère les valeurs par défaut.


9
2017-07-30 13:20



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

Si le champ est destiné à être recherché, contrairement à un utilisateur, utilisez utf8_general_ci ou utf8_unicode_ci. Les deux sont insensibles à la casse, l'un correspondra difficilement («ß» est égal à «s», et non à «ss»). Il existe également des versions spécifiques à la langue, comme utf8_german_ci où la correspondance de perte est plus adaptée à 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 manière réaliste, MySQL aurait dû mettre à jour le jeu de caractères "utf8" et les classements respectifs pour correspondre à la spécification "utf8", mais plutôt un jeu de caractères séparé et des classements respectifs pour ne pas affecter la désignation de stockage pour ceux qui utilisent déjà leur jeu de caractères "utf8" incomplet .


6
2018-05-08 13:27