我已经在几个小时,在MySQL文档和其他地方研究了这个问题,但仍然找不到令人满意的解决方案。问题是:
像sqlite一样使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
是去的方式,但我仍然想听听您对>>>其他,可能>更好的更好 options 。
附录: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)
mysql想要在执行INSERT
和SELECT
时转换字符串。转换是您声明 client 具有的内容以及列被声明为存储的内容。
避免这种情况的唯一方法是用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
对待一=一个>Öne。
尾间空间由列的数据类型(VARCHAR
或其他)控制。最新版本甚至具有与是否考虑尾随空间有关的设置。
问题中所示的方法(大部分)在MySQL中应正常工作:
:整理(不要与编码相混淆)是定义如何对字符进行排序和比较的集合或规则,通常用于在数据库级别复制,从文化角度来看用户期望(如果我搜索
cafe
I I搜索CC_24 I期望也可以找到café
)。整理在唯一约束上起着重要的规则,因为它确定了 unique 。
的定义二进制校正是专门用于忽略文化规则和在字节级别的工作,因此
utf8mb4_bin
是此处的正确选择。mySQL允许将编码和整理与列级粒度设置组合。
如果列定义缺少整理,则将使用表级别。
如果表格定义缺少整理,则将使用数据库级别。
如果数据库定义缺少整理,则将使用服务器级别。
也值得注意的是,MySQL只要:
- 正确设置连接编码
- 在物理上进行转换是可能的(例如,所有源字符也属于目标编码)
由于这个最后的原因,VARBINARY
可能不是仍然是文字的列的最佳选择配置为使用UTF-8的连接。
旁注:显示的表定义可能会触发以下错误:
错误1071(42000):指定的键太长;最大密钥长度为767字节
索引的最大尺寸可能相对较小。来自文档:
如果启用了Innodb_large_prefix(默认),则索引键前缀 限制是使用动态或压缩的InnoDB表的3072字节 行格式。如果禁用Innodb_large_prefix,则索引密钥前缀 限制为任何行格式的表767字节。
innodb_large_prefix被弃用,将来将被删除 发布。Innodb_large_prefix在MySQL 5.5中引入了以禁用 大型索引密钥前缀,以与早期版本的兼容性 不支持大索引密钥前缀的InnoDB。
索引密钥前缀长度限制为767个字节,用于InnoDB表 使用冗余或紧凑的行格式。例如,您可能会打 该限制具有255个以上字符的列前缀索引 假设UTF8MB3字符集,文本或VARCHAR列和 每个字符最多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 Collations有趣的微妙之处。来自文档:
所有MySQL校正都是类型的padspace。这意味着所有char, 比较MySQL中的VARCHAR和文本值,而无需考虑任何 落后空间。在这种情况下的"比较"不包括 像模式匹配的操作员,尾随空间是 显着
[...] 对于那些拖延垫字符的情况或 比较忽略它们,如果列的索引需要独特 值,插入仅在数量的列值中 尾垫字符将导致重复键错误。
我敢说VARBINARY
类型是克服这一点的唯一方法...