Character set available in MySQL

MySQL

MySQL offers several types of charset (CHARACTER_SET), thereby satisfying the most varied projects.

Show character set supported by the MySQL Server

> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------
| Charset  | Description                 | Default collation   
+----------+-----------------------------+--------------------
| big5     | Big5 Traditional Chinese    | big5_chinese_ci    
| dec8     | DEC West European           | dec8_swedish_ci    
| cp850    | DOS West European           | cp850_general_ci   
| hp8      | HP West European            | hp8_english_ci     
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci   
| latin1   | cp1252 West European        | latin1_swedish_ci  
| latin2   | ISO 8859-2 Central European | latin2_general_ci  
| swe7     | 7bit Swedish                | swe7_swedish_ci    
| ascii    | US ASCII                    | ascii_general_ci   
....

Show available COLLATION for a CHARACTER SET

> SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS 
WHERE CHARACTER_SET_NAME = 'utf8';
+--------------------+
| COLLATION_NAME     |
+--------------------+
| utf8_general_ci    |
| utf8_bin           |
| utf8_unicode_ci    |
| utf8_icelandic_ci  |
| utf8_latvian_ci    |
| utf8_romanian_ci   |
| utf8_slovenian_ci  |
...

View COLLATION, CHARACTER_SET for a specific DB

> SELECT * from information_schema.SCHEMATA WHERE SCHEMA_NAME='librebyte';

+---------+--------------+-------------+----------------------------+-------------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | librebyte   | utf8                       | utf8_general_ci        | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

Show COLLATION, CHARACTER_SET for the columns in a particular table

Here we take as an example the BD: Sakila

> SELECT COLUMN_NAME,COLLATION_NAME, CHARACTER_SET_NAME FROM 
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'actor' AND 
TABLE_SCHEMA = 'sakila';
+-------------+-----------------+--------------------+
| COLUMN_NAME | COLLATION_NAME  | CHARACTER_SET_NAME |
+-------------+-----------------+--------------------+
| actor_id    | NULL            | NULL               |
| first_name  | utf8_general_ci | utf8               |
| last_name   | utf8_general_ci | utf8               |
| last_update | NULL            | NULL               |
+-------------+-----------------+--------------------+

Recommended reading

– F. van der lan, Rick. SQL for MySQL Developers, Part III, Chapter 22.

Spanish Video

YouTube video

Administering MySQL from the command line, 13 (15)

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.