Database character set and collation (Character set and Collation)

Source: Internet
Author: User

The database needs to accommodate a variety of languages and characters to support different character sets (Character set), and each character set has its own collation (Collation).

(Note: Collation originally intended for proofreading, collation, but according to the actual use of the scene, think or translation as a collation more appropriate)

In most cases, the type of character set and collation used determines the level of the server, database, and table, and general SQL operations do not need to be concerned about these.

The following operations are all examples of MySQL.

To view the character set and collation supported by the database

To view the character set:


Some of the results:

To view the collation: (suffix "_cs" or "_ci" means case-sensitive and case-insensitive (sensitive. & Case Insensitve))


Some of the results:

Using the character set and collation supported by the database

Typically, system administration defines a default character set and collation at installation time.

You can also set character sets and collations at the table level, or even at the column level, when you create a database, on a database-wide scale, and on a table.

> Specify character set and collation for database construction :

To determine the character set and collation used, you can use the following statements:

 like ' character% '  like'collation%';

> Specify a table's character set and collation when you build a table :

1 CREATE TABLE mytable (2     INT , 3     VARCHAR (ten) 4 DEFAULT CHARACTER SET Hebrew 5 COLLATE hebrew_general_ci;

Use the database default setting when you do not specify a character set and collation, or use the default collation of a character set if you specify a character set that does not specify a collation.

> specifying the character set and collation of tables and columns when you build a table

CREATE TABLE mytable (    INT,    varchar ()    ,varchar (CHARACTERSET  latin1         DEFAULTCHARACTER  SET  Hebrew     COLLATE hebrew_general_ci;

> Sorting Rules for order by in a custom query statement

SELECT *  from ORDER  by ' column1 ' COLLATE latin1_general_cs;

The use here is not limited to order by, but also group by, aggregate functions, and so on.

Reference article:

"1"MySQL will be January 2009 11th Edition 27th chapter "Globalization and Localization"

Database character set and collation (Character set and Collation)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.