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

一、查看字符集

1.查看MYSQL数据库服务器和数据库字符集

方法一：show variables like '%character%';

方法二：show variables like 'collation%';

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)方法一：

show charset;

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所支持的字符集

语法：show database status from 库名 like 表名;

mysql> show create database shiyan\G*************************** 1. row ***************************  Database: shiyanCreate Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET gbk */1 row in set (0.00 sec)

语法：show table status from 库名 like 表名;

mysql> show table status from class_7 like 'test_info';

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)查看表的字符集

语法：show full columns from 表名;

mysql> show full columns from test_info;

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)所有列的字符集

设置字符集一般有两种方法，一种是在创建表的时候设置字符集，另一种是表建成之后修改字符集。

1.创建时指定字符集

创建库的时候指定字符集：

语法：create database 库名 default character set=字符集；

create database db2 default character set=utf8

语法：create table 表名（属性）default character set = 字符集；

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;修改全局字符集

语法：alter database 库名 default character set 字符集;

alter database shiyan default character set gbk;

mysql> show create database shiyan\G*************************** 1. row ***************************  Database: shiyanCreate 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: shiyanCreate Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET gbk */1 row in set (0.00 sec)

语法：alter table 表名 convert to character set 字符集;

alter table test1 convert to character set utf8;

mysql> show create table test1\G*************************** 1. row ***************************  Table: test1Create 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: 0mysql> show create table test1\G*************************** 1. row ***************************  Table: test1Create 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)修改列表的字符集

语法：alter table 表名 modify 字段名字段属性 character set gbk；

alter table test1 modify name char(10) character set gbk;

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: 0mysql> 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)修改字段的字符集

更多相关文章

随机推荐