В чем разница между utf8_general_ci и utf8_unicode_ci
То, как это должно быть сделано (то есть, если wkhtmltopdf поддерживает его), будет использоваться правильный CSS Paged Media: http://www.w3.org/TR/css3-gcpm/
Я изучаю, что это займет сейчас.
5 ответов
These two collations are both for the UTF-8 character encoding. The differences are in how text is sorted and compared.
Note: You should use utf8mb4 rather than utf8. They both refer to the UTF-8 encoding, but the older utf8 had a MySQL-specific limitation preventing use of characters numbered above 0xFFFD.
Note: Newer versions of MySQL have updated Unicode sorting rules, available under names such as utf8mb4_0900_ai_ci for equivalent rules based on Unicode 9.0 - and with no equivalent general variant.
Key differences
utf8mb4_unicode_ciis based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.utf8mb4_general_ciis a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today's computers.
Note: there exists now an updated version of utf8mb4_unicode_ci called utf8mb4_0900_ai_ci - this is based on changes in Unicode version 9.0, and is also apparently faster. It adopts a new naming scheme whereby 0900 is the Unicode version and ai means accent-insensitive - like the previous utf8mb4_unicode_ci, accents in letters are not considered significant.
Benefits of utf8mb4_unicode_ci over utf8mb4_general_ci
utf8mb4_unicode_ci, which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.
As far as Latin (ie "European") languages go, there is not much difference between the Unicode sorting and the simplified utf8mb4_general_ci sorting in MySQL, but there are still a few differences:
For examples, the Unicode collation sorts "ß" like "ss", and "Œ" like "OE" as people using those characters would normally want, whereas
utf8mb4_general_cisorts them as single characters (presumably like "s" and "e" respectively).Some Unicode characters are defined as ignorable, which means they shouldn't count toward the sort order and the comparison should move on to the next character instead.
utf8mb4_unicode_cihandles these properly.
In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8mb4_general_ci sorting. The suitability of utf8mb4_general_ci will depend heavily on the language used. For some languages, it'll be quite inadequate.
What should you use?
There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.
In the past, some people recommended to use utf8mb4_general_ci except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.
There's an argument to be made that if speed is more important to you than accuracy, you may as well not do any sorting at all. It's trivial to make an algorithm faster if you do not need it to be accurate. So, utf8mb4_general_ci is a compromise that's probably not needed for speed reasons and probably also not suitable for accuracy reasons.
One other thing I'll add is that even if you know your application only supports the English language, it may still need to deal with people's names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.
What the parts mean
Firstly, ci is for case-insensitive sorting and comparison. This means it's suitable for textual data, and case is not important. The other types of collation are cs (case-sensitive) for textual data where case is important, and bin, for where the encoding needs to match, bit for bit, which is suitable for fields which are really encoded binary data (including, for example, Base64). Case-sensitive sorting leads to some weird results and case-sensitive comparison can result in duplicate values differing only in letter case, so case-sensitive collations are falling out of favor for textual data - if case is significant to you, then otherwise ignorable punctuation and so on is probably also significant, and a binary collation might be more appropriate.
Next, unicode or general refers to the specific sorting and comparison rules - in particular, the way text is normalized or compared. There are many different sets of rules for the utf8mb4 character encoding, with unicode and general being two that attempt to work well in all possible languages rather than one specific one. The differences between these two sets of rules are the subject of this answer. Note that unicode uses rules from Unicode 4.0. Recent versions of MySQL add the rulesets unicode_520 using rules from Unicode 5.2, and 0900 (dropping the "unicode_" part) using rules from Unicode 9.0.
And lastly, utf8mb4 is of course the character encoding used internally. In this answer I'm talking only about Unicode based encodings.
Согласно этому сообщению, существует значительно большой выигрыш в производительности на MySQL 5.7 при использовании utf8mb4_general_ci в земельном участке utf8mb4_unicode_ci: https://www.percona.com/blog/2019/02/27/charset-and-collation-settings-impact-on-mysql-performance /
-
1@bandejapaisa: В значительной степени, что я сказал в своем ответе: Отслеживайте ли I' m наблюдение и только пытаются прекратить наблюдать, ли я. – Peter Hosey 18 January 2012 в 23:07
См. Руководство mysql, раздел Наборы символов Unicode :
Для любого набора символов Unicode , операции, выполненные с использованием Параметры сортировки _general_ci выполняются быстрее, чем параметры сортировки _unicode_ci. Например, сравнения для Сортировка utf8_general_ci быстрее, но чуть менее правильно, чем сравнения для utf8_unicode_ci. причина в том, что utf8_unicode_ci поддерживает такие отображения как расширения; то есть когда один персонаж сравнивается как равный комбинации других персонажей. Для Например, на немецком и некоторых других языки «ß» равно «ss». utf8_unicode_ci также поддерживает сокращения и игнорируемые персонажи. utf8_general_ci - это устаревшее сопоставление который не поддерживает расширения, сокращения или игнорируемые персонажи. Это может сделать только один к одному Сравнения между символами.
Итак, для подведения итогов, utf_general_ci использует меньший и менее правильный (согласно стандарту) набор сравнений, чем utf_unicode_ci, который должен реализовать весь стандарт. Набор general_ci будет быстрее, потому что требуется меньше вычислений.
Существует две больших разницы сортировка и символьное соответствие:
Сортировка :
-
utf8mb4_general_ciудаляет все диакритические знаки и виды один за другим, которые могут создать неправильные результаты вида. -
utf8mb4_unicode_ciточные виды.
Символ, Соответствующий
, Они соответствуют символам по-другому.
, Например, в utf8mb4_unicode_ci Вы имеете i != ı, но в utf8mb4_general_ci это содержит ı=i.
, Например, предположите, что Вы ссоритесь с name="Yılmaz". Затем
select id from users where name='Yilmaz';
возвратил бы строку, если словосочетание utf8mb4_general_ci, но если это расположено с [1 110], это было бы не , возвращают строку!
, С другой стороны, у нас есть это a=ª и ß=ss в [1 113], который не имеет место в [1 114]. Поэтому предположите, что Вы ссоритесь с [1 115], затем
select id from users where name='assi';
возвратил бы строку, если словосочетание utf8mb4_unicode_ci, но было бы не , возвращают строку, если словосочетание установлено на [1 117].
полный список А соответствий для каждого словосочетания может быть найден здесь .
Этот пост очень хорошо описывает его.
Вкратце: utf8_unicode_ci использует алгоритм Unicode Collation Algorithm, как он определен в стандартах Unicode, в то время как utf8_general_ci - более простой порядок сортировки, который приводит к "менее точным" результатам сортировки.
.