网站 视觉上,wordpress和wamp,漯河市万金镇网站建设,泗阳县住房和城乡建设局网站#x1f4ab;《博主介绍》#xff1a;✨又是一天没白过#xff0c;我是奈斯#xff0c;从事IT领域✨ #x1f4ab;《擅长领域》#xff1a;✌️擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控#xff1b;并对SQLserver、NoSQL(… 《博主介绍》✨又是一天没白过我是奈斯从事IT领域✨ 《擅长领域》✌️擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控并对SQLserver、NoSQL(MongoDB)有了解✌️ 大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注 如标题所示本篇文章是记录一下使用mysqldump导出的SQL文件进行导入时的报错Unknown collation: utf8mb4_0900_ai_ci。众所周知mysqldump是MySQL的逻辑迁移工具它能够将数据库的内容以 SQL语句的形式导出为文件 为数据库的迁移、备份和恢复工作提供了极大的便利。 但mysqldump导入时很鸡肋真的让人有些哭笑不得就是使用mysql命令进行导入的所以 没有办法看到每张表的导入情况 也没有办法指定某个表、某个库的导入导入一个mysqldump导出的sql文件然后它默默地在那边工作你除了等待和祈祷只能 看看后台这个导入进程还是否存在 几乎什么都做不了。虽然mysqldump导入有些“鸡肋”但我们也不要太过苛求。毕竟它为我们提供了基本的数据库备份和恢复功能这已经足够我们在很多场景下使用了。如果真的需要更高级的功能那也不妨考虑一下那些收费的商业软件它们或许能给你带来更好的体验。 那么回到正题 这个报错当时是研发人员需要将MySQL8.0中的某个生产库的全量数据导入到MySQL5.7时抛出的错误属于降版本导入数据。 其实这个需求挺奇葩的明明已经是高版本MySQL8.0了MySQL8.0不管是性能上还是新功能上都要比MySQL5.7强为啥还需要降版本导入到MySQL5.7中呢原因是因为目前其他项目上有用MySQL8.0的还有尝试用最新的MySQL8.1等版本的但当前需要协助导入数据的项目上还用的是MySQL5.7这个项目要拉其他项目上的数据来进行测试如果不同版本上的数据都单独创建一个对应版本实例的话使用MySQL自带的federated功能联调就比较麻烦并且最要命的是使用federated功能只支持select、insert、update、delete、truncate和索引不支持ALTER TABLE或任何直接影响表结构的数据定义语句执行ALTER TABLE报错ERROR 1031 (HY000): Table storage engine for liu_mysqloltp_ywcs_tb88 doesnt have this option所以最好的办法就是统一将不同版本上的数据统一导入到一个版本中。大家都知道使用mysqldump升版本的导入导出没问题也就是支持向上兼容但mysqldump导出的SQL文件不支持降版本导入所以就需要对导出的SQL文件进行一下处理。 问题描述 在MySQL8.0环境中导出某个生产库的全部对象执行如下mysqldump导出语句 [rootmgr1 ~]# mysqldump -uroot -p123456 --set-gtid-purgedOFF --single-transaction --master-data2 --flush-logs --routines --events --databases liudbywcs --log-errormysqldump_liudbywcs_3306_err.log --skip-add-locks --socket/liu_data/mysql8.0/data/3306/liu.sock mysqldump_liudbywcs_3306.sql 然后将MySQL8.0中mysqldump导出的SQL文件通过mysql客户端命令导入到MySQL5.7环境中 [rootha1 ~]# mysql -uroot -p123456 --socket/liu_data/mysql5.7/data/3306/liu.sock --force mysqldump_liudbywcs_3306.sql 在MySQL5.7环境中通过mysql客户端命令导入时报错信息如下 mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1273 (HY000) at line 28: Unknown collation: utf8mb4_0900_ai_ci
ERROR 1253 (42000) at line 28: COLLATION utf8_general_ci is not valid for CHARACTER SET utf8mb4
ERROR 1049 (42000) at line 30: Unknown database liudbywcs
ERROR 1046 (3D000) at line 36: No database selected
ERROR 1046 (3D000) at line 39: No database selected
ERROR 1046 (3D000) at line 53: No database selected
ERROR 1046 (3D000) at line 54: No database selected
............... 解决办法 分析上面两个报错Unknown collation: utf8mb4_0900_ai_ci和COLLATION utf8_general_ci is not valid for CHARACTER SET utf8mb4。 报错一Unknown collation: utf8mb4_0900_ai_ci。在MySQL5.7中不支持这个字符集排序规则collation所以需要替换成MySQL5.7支持的utf8mb4_general_ci字符集排序规则collation。报错二COLLATION utf8_general_ci is not valid for CHARACTER SET utf8mb4。utf8_general_ci 不适用于 utf8mb4 字符集应该使用 utf8mb4_general_ci 或 utf8mb4_unicode_ci 排序规则来避免该错误。 如果MySQL5.7的默认字符集是utf8那么将导出来的SQL文件的字符集和排序规则修改如下 sed -i s/utf8mb4_0900_ai_ci/utf8_general_ci/g SQL文件
sed -i s/utf8mb4_general_ci/utf8_general_ci/g SQL文件
sed -i s/utf8mb4/utf8/g SQL文件 如果MySQL5.7的默认字符集是utf8mb4那么将导出来的SQL文件的字符集和排序规则修改如下 sed -i s/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g SQL文件 扩展MySQL的字符集和排序规则 字符集 字符集Character Set在 MySQL 中定义了如何将字符映射到字节。它是决定如何存储和表示文本数据的基本规范。字符集与排序规则Collation密切相关但它们的作用不同。字符集负责处理字符的存储和传输而排序规则则定义了字符的比较和排序方式。 MySQL5.7支持的字符集和字符集默认的排序规则可以参考MySQL5.7的官方文档学习支持41种字符集MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL MySQL8.0支持的排序规则达272个之多详细可以参考MySQL8.0的官方文档学习MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL 那么下面梳理一下排序规则的重要知识点 一、字符集的作用 1、字符的存储和编码字符集决定了字符如何转换成字节序列进行存储。不同的字符集采用不同的编码方式来表示字符。例如 UTF-8将字符映射到1到4个字节之间。它支持全球绝大多数语言并且是网络传输中常用的编码。Latin1ISO-8859-1每个字符用1个字节表示通常用于西欧语言。UTF-16通常使用2个字节或4个字节来表示字符适合用于表示亚洲字符集。 2、支持不同语言和符号 字符集允许数据库支持多种语言的字符。例如UTF-8字符集可以存储几乎所有语言的字符包括汉字、阿拉伯字母、日文字符等。选择合适的字符集对于支持多语言的应用程序至关重要。 3、数据存储和读取的一致性 字符集确保数据在存储和读取时的一致性。如果数据库和应用程序使用不匹配的字符集可能会导致乱码或数据损坏。 4、跨平台数据交换 使用标准化字符集如 UTF-8有助于不同系统之间的数据交换。这样无论数据如何迁移字符都能正确显示。 5、节省存储空间 不同的字符集在存储上有所不同。比如 latin1 每个字符占用 1 字节而 utf8 占用的字节数则与字符内容的复杂性有关可能是 1 到 4 个字节。在存储英文等简单字符时使用 latin1 可以节省存储空间但如果有多语言需求使用 utf8 或 utf8mb4 可以更好地支持国际化。 二、常见的字符集 latin1ISO-8859-1这是最常见的西欧语言字符集之一每个字符占用 1 个字节。适合处理简单的西欧语言字符如英语、西班牙语、法语等。utf8这个字符集可以表示全球几乎所有的字符支持从 1 到 3 字节的编码。对于大多数现代应用尤其是需要支持多语言的系统utf8 是非常常用的字符集。utf8mb4它是 utf8 的超集支持更多的字符例如 emoji 表情和某些少数民族的字符。utf8mb4 使用 1 到 4 字节存储字符能够存储所有 Unicode 字符。utf16这个字符集使用 2 或 4 个字节表示字符适用于需要表示大量不同语言字符的情况。gbk 和 gb2312这些是用于中文字符的字符集在中文环境中较为常见。gb2312 是较旧的字符集支持简体中文而 gbk 是对 gb2312 的扩展支持更多的中文字符。big5这个字符集用于繁体中文主要在台湾和香港地区使用。 三、字符集与排序规则的关系 字符集和排序规则是密切相关的。在 MySQL 中字符集定义了如何存储和表示字符而排序规则定义了如何比较和排序字符。每个字符集通常会有一个默认的排序规则。例如 utf8 字符集的默认排序规则是 utf8_general_ci其中 ci 代表“不区分大小写”。utf8mb4 字符集的默认排序规则是 utf8mb4_general_ci也是不区分大小写。需要注意MySQL5.7中utf8mb4默认排序规则是 utf8mb4_general_ci而在MySQL8.0中默认排序规则则成了 utf8mb4_0900_ai_ci。 可以在创建数据库、表、列时单独指定字符集和排序规则。排序规则影响的是比较、匹配、排序等操作而字符集影响的是字符的存储。 字符集的排序规则 排序规则Collation在 MySQL 中的作用主要是决定如何对字符进行 比较和排序 。并且排序规则与字符集密切相关但排序规则的重点在于字符的比较方式尤其是如何在查询中对字符进行排序、查找和匹配。 MySQL5.7支持的排序规则达222个之多详细可以参考MySQL5.7的官方文档学习 MySQL :: MySQL 5.7 Reference Manual :: 10.10 Supported Character Sets and Collations MySQL8.0支持的排序规则达272个之多详细可以参考MySQL8.0的官方文档学习MySQL :: MySQL 8.0 Reference Manual :: 12.10 Supported Character Sets and Collations 那么下面梳理一下排序规则的重要知识点 一、排序规则的作用 1、字符比较排序规则定义了字符在查询时如何进行比较。对于同一个字符集不同的排序规则可以影响字符比较的行为尤其在大小写、重音符号、语言等方面的处理。例如 utf8_general_ci ci 代表不区分大小写Case Insensitive即在比较时A 和 a 被认为是相等的。utf8_bin bin 代表二进制比较即字符的二进制值完全一致才认为相等这意味着 A 和 a 是不同的字符。 2、字符排序排序规则定义了字符如何排序。例如某些语言的字符可能会根据特定的语言规则进行排序而不是按照字母的顺序排列。例如 utf8_unicode_ci使用 Unicode 字符集进行排序和比较遵循 Unicode 排序规则这通常会考虑语言差异和字符的实际顺序。utf8_general_ci这是一个比较简单的排序规则适用于大多数情况下的字符排序但可能不会考虑所有语言的细微差别。 3、语言和文化敏感性排序规则会根据特定语言的语法进行排序。例如德语中的 ß 字符在某些排序规则中可能会与 ss 组合字符视为相等。例如 utf8_spanish_ci这是西班牙语的排序规则它会遵循西班牙语的特定排序规则来比较字符。 4、大小写敏感性排序规则决定了字符比较时是否区分大小写。 ci不区分大小写例如 a 和 A 被认为相等。cs区分大小写例如 a 和 A 被认为不同。 5、重音符号敏感性一些排序规则区分带有重音符号的字符与没有重音符号的字符另一些则不区分。 ai不区分重音符号例如 é 和 e 被认为相等。as区分重音符号例如 é 和 e 被认为不同。 二、排序规则的常见类型 1、区分大小写Case Sensitivity ciCase Insensitive不区分大小写在比较时 A 和 a 被认为相同。csCase Sensitive区分大小写在比较时 A 和 a 被认为不同。 2、区分重音符号Accent Sensitivity aiAccent Insensitive不区分重音符号在比较时 é 和 e 被认为相同。asAccent Sensitive区分重音符号在比较时 é 和 e 被认为不同。 3、二进制比较 binBinary二进制比较对字符进行字节级别的比较 A 和 a 被认为不同。 MySQL5.7(含)之后的版本一般都采用utf8mb4字符集它是 utf8 的超集支持更多的字符存储。对于排序规则而言采用默认的即可但如果业务表有特殊要求根据要求设置即可。
