MySql sorting rules (2)

MySql sorting rules (2)

I saw this SQL statement today. I have heard of it before, but I have not understood it. So I would like to take a note here:

UserName varchar (20) collate chinese_prc_ci_as null

1. First, collate is a clause that can be applied to database definitions or column definitions to define sorting rules, or to string expressions to apply projection of sorting rules

::={ Windows_collation_name} | {SQL _collation_name}

Parameter: collate_name is the name of the sorting rule applied to expression column definition or database definition. collation_name can only be the specified Windows_collation_name or SQL _collation_name.

Windows_collation_name: indicates the name of a Windows sorting rule. For more information, see Windows sorting rule name.

SQL _collation_name: indicates the name of an SQL sorting rule. For more information, see SQL sorting rule name.

2. What is a sorting rule?

"In Microsoft SQL Server 2000,
The physical storage of strings is controlled by the sorting rules. The sorting rules specify the bit mode of each character and the storage
Rules Used for saving and comparing characters"
SQLSERVER

Select * from: fn_helpcollations ()

The name of a sorting rule consists of two parts. The first half is the character set supported by this sorting rule.
For example:
Chinese_PRC_CS_AI_WS
First half: UNICODE Character Set, Chinese_PRC _ pointer sorting rule for Chinese Simplified UNICODE
The second half of the sorting rule is the suffix meaning:
_ BIN binary sorting
_ CI (CS) is case sensitive, CI is case insensitive, and CS is case sensitive
_ Whether AI (AS) distinguishes stress, AI does not distinguish,
_ KI (KS) indicates whether Kana is distinguished. KI is not distinguished, and KS is distinguished.
_ Whether WI (WS) is differentiated by width WI, WS

Case Sensitive: select this option if you want to make the comparison between uppercase and lowercase letters different.
Accent differentiation: select this option if you want to treat the comparison as different from the accent and non-accent letters,
Comparison also treats letters with different accents as unequal
Kana differentiation: select this option if you want to treat Katakana and katakana as different Japanese syllables.
Width differentiation: select this option if you want to make the comparison between halfwidth and fullwidth characters.

3. There are also corresponding character sorting rules in MySql (in MySql 5.1, 10.10.MySql supports Character Set co-checking)

For example:

Create a user database that uses the utf8 Character Set and has verification rules

Create database if not exists user character set UTF8 collate utf8_general_ci;

How can a mysql database be sorted by two conditions?

Select * from mysql order by field 1 asc, Field 2 asc, Field 3 desc;

Mysql sorting problems

What sorting rules are required for the results you have given? Cannot understand. Sorting is based on numeric values and character values. The numeric column type is easy to understand. There are many character types. For example, based on the converted ASIIK code, the number of Chinese characters includes the first spelling, number of strokes, and so on.

SELECT username FROM 'cmstop _ member' WHERE username LIKE "bi %" order by case username WHEN username LIKE "bi %" THEN 1 ELSE 0 END

This should meet your requirements. Match the input result and display it before. However, there are no pairs. Sort strings after the input characters are removed.