MySQL查看和修改字符集的方法

浅浅的花香味﹌ 2023-01-01 08:50 90阅读 0赞

--------------------

摘自：[明王不动心][Link 1]

--------------------

## 一、查看字符集 ##

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)

2.查看MYSQL所支持的字符集

> 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)

3.查看库的字符集

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

> 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)

4.查看表的字符集

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

> mysql> show table status from class\_7 like ‘test\_info’;

5.查看表中所有列的字符集

语法：show full columns from 表名;

> mysql> show full columns from test\_info;

## 二、设置字符集 ##

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

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)

2.修改字符集

修改全局字符集

/*建立连接使用的编码*/
    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: 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)

修改表的字符集

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

> alter table test1 convert to character set utf8;

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)

修改字段的字符集

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

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

[Link 1]: https://www.cnblogs.com/yangmingxianshen/p/7999428.html