常用命令
diguage opened this issue · 0 comments
diguage commented
-- 查看数据库字符集
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;