Develop

MySQL 字符集相关

查看字符集

查看服务器字符集

1
SHOW VARIABLES LIKE '%char%';

查看数据库字符集

1
SHOW CREATE DATABASE db_name;

查看表的字符集

1
2
SHOW CREATE TABLE table_name;
SHOW TABLE STATUS FROM db_name LIKE '%TABLE_NAME%';

查看字段（列）字符集

1
SHOW FULL COLUMNS FROM table_name;

修改字符集

修改数据库默认字符集

1
2
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
ALTER DATABASE my_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改表默认字符集

1
2
ALTER TABLE table_name DEFAULT CHARACTER SET character_name [COLLATE...];
ALTER TABLE my_table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改表默认字符集，同时转换有关字段字符集

1
2
ALTER TABLE table_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段（列）字符集：

1
2
ALTER TABLE table_name CHANGE col_name new_col_name CHARACTER SET character_name [COLLATE ...];
ALTER TABLE my_table CHANGE my_column my_column VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;