将MySQL UTF8迁移到UTF8MB4问题和疑问 | UTF8迁移到UTF8MB4问题和疑问

本文介绍了将MySQL UTF8迁移到UTF8MB4问题和疑问的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试将我的UTF8 MySQL 5.5.30数据库转换为UTF8MB4.我看过这篇文章 https://mathiasbynens.be/notes/mysql-utf8mb4 但有一些问题.

Im trying to convert my UTF8 MySQL 5.5.30 database to UTF8MB4. I have looked at this article https://mathiasbynens.be/notes/mysql-utf8mb4 but have some questions.

我已经完成了这些

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

最后一个是用62个表手动完成的，其中一个给了我警告

The last one was manually done with 62 tables, one of them gave me this warning

这是个问题吗?我该怎么做才能解决它?

下一步是

ALTER TABLE table_name CHANGE column_name column_name
         VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

我不确定该命令，为什么会有2 column_name?
是否仅在VARCHAR(191)列上执行此操作?我不认为我有他们吗?
您是否还知道这样的更多青蒿，这些青蒿解释了为什么和如何获得更多id详细信息?

桌秀游戏

CREATE  TABLE `games` (
        `id` int(10) unsigned NOT NULL DEFAULT \'0\',
        `name` varchar(255) NOT NULL,
        `description` mediumtext,
        `yearPublished` datetime NOT NULL,
        `minPlayers` int(10) unsigned NOT NULL,
        `maxPlayers` int(10) unsigned NOT NULL,
        `playingTime` varchar(127) NOT NULL,
        `grade` double NOT NULL DEFAULT \'0\',
        `updated` datetime NOT NULL,
        `forumParentId` int(10) unsigned DEFAULT \'0\',
        `lastVisited` datetime DEFAULT NULL,
        `inactivatedDate` datetime DEFAULT NULL,
        `bggGrade` double DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `inactivatedDate` (`inactivatedDate`),
        KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8'

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

推荐答案

INDEX的大小有限制.您遇到了限制，因为每个字符 utf8mb4最多需要4个字节，而utf8只需要3个字符.与此同时，INDEX大小限制为字节 >.

There are limits on the size of an INDEX. You bumped into the limit because utf8mb4 needs up to 4 bytes per character, where as utf8 needs only 3. Meanwhile the INDEX size limit is in bytes.

解决方案"是决定如何处理超大索引. (更多内容在下面)

The 'solution' is to decide what to do about the over-sized index. (more below)

ALTER TABLE t CHANGE col col ...

与更合逻辑的

ALTER TABLE t MODIFY col ...

前者允许您更改列的名称，因此在不需要更改名称时可以复制列名称的两个副本.

The former allows you to change the name of the column, hence two copies of the column name when you don't need to change the name.

很有可能您有VARCHAR(255)，它在utf8中占用767个字节(3 * 255 + 2；"2"是长度字段的大小).在4个字节的utf8mb4中，等价于(191)(4 * 191 + 2 = 766；空间不能超过191).

Quite likely you had VARCHAR(255) which takes 767 bytes in utf8 (3*255+2; the "2" is the size of the length field). The equivalent in the 4-byte utf8mb4 would be (191) (4*191+2=766; not room for more than 191).

我还没有看过有关它的文章.我怀疑我刚才说的是大部分需要说的话.

I have not seen an article about it. I suspect that what I just said is most of what needs to be said.

所以...

计划 A :您有foo VARCHAR(255)，它是utf8吗? (现在和将来)其中的数据是否总是少于191个字符?如果是这样，则只需执行ALTER.

Plan A: Do you have foo VARCHAR(255) and it was utf8? Is the data in it always (now and in the future) shorter than 191 characters? If so, then simply do the ALTER.

计划 B :如果您需要的数量超过191，那么您真的需要INDEX吗?可以使用DROP INDEX.

Plan B: If you need more than 191, do you really need the INDEX? DROP INDEX may be an alternative.

计划 C :或者，您可以使用前缀"索引:INDEX(foo(191))，而将其保留为VARCHAR(255).通常，前缀"索引是没有用的，但是您可能有一个适用的用例.

Plan C: Or, you could use a "prefix" index: INDEX(foo(191)), while leaving it VARCHAR(255). Usually "prefix" indexes are useless, but you might have a use case for which it works.

要对此进行进一步讨论，请为所涉及的表格提供SHOW CREATE TABLE，并讨论该特定字段及其INDEX的含义.

To discuss this further, please provide SHOW CREATE TABLE for the table in question, and discuss the meaning of that particular field and its INDEX.

这篇关于将MySQL UTF8迁移到UTF8MB4问题和疑问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！