For a website with international support, I use utf8mb4 charset and utf8mb4_unicode_ci to sort in most tables and columns. Performance is not paramount, and accurate sorting in different languages is important.
I understand how the utf8mb4_general_ci and utf8mb4_unicode_ci sorts work with comparisons in general with accented characters, namely:
SELECT column FROM table WHERE column='abad';
Would return "abad" and "abád"
While studying utf8 support in MySQL, I ran into an alleged problem with nonfunctional utf8___ calculations. The page http://mzsanford.com/blog/mysql-and-unicode/ describes the problem that the changes are not saved in some updates. He says: “When updating a record, it seems that MySQL (or at least InnoDB) checks for equality before updating the record. Since conversion only with accent is considered comparable, MySQL matches the record (which saves I / O overhead) and returns success. as he believes that he optimized the record, not failure. '
I interpret this as: if you tried to update a record that only changes the accents of a field, it will not be updated properly (since MySQL considers it to already match). But I could not reproduce it. I created a simple test case:
CREATE DATABASE test_utf8 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE test_utf8; CREATE TABLE test ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, text VARCHAR(300) NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; INSERT INTO test (text) VALUES ('abád'); UPDATE test SET text='abad' WHERE id=1;
However, this correctly updates the value (despite only changing the emphasis by one character). Perhaps this was just a problem in an older version of MySQL? Or does this question arise in slightly different circumstances?
I would also appreciate if you have a moment to read some of my notes on several concepts around the subject and see if I have any misconceptions. If this is unmistakable, perhaps this will be useful information for someone.
The MySQL utf8 character set does not support true utf8 support, as characters are only 1-3 bytes. For true utf8 support, you probably want to use utf8mb4.
In general, utf8mb4_unicode_ci will be more accurate with appropriate language sorting, but there is a slight performance hit rather than using utf8mb4_general_ci.
If some columns never need to be sorted and will use comparison / equality checks, you should use utf8mb4_bin since it will be a little faster.
Accented characters are considered equal in both utf8mb4_general_ci and utf8mb4_unicode_ci commands. Because of this, this is a poor sorting choice for columns that must have unique values (e.g. primary keys). In this case, use utf8mb4_bin. And if the field should be emphasized for uniqueness, but at some point it should be sorted by language, it can be saved as utf8mb4_bin, and you can use the collate clause in the request when ordering. Example:
SELECT column FROM table ORDER BY column COLLATE utf8mb4_unicode_ci;
This will cause the ordering to be sorted by language, despite its internal storage in binary sorting. This will affect performance because field mapping determines how it is indexed. The difference in query performance will be similar to the difference in performance when sorting a column without indexing and an indexed column.
By default, searches under utf8mb4_unicode_ci or utf8mb4_general_ci will not be accented, so a search for "abad" will return "abad" and "abád". Therefore, if you want to search with an accent, you need to either set the column mapping in utf8mb4_binary (if all queries are sensitive to accents), or use the matching clause in the query (if you want most queries to be accurate). Since the utf8mb4_bin collation is case sensitive, you will also need to modify the query if you want case insensitive, but with an accent. For example (suppose your search query is already lowercase in the server side scripting language):
(Assuming the data is stored with a collation of utf8mb4_bin) SELECT column FROM table WHERE LOWERCASE(column) LIKE 'abád'; (Assuming the data is stored with a collation of utf8mb4_unicode_ci) SELECT column FROM table WHERE LOWERCASE(column) LIKE 'abád' COLLATE utf8mb4_bin;
Also, from the MySQL documentation (just by including it for others): when comparing values from different columns, declare those columns with the same character set and sort where possible, to avoid string conversion during query execution.