{"id":2342,"date":"2013-05-14T00:46:50","date_gmt":"2013-05-14T08:46:50","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2342"},"modified":"2023-10-23T02:53:42","modified_gmt":"2023-10-23T09:53:42","slug":"mysql-collation","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-collation\/","title":{"rendered":"MySQL Collation"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL collation<\/strong> and how to set character sets and collations for the MySQL server, database, table, and column.<\/p>\n\n\n\n

Introduction to MySQL collation<\/h2>\n\n\n\n

A MySQL collation is a set of rules used to compare characters in a particular character set<\/a>. Each character set in MySQL has at least one default collation. It can have more than one collation. However, two character sets cannot have the same collation.<\/p>\n\n\n\n

MySQL provides the SHOW CHARACTER SET<\/code>  statement that allows you to get the default collations of character sets:<\/p>\n\n\n

SHOW<\/span> CHARACTER<\/span> SET<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The values of the default collation column specify the default collations for the character sets.<\/p>\n\n\n\n
By convention, a collation for a character set begins with the character set name and ends with _ci<\/code> (case insensitive) _cs<\/code>  (case sensitive) or _bin<\/code>  (binary).<\/p>\n\n\n\n
To get all collations for a given character set, you use the SHOW COLLATION<\/code>  statement as follows:<\/p>\n\n\n
SHOW<\/span> COLLATION<\/span> LIKE<\/span> 'character_set_name%'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement gets all collations for the latin1<\/code> character set:<\/p>\n\n\n
SHOW<\/span> COLLATION<\/span> LIKE<\/span> 'latin1%'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL
MySQL Collations for latin1 Character Set<\/figcaption><\/figure>\n\n\n\n
As mentioned above, each character set has a default collation e.g., latin1_swedish_ci<\/code> is the default collation of the latin1<\/code> character set.<\/p>\n\n\n\n
Setting character sets and collations<\/h2>\n\n\n\n
MySQL allows you to specify character sets and collations at four levels: <\/p>\n\n\n\n
    \n
  1. Server<\/li>\n\n\n\n
  2. Database<\/li>\n\n\n\n
  3. Table<\/li>\n\n\n\n
  4. Column<\/li>\n<\/ol>\n\n\n\n
    1) Setting character sets and collations at the server Level<\/h3>\n\n\n\n
    MySQL uses the latin1<\/code> as the default character set. Therefore, the default collation is latin1_swedish_ci<\/code>. You can change these settings at server startup.<\/p>\n\n\n\n
    If you specify one character set at server startup, MySQL will use the default collation of that character set. However, if you specify both a character set and a collation explicitly, MySQL will use the character set and collation for all databases that you will create.<\/p>\n\n\n\n
    The following statement sets the utf8 character set and utf8_unicode_cs<\/code> collation for the server via the command line:<\/p>\n\n\n
    >mysqld --character-set-server=utf8 --collation-server=utf8_unicode_cs<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    2) Setting character sets and collations at the database level<\/h3>\n\n\n\n
    When you create a database<\/a> but do not specify the character set and collation, MySQL will use the default character set and collation of the server for the new database.<\/p>\n\n\n\n
    You can override the default settings at the database level by using CREATE DATABASE<\/a><\/code> statement:<\/p>\n\n\n
    CREATE<\/span> DATABASE<\/span> database_name\nCHARACTER<\/span> SET<\/span> character_set_name\nCOLLATE<\/span> collation_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    or using the ALTER DATABASE<\/code> statement:<\/p>\n\n\n
    ALTER<\/span> DATABASE<\/span> database_name\nCHARACTER<\/span> SET<\/span> character_set_name\nCOLLATE<\/span> collation_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    MySQL will use the database’s character set and collation by default for the tables that you create in the database.<\/p>\n\n\n\n
    3) Setting character sets and collations at the table level<\/h3>\n\n\n\n
    A database may contain tables with character sets and collations that are different from the database’s character set and collation.<\/p>\n\n\n\n
    You can specify the default character set and collation for a table when you create the table by using the CREATE TABLE<\/a><\/code> statement:<\/p>\n\n\n
    CREATE<\/span> TABLE<\/span> table_name(\n   ...\n)\nCHARACTER<\/span> SET<\/span> character_set_name\nCOLLATE<\/span> collation_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    ALTER<\/span> TABLE<\/span> table_name(\n  ...\n)\nCHARACTER<\/span> SET<\/span> character_set_name\nCOLLATE<\/span> collation_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    4) Setting character set and collation at column level<\/h3>\n\n\n\n
    A column of type CHAR<\/a><\/code> , VARCHAR<\/a><\/code> or TEXT<\/a><\/code> can have its own character set and collation different from the table’s character set and collation.<\/p>\n\n\n\n
    The CREATE TABLE<\/code> and ALTER TABLE<\/code> statement allows you to override the character set and collation for a specific column:<\/p>\n\n\n
    column_name [CHAR | VARCHAR | TEXT] (length)\nCHARACTER SET<\/span> character_set_name\nCOLLATE<\/span> collation_name<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The rules for setting the character set and collation are:<\/p>\n\n\n\n
    or when you alter the table using the ALTER TABLE<\/a><\/code>  statement:<\/p>\n\n\n