MySQL character set and collation order tutorial

Using character sets and collation order

MySQL supports numerous character sets. To see the complete list of supported character sets, use the following statement:

Input:

show character set;

Analysis: This statement displays all available character sets along with a description and default collation for each character set .

To see the complete list of supported collations, use the following statement:

Input:

show collation;

Analysis: This statement displays all available collations, and the character sets for which they apply . You can see that some character sets have more than one collation. For example, latin1 has several collations for different European languages, and many collations appear twice, once case-sensitive (denoted by _cs) and once case-insensitive (denoted by _ci).

Usually the system administrator defines a default character set and collation during installation. In addition, you can also specify the default character set and collation when creating the database. To determine the character set and collation used, you can use the following statement:

Enter:

show variables like 'character%';
show variables like 'collation%';

In practice, character sets are rarely server-wide (or even database-wide) settings. Different tables and even different columns may require different character sets, and both can be specified when creating the table.

In order to specify the character set and collation for the table, you can use CREATE TABLE with clauses:

Input:

create table mytable
(
columnn1       int,
columnn2       varchar(10)
)default character set hebrew collate hebrew_general_ci;

Analysis: This statement creates a table containing two columns, And specify a character set and a collation order.

This example specifies both CHARACTER SET and COLLATE. Generally, MySQL determines what character set and collation to use as follows.

1. If both CHARACTER SET and COLLATE are specified, these values ​​are used.

2. If only CHARACTER SET is specified, this character set and its default collation are used (as shown in the results of SHOW CHARACTER SET).

3. If neither CHARACTER SET nor COLLATE is specified, the database default is used.

In addition to being able to specify the character set and collation table range, MySQL also allows them to be set for each column, as follows:

Input:

create table mytable
(
columnn1       int,
columnn2       varchar(10),
columnn3       varchar(10) character set latin1 collate latin1_general_ci
latin_grneral_ci
)default character set hebrew collate hebrew_general_ci;

Analysis: here CHARACTER SET and COLLATE are specified for the entire table and for a specific column.

As mentioned earlier, proofreading plays an important role in sorting the data retrieved using the ORDER BY clause. If you need to order a specific SELECT statement in a different collation order than when you created the table, you can do this in the SELECT statement itself:

Input:

select * from customers order by lastname,firstname collate latin1_general_cs;

Analysis: This SELECT uses COLLATE to specify an alternate collation order (in this case, case-sensitive collation). This will obviously affect the order in which the results are sorted.

Temporary Case Sensitivity The SELECT statement above demonstrates a technique for performing a case-sensitive search on a table that is not normally case-sensitive. Of course, the reverse is also possible.

Other COLLATE clauses of SELECT In addition to being used in the ORDER BY clause as seen here, COLLATE can also be used in GROUP BY, HAVING, aggregate functions, aliases, etc.

Finally, it's worth noting that strings can be converted between character sets if absolutely necessary. To do this, use the Cast() or Convert() function.

【Related recommendations】

1. mysql free video tutorial

2. Introduction to MySQL character set and proofreading order

3. MySQL transaction-detailed explanation of using retention points and changing the default commit behavior

4. MySQL transaction-ROLLBACK, detailed explanation of COMMIT usage

5. MySQL transaction processing example explanation

The above is the detailed content of MySQL character set and collation order tutorial. For more information, please follow other related articles on the PHP Chinese website!