Dev Faq
8
votes

Migration de MySQL UTF8 sur les problèmes et questions UTF8MB4

im essayant de convertir ma base de données UTF8 MySQL 5.5.30 en UTF8MB4. J'ai examiné cet article https://mathiasbynens.be/notes/mysql-utf8mb4 mais avoir Quelques questions.

J'ai fait ces p> 

    'CREATE TABLE `forum_threads` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
      `description` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
      `createdDate` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
      `createrId` int(10) unsigned DEFAULT NULL,
      `replys` int(10) unsigned NOT NULL DEFAULT ''0'',
      `lastPostUserId` int(10) unsigned DEFAULT NULL,
      `lastPostId` int(10) unsigned DEFAULT NULL,
      `forumId` int(10) unsigned DEFAULT NULL,
      `visits` int(10) unsigned NOT NULL DEFAULT ''0'',
      `lastPostCreated` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
      `lastPostNickName` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
      `createrNickName` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
      `solved` tinyint(1) NOT NULL DEFAULT ''0'',
      `locked` tinyint(1) NOT NULL DEFAULT ''0'',
      `lockedByUserId` int(10) unsigned NOT NULL DEFAULT ''0'',
      `lockedDate` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
      `alteredDate` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
      `alteredUserId` int(10) unsigned DEFAULT NULL,
      `glued` tinyint(1) NOT NULL DEFAULT ''0'',
      `pollId` int(10) unsigned DEFAULT NULL,
      `facebookPostId` bigint(20) DEFAULT NULL,
      `facebookImportedDate` datetime DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `FK_forum_threads_1` (`forumId`),
      KEY `FK_forum_threads_2` (`pollId`),
      KEY `createdDate` (`createdDate`),
      KEY `createrId` (`createrId`),
      KEY `lastPostCreated` (`lastPostCreated`),
      CONSTRAINT `FK_forum_threads_1` FOREIGN KEY (`forumId`) REFERENCES `forum` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=4306 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

'CREATE TABLE `forum` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
  `description` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
  `createdDate` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `threads` int(10) unsigned NOT NULL DEFAULT ''0'',
  `createrId` int(10) unsigned DEFAULT NULL,
  `lastPostUserId` int(10) unsigned DEFAULT NULL,
  `lastThreadId` int(10) unsigned DEFAULT NULL,
  `parentForumId` int(10) unsigned DEFAULT NULL,
  `lastPostNickName` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
  `lastPostCreated` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `lastThreadTitle` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
  `alteredDate` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `alteredUserId` int(10) unsigned DEFAULT NULL,
  `placeOrder` int(10) unsigned NOT NULL DEFAULT ''0'',
  `separator` tinyint(1) NOT NULL DEFAULT ''0'',
  `rightLevel` int(10) unsigned NOT NULL DEFAULT ''1'',
  `createChildForum` tinyint(3) unsigned NOT NULL DEFAULT ''1'',
  `createThreads` tinyint(3) unsigned NOT NULL DEFAULT ''1'',
  PRIMARY KEY (`id`),
  KEY `Index_1` (`id`,`parentForumId`)
) ENGINE=InnoDB AUTO_INCREMENT=375 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

0 commentaires

3 Réponses :

5
votes
  1. Il y a des limites de la taille d'un index. Vous avez reculé dans la limite car UTF8MB4 a besoin jusqu'à 4 octets em> par em> caractère em>, où l'UTF8 a besoin uniquement de la limite de taille de l'index est dans octets em >. li>

    La «solution» est de décider quoi faire à propos de l'index sur la taille. (plus ci-dessous) p>

    2. P> 

    ALTER TABLE t MODIFY col ...
13 commentaires

Merci! Vous parlez d'index mais tout mon index est de simple int? Les Varcharars ne sont utilisés que pour les titres, le nom d'utilisateur et ainsi de suite.

Hmmmm, c'est étrange. Voyons show créer une table bradspelold.games .

Il se plaint de Nom de la clé (nom) , qui est varchar (255)

Et cela devrait être 191 à la place? Je ne comprends pas vraiment à quel point peut être meilleur? Et si j'ai besoin de plus gros?

Sélectionnez Max (char_length (nom) de t - voyez combien de temps vous avez besoin (jusqu'à présent). Je soupçonne que c'est beaucoup en dessous de 191. Si, d'autre part, vous obtenez 255, vous avez déjà tronqué un nom s. S'il semble que vous ayez besoin de plus de 191, déclarez-le aussi grand que nécessaire, puis modifiez l'index vers index (nom (191)) . Ceci est un "index de préfixe"; Cela peut être meilleur que rien.

Merci, je vais vérifier cela. Habituellement 1 octet = 1 Char, alors quand le Varchar dit 255, je pensais que c'était 255 caractères. Avec UTF8MB4, il semble que chaque char soit plus grand? J'ai lu quelque chose à propos de cela dans UFT8, il est 3, donc je peux les pieds 85 caractères dans un champ de 255? Et en UFT8MB4, je ne peux que 63 caractères? Je ne comprends pas vraiment pourquoi j'ai besoin de le rendre plus petit? Cela n'a aucun sens? J'ai demandé plus grand Varcharais j'aurais compris?

Avec UTF8MB4, chaque caractère est compris entre 1 et 4 octets . 1 octet pour l'anglais / ASCII, 2 octets pour les lettres d'accentues européennes, 3 octets pour la plupart des Asie et 4 octets pour certains chinois. Le max (4) est utilisé pour limiter l'utilisation de l'indice. UTF8 est un sous-ensemble de UTF8MB4. UTF8 manque les caractères de 4 octets. 'Abcde' occupe seulement 5 octets.

Je conviens qu'ils auraient dû essayer de soulever la limite d'index lorsqu'elles ont ajouté UTF8MB4. Mais ils ne l'ont pas fait.

Désolé pour le retard, j'ai vérifié et la longueur de charlence maximale du champ Nom est de 248 ans, donc je ne suis nécessaire pour le rendre plus grand. Y a-t-il des problèmes pour simplement le définir à 512?

Tout entre 192 et 64k est pratiquement le même. Donc, 512 irait bien. Avez-vous regardé dans le plan B (se débarrasser de l'indice)? Ou avez-vous trouvé une requête qui semble avoir besoin de cet index? Vous pouvez nous montrer le SELECT, ou vous pouvez simplement utiliser le plan C (Index de préfixe) et espérer qu'il fonctionne assez bien.

Merci, cela a résolu le premier problème, mais je rencontre ensuite le même problème, mais impliquant une autre table. Comment saviez-vous que c'était le nom de la colonne dans les jeux qui n'allait pas? J'ai quelques Varchars dans les autres tables aussi, mais Thay sont de taille 150 et 30. Voir Modifier 2.

J'ai examiné tous les index (y compris unique et primaire); puis assortis les déclarations de colonne. Toute varchar (...) entre 192 et 255 était en difficulté. Nommément jeux.name, mais rien d'autre dans ces 3 ne crée. 150 et 30 est plus petit <= 191, donc ne dépassera pas 767. 2 + 4 * 150 est seulement 602.

Le message d'erreur "13:08:30 ALTER TABLE BRADSPELOLD.GAMES ..." m'a dit de regarder jeux .

4
votes 
DB="database_name"
USER="mysql_user"
PASS="mysql_password"
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
    mysql -p$PASS -u $USER "$DB" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) \
| mysql -p$PASS -u $USER "$DB"

To get the script you work open your command line and use the
following steps:


nano convert_to_utf8mb4.sh
paste the script & save 
sudo chmod 755 convert_to_utf8mb4.sh (in terminal) 
run the script by type ./convert_to_utf8mb4.sh


Yes, collation has been changed!
0 commentaires
2
votes

Ceci est une ancienne question, mais à la suite de certaines des réponses ici 5 ans plus tard, c'est que j'ai découvert une mauvaise idée. Ne modifiez pas la taille de vos champs varchar , vous pouvez endommager vos données et tout casser.

Dans les versions actuelles de MySQL et MarAIDB, ajoutez ceci à votre config et il prend en charge les plus grandes touches nécessaires à l'UTF8MB4

innodb_large_prefix = 1

Je suggère également d'ajouter innodb_file_per_table = 1 innodb_file_format = barracuda

Ensuite, la conversion se produira sans erreurs / avertissements sur la longueur de la clé

0 commentaires

Articles qui pourrait vous intéresser :

Ruby CSV BOM | encodage UTF-8 pour StringIO
Nombre de caractères UTF-8
Copiez un std :: u8string dans une chaîne de style C de caractères utf8
CICS TS (DFHJS2LS): les caractères chinois sont corrompus lorsqu'ils sont reçus dans MAINFRAME à partir de l'outil POSTMAN