{"id":2332,"date":"2013-05-14T00:45:54","date_gmt":"2013-05-14T08:45:54","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2332"},"modified":"2023-10-23T03:25:18","modified_gmt":"2023-10-23T10:25:18","slug":"mysql-character-set","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-character-set\/","title":{"rendered":"MySQL Character Sets"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, we’ll explore MySQL character sets and their importance in how MySQL stores text data.<\/p>\n\n\n\n

In MySQL, the character set<\/strong> and collation<\/a><\/strong> are essential concepts that define how data is stored and sorted in text columns. <\/p>\n\n\n\n

Understanding character sets and collations are crucial for designing databases that can handle different languages and text requirements. <\/p>\n\n\n\n

Introduction to MySQL Character Sets<\/h2>\n\n\n\n

A character set is a collection of characters with a unique encoding. It defines the set of characters that can be used in a text column, such as letters, numbers, symbols, and special characters. <\/p>\n\n\n\n

MySQL supports various character sets, and the choice of character set determines the range of characters <\/strong>that can be stored in a column. <\/p>\n\n\n\n

Common character sets include utf8<\/code>, utf8mb4<\/code>, latin1<\/code>, utf16<\/code>, and many others. To list all character sets in the current MySQL server, you use the following statement:<\/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
Here’s the partial output:<\/p>\n\n\n
+----------+---------------------------------+---------------------+--------+\n| Charset  | Description                     | Default<\/span> collation   | Maxlen |\n+----------+---------------------------------+---------------------+--------+\n| armscii8 | ARMSCII-8<\/span> Armenian              | armscii8_general_ci |      1<\/span> |\n| ascii    | US ASCII                        | ascii_general_ci    |      1<\/span> |\n| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2<\/span> |\n| binary   | Binary pseudo charset           | binary              |      1<\/span> |\n...<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The Maxlen<\/code> column specifies the number of bytes that a character in a character set holds. <\/p>\n\n\n\n
Some character sets contain single-byte characters e.g., latin1<\/code> , latin2<\/code> , cp850<\/code> , etc., whereas other character sets contain multi-byte characters.<\/p>\n\n\n\n
The default character sets are utf8mb4<\/code> and utf8mb4_0900_ai_ci<\/code>. However, you can change them accordingly.<\/p>\n\n\n\n
Converting between different character sets<\/h2>\n\n\n\n
MySQL provides two functions that allow you to convert strings between different character sets: CONVERT<\/code> and CAST<\/code>.<\/p>\n\n\n\n
The syntax of the CONVERT<\/code> function is as follows:<\/p>\n\n\n
CONVERT(expression USING character_set_name)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The CAST<\/code> function is similar to the CONVERT<\/code> function. It converts a string to a different character set:<\/p>\n\n\n
CAST(string AS character_type CHARACTER SET<\/span> character_set_name)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Take a look at the following example of using the CAST<\/code> function:<\/p>\n\n\n
SELECT<\/span> CAST<\/span>(_latin1'MySQL character set'<\/span> AS<\/span> CHAR<\/span> CHARACTER<\/span> SET<\/span> utf8);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Setting character sets for client connections<\/h2>\n\n\n\n
To configure a character set for a client connection, you can do one of the following ways:<\/p>\n\n\n\n
1) Using the SET NAMES statement<\/h3>\n\n\n\n
Issue the SET NAME<\/code>  statement after the client connected to the MySQL database server. For example, to set a Unicode character set utf8mb4<\/code>, you use the following statement:<\/p>\n\n\n
SET<\/span> NAMES<\/span> 'utf8mb4'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using –default-character-set option<\/h3>\n\n\n\n
If the application supports the --default-character-set<\/code>  option, you can use it to set the character set. <\/p>\n\n\n\n
For example, mysql client tool supports --default-character-set<\/code> option and you can set it up in the configuration file as follows:<\/p>\n\n\n
[mysql]\ndefault-character-set=utf8mb4<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Using the charset in connection strings<\/h3>\n\n\n\n
Some MySQL connectors allow you to specify a character set. For example, if you use PHP PDO<\/a>, you can set the character set in the data source name as follows:<\/p>\n\n\n
$dsn =\"mysql:host=$host;dbname=$db;charset=utf8mb4\"<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Regardless of which way you use it, make sure that the character set used by the application matches the character set stored in the MySQL database server.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Character sets define how MySQL stores data in text columns.<\/li>\n\n\n\n
  • Use the SHOW CHARACTER SET<\/code> statement to list all character sets.<\/li>\n\n\n\n
  • Use the CONVERT()<\/code> or CAST()<\/code> function to convert between character sets.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t