mysql> use db_name
Database changed
mysql> SELECT @@character_set_database;
+--------------------------+
| @@character_set_database |
+--------------------------+
| latin1 |
+--------------------------+
1 row in set (0.00 sec)
or:
mysql> show variables like "character_set_database";
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.01 sec)
mysql> SHOW TABLE STATUS where Name like 'wp_users';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| wp_users | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 32768 | 13631488 | 2 | 2015-12-09 23:24:18 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)
Great SQL Query for a total view for all tables
SELECT TABLE_SCHEMA,
TABLE_NAME,
CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
COLUMN_NAME,
COLUMN_TYPE,
C.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
WHERE TABLE_SCHEMA=SCHEMA()
AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
ORDER BY TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
;
Output:
+--------------+---------------------+------------------+-----------------------+--------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | DEFAULT_CHAR_SET | COLUMN_NAME | COLUMN_TYPE | CHARACTER_SET_NAME |
+--------------+---------------------+------------------+-----------------------+--------------+--------------------+
| wiki | wp_commentmeta | utf8mb4 | meta_key | varchar(255) | utf8mb4 |
| wiki | wp_commentmeta | utf8mb4 | meta_value | longtext | utf8mb4 |
| wiki | wp_comments | utf8mb4 | comment_agent | varchar(255) | utf8mb4 |
| wiki | wp_comments | utf8mb4 | comment_approved | varchar(20) | utf8mb4 |
| wiki | wp_comments | utf8mb4 | comment_author_email | varchar(100) | utf8mb4 |
| wiki | wp_comments | utf8mb4 | comment_author_IP | varchar(100) | utf8mb4 |
| wiki | wp_comments | utf8mb4 | comment_author_url | varchar(200) | utf8mb4 |
| wiki | wp_comments | utf8mb4 | comment_content | text | utf8mb4 |
| wiki | wp_comments | utf8mb4 | comment_type | varchar(20) | utf8mb4 |
| wiki | wp_links | utf8mb4 | link_description | varchar(255) | utf8mb4 |
| wiki | wp_links | utf8mb4 | link_image | varchar(255) | utf8mb4 |
........................................
No Comments