查看数据库的排序规则
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;