📢 📢 📢 📣 📣 📣 hello! Hello, everyone, I am [IT Bond], known as Jeames007. I have 10 years of DBA working experience and am a member of China DBA Union (ACDU). I am currently engaged in DBA and program programming 😜😜😜. ❤️❤️❤️ Thank you all! ❤ ️ ❤ ️ ❤ ️

⛳ ️ 1. COLLATE is introduced

COLLATE Affects the ORDER of the ORDER BY statement, the results filtered BY the greater than or less sign in the WHERE condition, and the query results of the DISTINCT, GROUP BY, and HAVING statements. In addition, when mysql creates an index, if the index column is of character type, it will affect the index creation, but the effect is not perceptible. In general, wherever character type comparison or sorting is involved, COLLATE is involvedCopy the code

Run the show create table command in mysql, you can see a table construction clause

Utf8_unicode_ci is a COLLATE rule for columns of character types in mysql, such as VARCHAR, CHAR, and TEXT. A COLLATE type is required to tell mysql how to sort and compare columns.Copy the code

⛳️ 2.COLLATE differences

COLLATE is usually related to data encoding (CHARSET). Generally, each CHARSET has multiple collates that it supports, and each CHARSET specifies a COLLATE as the default value.

The default COLLATE of latin1 is latin1_Swedish_ci. The default COLLATE of GBK is GBk_chinese_ciUTf8MB4 is UTF8MB4_general_ci Note that since mysql 8.0, the mysql default CHARSET has changed from Latin1 to UTF8MB4, and the default COLLATE has changed to UTF8MB4_0900_ai_ci

Mysql > show charset where charset in (‘ GBK ‘, ‘utf8MB4’, ‘latin1’);

⛳️ 3. Impact of COLLATE

🌈 3.1 Case impact

create table case_bin_test (word VARCHAR(10)); INSERT INTO case_bin_test VALUES (‘ Frank ‘), (” Google “), (‘ froogle), (” flickr “), (” flickr “);

Mysql queries are case insensitive by default, as shown below

If you want to be case-sensitive, add binary, as shown below \

🌈 3.2 Sorting impact

Create table test_collate (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Insert into test_collate(text) values(‘ aaa ‘),(‘ BBB ‘),(‘ aaa ‘),(‘ BBB ‘),(‘ BBB ‘); Mysql > select * from test_collate order by text;

It turns out that uppercase AAA and lowercase AAA are considered the same, both ranked before BBB and BBB. Now, if we sort by text, use utf8mb4_bin: select * from test_collate order by text collate utf8mb4_bin;

Recommended reading:

MySQL8.0 new features to look at, performance and double 叒 to improve MySQL wulinesocialis, SQL learning will be used to test Linux7.6 source code install Mysql8 Oracle inspection script, server can be directly deployed MySQL root password forget to find a magic way to monitor Zabbix, Oracle listens for log clearing from deployment to application

You can like, collect, pay attention to, comment on me, there are database related questions to contact me or exchange yo ~!