Collation
Rules for comparing / sorting strings. Collation defines sort orders that differ by language and culture.
Collation is a set of rules used for comparing and sorting strings. Because the sort order of the same characters differs by language and culture, correct collation settings are essential in internationalized systems. For example, in German "ö" is placed in almost the same position as "o", but in Swedish "å", "ä", and "ö" are treated as independent letters that follow "z".
In databases, you can specify a collation for each table or column. MySQL's utf8mb4_unicode_ci performs case-insensitive comparison, and utf8mb4_bin performs binary comparison. Choosing the wrong collation causes missing search results and incorrect sort orders. For example, utf8mb4_bin treats "A" and "a" as different characters, so a username search can fail to find a match because of a difference in case. In the name, ci means case-insensitive, ai means accent-insensitive, and cs and as mean case-sensitive and accent-sensitive, respectively. However, collations with the same ci are not identical inside. utf8mb4_0900_ai_ci is based on the weights of UCA (Unicode Collation Algorithm) 9.0.0 and is a newer set of rules than utf8mb4_unicode_ci, which is based on UCA 4.0.0. The two also differ in how they handle trailing spaces: the newer one is NO PAD and judges "a" and "a " to be different values. Note that when you migrate the collation of an existing table, rows that were previously regarded as the same value become different, and unique constraints and search results change.
Sorting Japanese is especially complex. Many rules are intertwined, such as treating hiragana / katakana as equivalent ("あ" and "ア", both "a"), the handling of dakuten / handakuten (the order of "は", "ば", and "ぱ", that is ha, ba, and pa), and how to order kanji. Unicode's CLDR (Common Locale Data Repository) maintains these rules by locale, and they can be used from each programming language through the ICU library. Because differences in kana type are judged at the fourth level of weights, hiragana and katakana are sorted as the same thing under the default settings. MySQL's collations for Japanese are similar: utf8mb4_ja_0900_as_cs treats katakana and hiragana as equivalent, and if you want to distinguish them, you choose utf8mb4_ja_0900_as_cs_ks, which uses four levels of weights. Kanji are even more troublesome. Even if you want to sort personal names or book titles in reading order, the reading cannot be uniquely determined from the kanji ("山崎" (Yamazaki) can be read both "やまざき" (yamazaki) and "やまさき" (yamasaki)). If you sort by the kanji as they are, "山崎" comes before "川口" (Kawaguchi), but if you sort by the kana reading, "川口" comes first. If reading order is a requirement, the reliable way is to keep the kana reading as a separate field and sort by that column.
In JavaScript, you can compare strings according to the locale with Intl.Collator. A result worth understanding here as an example is new Intl.Collator('ja').compare('あ', 'ア'), which returns 0 (equal). As mentioned above, differences in kana are handled at the lowest weight, so in some implementations they are not distinguished even if you raise the specified strength. If you want to treat hiragana and katakana separately, deal with it outside the collation, for example by normalizing to one or the other before comparison. A simple String.localeCompare() can perform the same comparison, but when sorting a large amount of data, creating one Intl.Collator instance and reusing it performs better. Passing { numeric: true } gives a numeric-order comparison in which "file2" sorts before "file10", which is useful for sorting names that contain sequential numbers.
From the perspective of character counting, collation affects the equivalence of characters. Whether "は" (ha) and "ば" (ba) are treated as equivalent, and whether the full-width digit "1" and the half-width digit "1" are regarded as the same, change with the collation settings. JavaScript's Intl.Collator judges "1" and "1" to be equal under the Japanese locale, but in MySQL's utf8mb4_bin they are different characters. This is why, even with the same data, the number of duplicates detected differs between the application side and the DB side. If you decide which differences to ignore before aggregation or deduplication, and normalize the differences you decided to ignore at save time, the counting in later stages will not vary.