MySQL 查看编码 排序规则 - 陈达辉

查看数据库的排序规则

mysql> show variables like \'collation%\';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

查看数据库的编码格式

mysql> show variables like \'char%\';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

查看字段的排序规则

mysql> show create table tablename;

查看表字段信息

mysql> desc tablename;

查看当前数据库编码

mysql> use databasename;
Database changed

mysql> show variables like \'character_set_database\'; 
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.03 sec)

修改当前数据库的编码

mysql> use databasename;
Database changed

mysql> alter database databasename character set latin1;  #将之前的utf8编码修改为latin1编码
Query OK, 1 row affected (0.00 sec)
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)

查看字段编码

SHOW FULL COLUMNS FROM tablename;

修改字段的字符集

ALTER TABLE tablename CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如：ALTER TABLE users CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;