关于Unicode和排序规则，如何使MySQL像SQLite那样处理字符串？

Question

我已经在MySQL文档和其他地方研究这个问题好几个小时了，但是仍然找不到令人满意的解决方案。问题是：

-

例如，以下内容在SQLite中工作得很好：

CREATE TABLE `dummy` (`key` VARCHAR(255) NOT NULL UNIQUE);

INSERT INTO `dummy` (`key`) VALUES ('one');
INSERT INTO `dummy` (`key`) VALUES ('one ');
INSERT INTO `dummy` (`key`) VALUES ('One');
INSERT INTO `dummy` (`key`) VALUES ('öne');

SELECT * FROM `dummy`;

但是，在MySQL中，有以下设置：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin

以及以下CREATE DATABASE声明：

CREATE DATABASE `dummydb` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_bin;

它在第二个INSERT上仍然失败。

我希望字符串列声明尽可能简单，SQLite的TEXT是理想的。VARBINARY 看上去像是的方式，但我仍然想听听您对其他(可能是better optionsE 220)的看法。

增编：SHOW CREATE TABLE dummy输出为

mysql> SHOW CREATE TABLE dummy;
+-------+-----------------------------------------------------
| Table | Create Table                                        
+-------+-----------------------------------------------------
| dummy | CREATE TABLE `dummy` (
  `key` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------
1 row in set (0.00 sec)

Answer 1

MySQL希望在执行INSERT和SELECT时转换字符串。转换是在声明客户端拥有的内容和声明要存储的列之间进行的。

避免这种情况的唯一方法是使用VARBINARY和BLOB，而不是使用VARCHAR和TEXT。

COLLATION utf8mb4_bin的使用并不能避免与CHARACTER SET utf8mb4的转换；它只是说WHERE和ORDER BY应该比较位数，而不是处理重音和大小写折叠。

请记住，CHARACTER SET utf8mb4是一种编码文本的方法；COLLATION utf8mb4_*是比较编码中文本的规则。_bin很简单。

UNIQUE涉及对相等性的比较，因此是COLLATION。在大多数utf8mb4排序规则中，3(没有空格)将比较相等。utf8mb4_bin将把这3种情况视为不同。utf8mb4_hungarian_ci对待one=One>öne。

尾随空格由列的数据类型(VARCHAR或其他)控制。最新版本甚至有一个是否考虑尾随空间的设置。

Answer 2

问题中所示的方法(大多数情况下)在MySQL中应该工作得很好，原因如下：

1. 排序规则(不要与编码混淆)是定义如何排序和比较字符的集合或规则，通常用于从文化角度在数据库级别复制用户期望(如果我搜索cafe，也希望找到café )。
2. 排序规则在唯一约束条件下起着重要的作用，因为它确立了唯一性的定义。
3. 二进制排序规则专门用于忽略文化规则并在字节级别工作，因此utf8mb4_bin是正确的选择。
4. MySQL允许使用列级粒度设置编码和排序规则的组合。
5. 如果列定义缺少排序规则，它将使用表一级。
6. 如果表定义缺少排序规则，它将使用数据库级别1。
7. 如果数据库定义缺少排序规则，它将使用服务器一级。

值得注意的是，MySQL将透明地在编码之间转换，只要：

• 正确设置连接编码
• 转换在物理上是可能的(例如，所有源字符也属于目标编码)

由于最后一个原因，对于仍然是文本的列，VARBINARY可能不是最好的选择，因为它打开了从配置为使用ISO-8859-1的连接存储café的大门，并且无法从配置为使用UTF-8的连接中正确地检索它。

附带注意:所示的表定义可能会触发以下错误：

错误1071 (42000)：指定的键太长；最大键长为767字节

索引的最大大小可能相对较小。来自文档

如果启用了innodb_large_prefix (默认)，则使用动态或压缩行格式的InnoDB表的索引键前缀限制为3072字节。如果禁用innodb_large_prefix，则任何行格式的表的索引键前缀限制为767字节。 innodb_large_prefix是不推荐的，并将在以后的版本中删除。innodb_large_prefix是在MySQL 5.5中引入的，用于禁用大索引键前缀，以便与不支持大索引键前缀的早期InnoDB版本兼容。 对于使用冗余或紧凑行格式的InnoDB表，索引键前缀长度限制为767字节。例如，可以在文本或VARCHAR列上使用超过255个字符的列前缀索引来达到此限制，假设为utf8mb3字符集，每个字符最多为3个字节。 试图使用超过限制的索引键前缀长度将返回错误。若要避免复制配置中的此类错误，请避免在主服务器上启用innodb_large_prefix (如果无法在从服务器上启用它)。

由于utf8_mb8为每个字符分配了4个字节，因此只有192个字符会溢出767个限制。

我们还有一个问题：

mysql> CREATE TABLE `dummy` (
    -> `key` varchar(191) COLLATE utf8mb4_bin NOT NULL,
    -> UNIQUE KEY `key` (`key`)
    -> )
    -> ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `dummy` (`key`) VALUES ('one');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `dummy` (`key`) VALUES ('one ');
ERROR 1062 (23000): Duplicate entry 'one ' for key 'key'

再说一次，好吗?

mysql> INSERT INTO `dummy` (`key`) VALUES ('One');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `dummy` (`key`) VALUES ('öne');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `dummy`;
+-----+
| key |
+-----+
| One |
| one |
| öne |
+-----+
3 rows in set (0.00 sec)

最后一个问题是一个有趣的微妙的MySQL排序规则。来自文档

所有MySQL排序规则都是PADSPACE类型的。这意味着对中的所有CHAR、VARCHAR和MySQL中的文本值进行比较，而不考虑任何尾随空格。此上下文中的“比较”不包括类似的模式匹配运算符，对于该运算符，尾随空格是重要的。 ..。对于那些拖尾垫字符被删除或比较忽略这些字符的情况，如果某个列的索引需要唯一值，则将只在尾随垫字符数量不同的列值中插入将导致重复键错误。

我敢说VARBINARY类型是克服这一问题的唯一方法.