MySQL 캐릭터셋 확인 COLLATION CHARACTER SET

mysql 캐릭터 셋이 궁금한 경우 확인하는 방법입니다. 요즘에는 대부분 utf8mb4를 사용하고 있는데요. 새로운 mysql 서버에 접속했을 때 궁금할 수 있어요. 쿼리로 확인할 수 있는 방법입니다.

데이터베이스 문자셋 캐릭터셋 확인

select SCHEMA_NAME,default_character_set_name FROM information_schema.SCHEMATA;

+--------------------+----------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME |
+--------------------+----------------------------+
| information_schema | utf8mb3                    |
| performance_schema | utf8mb4                    |
| test               | utf8mb4                    |
+--------------------+----------------------------+

테이블 문자셋 확인

SELECT TABLE_SCHEMA, T.table_name, CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
       WHERE CCSA.collation_name = T.table_collation;

+--------------------+-------------------------------+--------------------+
| TABLE_SCHEMA       | TABLE_NAME                    | CHARACTER_SET_NAME |
+--------------------+-------------------------------+--------------------+
| performance_schema | processlist                   | utf8mb4            |
| performance_schema | session_account_connect_attrs | utf8mb4            |
| performance_schema | global_status                 | utf8mb4            |
| performance_schema | session_status                | utf8mb4            |
| performance_schema | global_variables              | utf8mb4            |
| performance_schema | session_variables             | utf8mb4            |
| performance_schema | variables_info                | utf8mb4            |
| performance_schema | persisted_variables           | utf8mb4            |
| test               | test_table                    | utf8mb4            |
+--------------------+-------------------------------+--------------------+

컬럼 문자셋 확인

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, character_set_name FROM information_schema.`COLUMNS`

+--------------------+---------------------------------------+-----------------------------------+--------------------+
| TABLE_SCHEMA       | TABLE_NAME                            | COLUMN_NAME                       | CHARACTER_SET_NAME |
+--------------------+---------------------------------------+-----------------------------------+--------------------+
| information_schema | CHARACTER_SETS                        | CHARACTER_SET_NAME                | utf8mb3            |
| information_schema | CHARACTER_SETS                        | DEFAULT_COLLATE_NAME              | utf8mb3            |
| information_schema | CHARACTER_SETS                        | DESCRIPTION                       | utf8mb3            |
| information_schema | CHARACTER_SETS                        | MAXLEN                            | NULL               |
+--------------------+---------------------------------------+-----------------------------------+--------------------+

---

필요한 where 조건을 추가하여 조회하면 유용할 것 같습니다.