MySQL8.0排序规则:从旧的排序规则迁移

  • 时间:2017-05-04
  • 分类:数据库/系统
  • 2598 人浏览
[导读]从MySQL 8.0开始,utf8mb4是默认字符集,utf8mb4的默认排序规则是utf8mb4_0900_ai_ci。 MySQL 8.0还附带了utf8mb4字符集的一整套Unicode排序规则。
从MySQL 8.0开始,utf8mb4是默认字符集,utf8mb4的默认排序规则是utf8mb4_0900_ai_ci。 MySQL 8.0还附带了utf8mb4字符集的一整套Unicode排序规则。

这将允许在MySQL中使用完整的Unicode 9.0.0字符集,对于新应用程序,这是一个好消息。 但是旧的应用程序呢? 基于5.7的现有应用程序的字符集使用现代化是多么痛苦?

介绍

首先我们需要了解MySQL在字符集和排序规则方面的工作原理:

每个模式(在MySQL中也称为“数据库”)具有默认字符集和默认排序规则。 因此,在该模式中创建的所有表将默认从模式继承字符集和排序规则。

要更改此,请运行:

每个表还有默认字符集和默认排序规则,它默认由表中的列继承。 要更改表的默认字符集运行:

模式/表的默认字符集/排序规则的更改不会更改任何现有的列或数据。 这两个事实意味着如果您从5.7升级到8.0,升级后不会创建新的模式,MySQL会像以前一样在字符集和排序规则方面表现得如此。

此外,您需要知道,对于MySQL中的每个字符集,都有一个默认的排序规则,每个排序规则都意味着一个字符集。

这意味着在:

col1获取排序规则utf8mb4_0900_ai_ci,col2获取字符集utf8mb4。

因此,为了利用从5.7升级的现有应用程序的新的归类,需要做一个迁移项目。

首先,您需要分析您的方案,并确定哪些列应在utf8mb4中。那么你需要看看这些列的一些属性。

按键

InnoDB表的最大密钥长度为3072字节。此值取决于InnoDB行格式和innodb_page_size,因此如果不使用默认设置运行,则需要检查文档以确定您的限制。

一个utf8mb4字符使用1-4个字节,这意味着作为一个键的char / varchar列的最大长度将为767个字符。因此,如果您有长度大于767个字符的键varchar / char列,您将不得不考虑缩短长度,更改为TEXT或更改InnoDB设置。

指数

当列的排序规则更改时,即使字符集保持不变,也会重新生成索引。对于大数据量,这可能需要相当长的时间,并且表将在索引生成期间被锁定。

独特约束

由于排序规则不同,更改表格的字符集可能会破坏唯一的约束(UNIQUE或PRIMARY KEY等)。最简单的例子是,如果您在MySQL 5.7中创建了一个带有默认设置的字符列,则该列将具有字符集latin1,该字符集又具有默认的排序规则latin1_swedish_ci。在这个整理中,'a'!='å','a'!='ä'和'o'!='ö'。如果此列更改为utf8mb4,它在MySQL 8.0中具有默认的排序规则utf8mb4_0900_ai_ci,则唯一的约束可能会被破坏,因为在此排序规则中,'a'='å'='ä'和'o'=ö'。在这种情况下,您必须考虑您真正想要的归类。对于瑞典数据,一个替代方案可能是utf8mb4_sv_0900_ai_ci,它与latin1_swedish_ci共享许多属性。但正确的解决方案将取决于您的实际数据集和应用程序的需求。

要检查表t中具有唯一约束的列v是否可转换为utf8mb4并进行一些整理,您可以执行以下操作:

如果结果大于零,则必须更改数据或为数据选择更合适的排序规则。
要检查哪个值对导致问题,您可以:

没有PAD归类

新的utf8mb4 Unicode 9.0.0排序规则是无PAD排序规则(与旧的排序规则是PAD SPACE排序规则相反)。 这意味着字符串中的尾随空格对于VARCHAR列将是重要的。 查看哪些MySQL排序是PAD SPACE,哪些不是PAD:

这可能会影响您的应用程序和存储的数据。
小例子
让我们假设从5.7升级后我们有下表:

如果我们对这张表进行检查,我们得到:

如果你只是尝试转换列,你会得到如预期的:

这里有几种可能的方式:

您可以假设这是瑞典数据,并转换为utf8mb4_sv_0900_ai_ci(由于原始文本具有latin1_swedish_ci)或其他合适的排序规则,具体取决于应用程序和数据。
可能是有数据不正确(表格不应该有一个“Örebro”条目,另一个对于“Orebro”)。 在这种情况下,您需要在转换前清理数据。
我们会去替代和清理数据。 那么我们准备好转换了吗?

那是最长的字符个数。 但是这个名字的城市并不常见,所以在我们的例子中,我们将名称的长度减少到最高768个字符,这远远高于我们所需要的。

现在表已经准备好了不是Latin-1的数据,所以可以为东京(东京)插入数据:

结论

这个博客绝对不是一个详尽的迁移手册,而且这些例子可能不会扩展到巨大的数据量,但是我希望在迁移到utf8mb4时可能会遇到问题。

来源:本文为线上采编,如涉及作品内容、版权和其它问题,请及时与本网联系,我们将在第一时间删除!