使用破折号进行 MySQL 字符串匹配

Question

我目前正在将数据从 Windows 上的 MySQL 5.6.41 迁移到 Windows 上的 MySQL 8.0.21。总体而言，迁移非常顺利，但遇到了一些非常令人沮丧的问题。有一张表看起来像这样:

CREATE TABLE `domains` (
`intDomainID` int(11) NOT NULL AUTO_INCREMENT,
`txtDomain` varchar(100) NOT NULL,
`dtDateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`blnTor` int(1) NOT NULL DEFAULT '0',
`txtTLD` varchar(100) NOT NULL,
PRIMARY KEY (`intDomainID`),
UNIQUE KEY `txtDomain` (`txtDomain`)
ENGINE=InnoDB AUTO_INCREMENT=10127897 DEFAULT CHARSET=utf8mb4;

CREATE SCHEMA 是完整的，它是由 Workbench 的“复制到剪贴板”-->“创建模式”功能创建的。
当我使用内置的 Workbench 导出/导入时，导入总是失败，并显示“txtDomain 中的重复值”(此处释义)错误，这很奇怪，因为原始表在该字段上有一个 UNIQUE KEY 约束，因此不能重复，我确认，它发现的重复值不是原始数据库中的重复值。
然后我使用 SELECT ... INTO OUTFILE 倾倒了表，将文件移至新服务器，然后执行 LOAD DATE INFILE .这也因相同的“txtDomain 中的重复值”错误而失败。
然后我删除了 UNIQUE KEY 约束，并重做了 LOAD DATE INFILE .这行得通，数据就在那里。但是，由于“重复”，我无法重新添加 UNIQUE KEY 约束。我调查并发现了这一点:
MySQL 5.6.41查询结果:

MySQL 8.0.21 查询结果:

现在，发生了什么？表定义、数据库、表和字段字符集/排序规则是相同的。我需要那个 UNIQUE KEY 约束回来......
为什么是 http://d­­eepdot35wv­­m­eyd5.onion:80 == http://d­­ee-p---dot35w-v­­m­eyd5.onion:80 ??
如果有帮助，我的导出命令:

SELECT * INTO OUTFILE 'S:\\temp\\domains.txt'
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  FROM domains;

我的导入命令:

LOAD DATA INFILE 'E:\\DB Backup\\ServerMigration\\domains.txt' 
INTO TABLE domains
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n';

整理:
Collat​​ions Old Server: utf8_general_ci [我不记得碰过这个值]
新服务器:utf8mb4_0900_ai_ci【我没碰这个值】
DB old/new 是一样的:utf8mb4_0900_ai_ci
表旧/新相同:utf8mb4_0900_ai_ci
这是原始 TXT 文件在文件系统上的样子:

请注意，如果我将屏幕截图中的其中一个 URL 粘贴到此处，它会神奇地变成“正确”值，而没有破折号:
即: http://deepdot35w­­v­­­m­­­eyd5.onion:80
注2:使用 Notepad++ ，如果我将常规的“破折号”转换为十六进制，则会得到“2D”。但是，如果我从引起问题的 URL 转换一个，我会得到十六进制“C2AD”。所以似乎我正在处理一个奇怪的 unicode 字符而不是破折号？
注3:如果有人想要一个小样本文件，这里是:
https://www.dropbox.com/s/1ssbl95t2jgn2xy/domains_small.zip

Answer 1

有问题的字符是 U+00AD "SOFT HYPHEN"- 一个不可打印的字符，用于表示单词内可能的连字符点。
似乎使用的 COLLATION 在新设置(具有默认排序规则设置的 MySQL 8.0)中处理这些字符的方式与在旧设置(具有默认排序规则设置的 MySQL 5.7)上的处理方式不同:
这些不可打印的字符现在在比较中被忽略。
您可以使用这个简单的 fiddle 来测试差异。比较在 5.6 中为“0”，但在 MySQL 8.0 中为“1” -> https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=a9bd0bf7de815dc14a886c5069bd1a0f
请注意，当未明确指定时，SQL fiddle 也使用默认排序规则配置。
您可以通过为 txtDomain 列设置二进制 UTF-8 排序规则来解决这个问题，无论如何，这实际上是您想要的技术字符串:

CREATE TABLE `domains` (
  `intDomainID` int(11) NOT NULL AUTO_INCREMENT,
  `txtDomain` varchar(100) NOT NULL 
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_binary_ci,
  `dtDateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `blnTor` int(1) NOT NULL DEFAULT '0',
  `txtTLD` varchar(100) NOT NULL,
  PRIMARY KEY (`intDomainID`),
  UNIQUE KEY `txtDomain` (`txtDomain`)
) ENGINE=InnoDB AUTO_INCREMENT=10127897 DEFAULT CHARSET=utf8mb4;

更新:事实证明，旧 (5.6) 和新 (8.0) 设置之间的 COLLATION 必须不同，如 utf8mb4_0900_ai_ci是在 MySQL 8.0 中引入的。旧的排序规则必须是 utf8mb4_general_ci ，在应用时也显示了 MySQL 8.0 中所需的行为。
但是，无论如何，您应该对 URL 等技术字符串使用二进制排序规则。