Why is Collation in the database?

Collation in MySQL is a set of rules to define the sorting and comparison of data. It mainly formulates rules for character coding and string operations, such as:

Smallwriter sensitivity (uppercase letters and lowercase letters, for example: A and A)

Scholarship of the Symbol symbol (for example: é and e)

Language -based sorting rules (eg, in German, it is arranged near A, but in Swedish behind Z).

It is associated with the character set, and the character set defines the characters (letters) that can be stored in the database.

Why do I need to be?

Data Sorting (Sorting):

Collation determines the order of data display. For example: in UTF8MB4_General_ci Collation, Apple and Apple are considered the same (not distinguished by the case), but they are different in UTF8MB4_BIN.

Comparison comparison (comparison):

When comparing a string in WHERE Sentence or Join, the Collation rules are applied. For example: 'café' = 'cafe' depends on Collation.

Search accuracy:

According to color, the search results will change. For example: in the Like 'T%' query, whether the cat or cat can be found depends on the color.

Language support:
Collation is used to correctly sort or compares different languages. For example: special rules for Spanish or German.

Data consistency:

The same rules are applied in all operations (sorting, comparison, search) to maintain the coherence of data.

Colting Example UTF8MB4_General_CI:

Not distinguishment, support general Unicode.

a = a, é = E.

UTF8MB4_BIN:

Dual comparison (distinguished and lowercase).

A A, é ≠ E.

UTF8MB4_UNICODE_CI:

fully supports Unicode, suitable for multiple languages.

How to set up color?

At the database level:

SQL Copy Create Database Mydb Character Set UTF8MB4 Collate UTF8MB4_UNICODE_CI; 2. At the level level:

SQL Copy Create Table MyTable ( ID int, name varchar (255) ) Character Set UTF8MB4 Collate UTF8MB4_UNICODE_CI; 3. At the level level:

SQL Copy Create Table MyTable ( ID int, name varchar (255) color utf8MB4_BIN ); Which color choose? Large -handed sensitivity: Select _ci (not distinguished or smallcat) or _bin

Language requirements: For multilingual data, UTF8MB4_UNICODE_CI is better.

Performance: UTF8MB4_General_CI is faster, but Unicode_ci is more accurate.

Warning Collation does not match: To avoid errors in table connection or comparison, use the same color in all table/columns.

Index: Changing Collation may need to rebuild the index.

Summary Collation affects the sorting, comparison and search of data in MySQL. Choose the appropriate Collation according to the language of the application, the sensitivity and performance of the application!

The above is the detailed content of Why is Collation in the database?. For more information, please follow other related articles on the PHP Chinese website!