Mysql Chinese sorting rules description

When using MySQL, we often sort and query a field. We usually want to sort by the first letter of the Chinese pinyin. However, when sorting Chinese characters in MySQL, the sorting results of Chinese characters are often wrong.

This situation exists in many versions of MySQL.

If this problem is not solved, MySQL will not be able to actually process Chinese.

The reason for this problem is that MySQL is case-insensitive when querying strings. When compiling MySQL, the ISO-8859 character set is generally used as the default character set. Therefore, the case conversion of Chinese encoded characters during the comparison process causes this phenomenon.

After checking the information, there are two solutions:

1. Add the "binary" attribute to the field containing Chinese characters so that it can be compared as a binary field. For example, change "name varchar(10)" to "name varchar(10)binary".

2. If you do not want to modify the table structure or recompile MySQL, you can also use the CONVERT function in the order by part of the query statement.

For example, if the name field is in Chinese and needs to be sorted, you can write select * from mytable order by CONVERT(name USING gbk);

Supplement: MySQL database default sorting problem

1. MySQL official answer:

SELECT * FROM tbl -- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.

This means that if a MyISAM engine table is not deleted or modified, and a select statement without order by is executed, the table will be sorted in the order of insertion.

If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.

For InnoDB engine tables, under the same circumstances, select without order by will sort by primary key, from small to large.

2. View the database engine command:

(1) View the engine used by a table

show create table;

(2) Check which engines MySQL supports

show engines;

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.