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
Administering MySQL from the command line, 13 (15)
- How to install MySQL 5.7 on CentOS 7
- How to install MySQL 8.0 in Ubuntu 20.04
- How to install MySQL 8.0 in Debian
- How to install MariaDB on Alpine Linux
- How to install MariaDB on NetBSD?
- MySQL 8.0, change root password
- MySQL server administration – Basic
- Create / modify / delete tables in MySQL
- MySQL user administration
- MySQL – Execute SQL script
- Disable innodb engine in MySQL
- Show MySQL storage engines
- Character set available in MySQL
- Connect to a MySQL server from the LAN
- MySQL can not write in the /tmp dir