使用二进制排序规则有什么影响? [英] What effects does using a binary collation have?
问题描述
回答此问题，我对无法设法找到足够答案的事情感到不确定.
While answering this question, I became uncertain about something that I didn't manage to find a sufficient answer to.
使用二进制utf8_bin和不区分大小写的utf8_general_ci归类之间的实际区别是什么?
What are the practical differences between using the binary utf8_bin and the case insensitive utf8_general_ci collations?
我可以看到三个:
-
两者的排序顺序不同；
_bin的排序顺序可能会将任何变音符放在字母表的末尾，因为比较了字节值(对吗?)
Both have a different sorting order;
_bin's sorting order is likely to put any umlauts to the end of the alphabet, because byte values are compared (right?)
仅_bin
在_bin
还有其他需要注意的差异或副作用吗?
Are there any other differences or side-effects to be aware of?
参考:
- 9.1.2. Character Sets and Collations in MySQL
- 9.1.7.6. The _bin and binary Collations in the mySQL manual
- 9.1.7.7. The BINARY Operator
无法解决该问题的类似问题:
Similar questions that don't address the issue:
推荐答案
如果字符不同(二进制大小写或变音符号不同)，二进制排序规则将完全按照C语言中的strcmp()进行字符串比较.缺点是排序顺序不自然.
Binary collation compares your string exactly as strcmp() in C would do, if characters are different (be it just case or diacritics difference). The downside of it that the sort order is not natural.
不自然排序顺序的示例(如二进制"是):A，B，a，b 在这种情况下，自然排序顺序应为:A，a，B，b(该sme字母的小写和大写字母彼此相邻排序)
An example of unnatural sort order (as in "binary" is) : A,B,a,b Natural sort order would be in this case e.g : A,a,B,b (small and capital variations of the sme letter are sorted next to each other)
二进制排序规则的实际优势是它的速度，因为字符串比较非常简单/快速.在一般情况下，具有二进制的索引可能不会产生预期的排序结果，但是对于完全匹配，它们可能会有用.
The practical advantage of binary collation is its speed, as string comparison is very simple/fast. In general case, indexes with binary might not produce expected results for sort, however for exact matches they can be useful.
这篇关于使用二进制排序规则有什么影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
