常用命令

[diguage]

-- 查看数据库字符集
USE db_name;
SELECT @@character_set_database, @@collation_database;
show variables like 'character_set_database';
show variables like 'collation_database';


SHOW CREATE DATABASE "schemaName";

-- 查看数据库字符集
-- https://stackoverflow.com/a/1049958/951836
SELECT schema_name,
       default_character_set_name,
       DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;

-- 查看表字符集
SELECT 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
  AND t.table_schema = "schemaName";

-- 查看字段字符集
SELECT column_name, character_set_name
FROM information_schema.`COLUMNS`
WHERE table_schema = "schemaName"
  AND table_name = "tableName";

-- 查看当前 Schema 中特定类型字段字符集
-- https://stackoverflow.com/a/4805964/951836
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;

-- https://stackoverflow.com/a/38996782/951836
SHOW FULL COLUMNS FROM my_tablename;

-- 查看默认字符集
SHOW VARIABLES LIKE 'character%';

-- 查询支持的中日韩字符集
-- https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/faqs-cjk.html
SELECT CHARACTER_SET_NAME, DESCRIPTION
FROM INFORMATION_SCHEMA.CHARACTER_SETS
WHERE DESCRIPTION LIKE '%Chin%'
   OR DESCRIPTION LIKE '%Japanese%'
   OR DESCRIPTION LIKE '%Korean%'
ORDER BY CHARACTER_SET_NAME;

-- 查看默认存储引擎
SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

-- 查看 InnoDB 的运行状态
-- https://mariadb.com/kb/en/show-engine-innodb-status/
SHOW ENGINE INNODB STATUS;