MySQL查看与修改字符集的方法实例教程

mysql> show variables like '%character%';
+--------------------------+--------------------------------------+
| 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  | utf8         |
| character_set_system  | utf8         |
| character_sets_dir  | /usr/local/mysql5535/share/charsets/ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)

方法一：

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

方法二：

mysql> show charset;
+----------+-----------------------------+---------------------+--------+
| charset | description     | default collation | maxlen |
+----------+-----------------------------+---------------------+--------+
| big5  | big5 traditional chinese | big5_chinese_ci  |  2 |
| dec8  | dec west european   | dec8_swedish_ci  |  1 |
| cp850 | dos west european   | cp850_general_ci |  1 |
| hp8  | hp west european   | hp8_english_ci  |  1 |
| koi8r | koi8-r relcom russian  | koi8r_general_ci |  1 |
| latin1 | cp1252 west european  | latin1_swedish_ci |  1 |
| latin2 | iso 8859-2 central european | latin2_general_ci |  1 |
| swe7  | 7bit swedish    | swe7_swedish_ci  |  1 |
| ascii | us ascii     | ascii_general_ci |  1 |
| ujis  | euc-jp japanese    | ujis_japanese_ci |  3 |
| sjis  | shift-jis japanese   | sjis_japanese_ci |  2 |
| hebrew | iso 8859-8 hebrew   | hebrew_general_ci |  1 |
| tis620 | tis620 thai     | tis620_thai_ci  |  1 |
| euckr | euc-kr korean    | euckr_korean_ci  |  2 |
| koi8u | koi8-u ukrainian   | koi8u_general_ci |  1 |
| gb2312 | gb2312 simplified chinese | gb2312_chinese_ci |  2 |
| greek | iso 8859-7 greek   | greek_general_ci |  1 |
| cp1250 | windows central european | cp1250_general_ci |  1 |
| gbk  | gbk simplified chinese  | gbk_chinese_ci  |  2 |
| latin5 | iso 8859-9 turkish   | latin5_turkish_ci |  1 |
| armscii8 | armscii-8 armenian   | armscii8_general_ci |  1 |
| utf8  | utf-8 unicode    | utf8_general_ci  |  3 |
| ucs2  | ucs-2 unicode    | ucs2_general_ci  |  2 |
| cp866 | dos russian     | cp866_general_ci |  1 |
| keybcs2 | dos kamenicky czech-slovak | keybcs2_general_ci |  1 |
| macce | mac central european  | macce_general_ci |  1 |
| macroman | mac west european   | macroman_general_ci |  1 |
| cp852 | dos central european  | cp852_general_ci |  1 |
| latin7 | iso 8859-13 baltic   | latin7_general_ci |  1 |
| utf8mb4 | utf-8 unicode    | utf8mb4_general_ci |  4 |
| cp1251 | windows cyrillic   | cp1251_general_ci |  1 |
| utf16 | utf-16 unicode    | utf16_general_ci |  4 |
| cp1256 | windows arabic    | cp1256_general_ci |  1 |
| cp1257 | windows baltic    | cp1257_general_ci |  1 |
| utf32 | utf-32 unicode    | utf32_general_ci |  4 |
| binary | binary pseudo charset  | binary    |  1 |
| geostd8 | geostd8 georgian   | geostd8_general_ci |  1 |
| cp932 | sjis for windows japanese | cp932_japanese_ci |  2 |
| eucjpms | ujis for windows japanese | eucjpms_japanese_ci |  3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)

查看mysql所支持的字符集

mysql> show create database shiyan\g
*************************** 1. row ***************************
  database: shiyan
create database: create database `shiyan` /*!40100 default character set gbk */
1 row in set (0.00 sec)

mysql> show table status from class_7 like 'test_info';
+-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+-
| name  | engine | version | row_format | rows | avg_row_length | data_leate_time   | update_time | check_time | collation  | checksum | 
+-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+-
| test_info | innodb |  10 | compact | 10 |   1638 |  17-12-05 19:01:55 | null  | null  | utf8_general_ci |  null | 
+-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+-
1 row in set (0.00 sec)

查看表的字符集

mysql> show full columns from test_info;
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| field | type  | collation  | null | key | default | extra | privileges      | comment |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(3) | null   | no | pri | null |  | select,insert,update,references |   |
| name | char(12) | utf8_general_ci | yes |  | null |  | select,insert,update,references |   |
| dorm | char(10) | utf8_general_ci | yes |  | null |  | select,insert,update,references |   |
| addr | char(12) | utf8_general_ci | yes |  | 未知 |  | select,insert,update,references |   |
| score | int(3) | null   | yes |  | null |  | select,insert,update,references |   |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
5 rows in set (0.00 sec)

所有列的字符集

create database db2 default character set=utf8

mysql> create table test1(id int(6),name char(10)) default character set = 'gbk';
query ok, 0 rows affected (0.39 sec)

/*建立连接使用的编码*/
set character_set_connection=utf8;
/*数据库的编码*/
set character_set_database=utf8;
/*结果集的编码*/
set character_set_results=utf8;
/*数据库服务器的编码*/
set character_set_server=utf8;

set character_set_system=utf8;

set collation_connection=utf8;

set collation_database=utf8;

set collation_server=utf8;

修改全局字符集

mysql> show create database shiyan\g
*************************** 1. row ***************************
  database: shiyan
create database: create database `shiyan` /*!40100 default character set utf8 */
1 row in set (0.00 sec)

mysql> alter database shiyan default character set gbk;
query ok, 1 row affected (0.00 sec)

mysql> show create database shiyan\g
*************************** 1. row ***************************
  database: shiyan
create database: create database `shiyan` /*!40100 default character set gbk */
1 row in set (0.00 sec)

mysql> show create table test1\g
*************************** 1. row ***************************
  table: test1
create table: create table `test1` (
 `id` int(6) default null,
 `name` char(10) default null
) engine=innodb default charset=gbk #原字符集
1 row in set (0.00 sec)

mysql> alter table test1 convert to character set utf8;
query ok, 0 rows affected (0.58 sec)
records: 0 duplicates: 0 warnings: 0

mysql> show create table test1\g
*************************** 1. row ***************************
  table: test1
create table: create table `test1` (
 `id` int(6) default null,
 `name` char(10) default null
) engine=innodb default charset=utf8 #修改后的字符集
1 row in set (0.00 sec)

修改列表的字符集

mysql> show full columns from test1;
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| field | type  | collation  | null | key | default | extra | privileges      | comment |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(6) | null   | yes |  | null |  | select,insert,update,references |   |
| name | char(10) | utf8_general_ci | yes |  | null |  | select,insert,update,references |   |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)

mysql> alter table test1 modify name char(10) character set gbk;
query ok, 0 rows affected (0.58 sec)
records: 0 duplicates: 0 warnings: 0

mysql> show full columns from test1;
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| field | type  | collation  | null | key | default | extra | privileges      | comment |
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(6) | null   | yes |  | null |  | select,insert,update,references |   |
| name | char(10) | gbk_chinese_ci | yes |  | null |  | select,insert,update,references |   |
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)

修改字段的字符集