MySQL字符集介绍

1,597次阅读
没有评论

MySQL字符集介绍

什么是字符集?

字符集,character set,就是一套表示字符的符号和这些的符号的底层编码;而校验规则,则是在字符集内用于比较字符的一套规则。

简单的说,字符集就是一套文字符号及其编码、比较规则的集合,第一个计算机字符集ASC2,MySQL数据库字符集包括字符集和校对规则两个概念,字符集是定义数据库里面的内容字符串的存储方式,而校对规则是定义比较字符串的方式

编译MySQL的时候,指定字符集了,这样以后建库的时候就直接create database db_name;

二进制安装MySQL,就没有指定字符集了,此时字符集默认就是latinl,此时需要建立UTF8字符集的库,就要指定UTF8字符集建库

<span class="line">1</span>
<span class="line">create database oldboy default character <span class="built_in">set</span> utf8 deaultcollate=utf8_general_ci;</span>

在互联网环境中,使用MySQL时常用的字符集

常用字符集 一个汉字长度(字节) 说明
GBK 2 不是国际标准,对中文支持的很好
UTF-8 3 中英文混合的环境,建议使用此字符集,用的比较多的
Latinl 1 MySQL的默认字符集
Utf8mb4 4 UTF-8 Unicode,移动互联网

工作中MySQL如何选择字符集?

  1. 如果处理各种各样的文字,发布到不同语言国家地区,应选Unicode字符集。对mysql来说就是UTF-8(每个汉字三字节),如果应用需处理英文,仅有少量汉字UTF-8更好。
  2. 如只需支持中文,并且数据量很大,性能要求也很高,可以选择GBK(定长 每个汉字占双字节,英文也占双字节),如果需大量运算,比较排序等,定义长字符集,更快,性能高
  3. 处理移动互联网业务,可能需要使用utf8mb4字符集

MySQL字符集相关命令

查看MySQL字符集设置情况

<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
<span class="line">mysql> show variables like <span class="string">'character%'</span>;</span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p3306 -S /data/3306/mysql.sock -e "show variables like 'character%'"</span></span>
<span class="line">+--------------------------+----------------------------+</span>
<span class="line">| Variable_name            | Value                      |</span>
<span class="line">+--------------------------+----------------------------+  </span>
<span class="line">| character_<span class="built_in">set</span>_client     | utf8                       |  --客户端字符集</span>
<span class="line">| character_<span class="built_in">set</span>_connection | utf8                       |	 --连接字符集,客户端</span>
<span class="line">| character_<span class="built_in">set</span>_database   | utf8                       |  --数据库字符集,配置文件指定或建库表指定</span>
<span class="line">| character_<span class="built_in">set</span>_filesystem | binary                     |  --文件系统的字符集</span>
<span class="line">| character_<span class="built_in">set</span>_results    | utf8                       |  --返回结果字符集 客户端</span>
<span class="line">| character_<span class="built_in">set</span>_server     | utf8                       |  --服务器字符集,配置文件指定或建库表指定</span>
<span class="line">| character_<span class="built_in">set</span>_system     | utf8                       |  --系统的字符集</span>

查看字符集及校验规则

<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p3306 -S /data/3306/mysql.sock -e "show character set;"</span></span>
<span class="line">+----------+-----------------------------+---------------------+--------+</span>
<span class="line">| Charset  | Description                 | Default collation   | Maxlen |</span>
<span class="line">+----------+-----------------------------+---------------------+--------+</span>
<span class="line">| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |</span>
<span class="line">| dec8     | DEC West European           | dec8_swedish_ci     |      1 |</span>
<span class="line">| cp850    | DOS West European           | cp850_general_ci    |      1 |</span>
<span class="line">| hp8      | HP West European            | hp8_english_ci      |      1 |</span>
<span class="line">| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |</span>
<span class="line">| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |</span>
<span class="line">| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |</span>
<span class="line">| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |</span>
<span class="line">| ascii    | US ASCII                    | ascii_general_ci    |      1 |</span>
<span class="line">| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |</span>
<span class="line">| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |</span>
<span class="line">| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |</span>
<span class="line">| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |</span>
<span class="line">| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |</span>
<span class="line">| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |</span>
<span class="line">| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |</span>
<span class="line">| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |</span>
<span class="line">| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |</span>
<span class="line">| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |</span>
<span class="line">| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |</span>
<span class="line">| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |</span>
<span class="line">| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |</span>
<span class="line">| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |</span>
<span class="line">| cp866    | DOS Russian                 | cp866_general_ci    |      1 |</span>
<span class="line">| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |</span>
<span class="line">| macce    | Mac Central European        | macce_general_ci    |      1 |</span>
<span class="line">| macroman | Mac West European           | macroman_general_ci |      1 |</span>
<span class="line">| cp852    | DOS Central European        | cp852_general_ci    |      1 |</span>
<span class="line">| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |</span>
<span class="line">| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |</span>
<span class="line">| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |</span>
<span class="line">| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |</span>
<span class="line">| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |</span>
<span class="line">| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |</span>
<span class="line">| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |</span>
<span class="line">| binary   | Binary pseudo charset       | binary              |      1 |</span>
<span class="line">| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |</span>
<span class="line">| cp932    | SJIS <span class="keyword">for</span> Windows Japanese   | cp932_japanese_ci   |      2 |</span>
<span class="line">| eucjpms  | UJIS <span class="keyword">for</span> Windows Japanese   | eucjpms_japanese_ci |      3 |</span>
<span class="line">+----------+-----------------------------+---------------------+--------+</span>

查看库的字符集

<span class="line">1</span>
2
3
4
5
6
<span class="line">mysql> show create database db;</span>
<span class="line">+----------+-------------------------------------------------------------+</span>
<span class="line">| Database | Create Database                                             |</span>
<span class="line">+----------+-------------------------------------------------------------+</span>
<span class="line">| db       | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 */ |</span>
<span class="line">+----------+-------------------------------------------------------------+</span>

查看表的字符集

<span class="line">1</span>
2
3
4
5
6
7
<span class="line">mysql> show create table db_tb\G</span>
<span class="line">*************************** 1. row ***************************</span>
<span class="line">       Table: db_tb</span>
<span class="line">Create Table: CREATE TABLE `db_tb` (</span>
<span class="line">  `id` int(4) DEFAULT NULL,</span>
<span class="line">  `name` varchar(16) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</span>

查询MySQL数据库所支持的字符集和校验规则

  • 查询所有
<span class="line">1</span>
<span class="line">mysql> show collation;</span>
  • like进行筛选,之查看utf8的
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<span class="line">mysql> show collation like <span class="string">'utf8%'</span>;</span>
<span class="line">+--------------------------+---------+-----+---------+----------+---------+</span>
<span class="line">| Collation                | Charset | Id  | Default | Compiled | Sortlen |</span>
<span class="line">+--------------------------+---------+-----+---------+----------+---------+</span>
<span class="line">| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |</span>
<span class="line">| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |</span>
<span class="line">| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |</span>
<span class="line">| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |</span>
<span class="line">| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |</span>
<span class="line">| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |</span>
<span class="line">| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |</span>
<span class="line">| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |</span>
<span class="line">| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |</span>
<span class="line">| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |</span>
<span class="line">| utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 |</span>
<span class="line">| utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 |</span>
<span class="line">| utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 |</span>
<span class="line">| utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 |</span>
<span class="line">| utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 |</span>
<span class="line">| utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 |</span>
<span class="line">| utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 |</span>
<span class="line">| utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 |</span>
<span class="line">| utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 |</span>
<span class="line">| utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 |</span>
<span class="line">| utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 |</span>
<span class="line">| utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 |</span>
<span class="line">| utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |</span>
<span class="line">| utf8mb4_general_ci       | utf8mb4 |  45 | Yes     | Yes      |       1 |</span>
<span class="line">| utf8mb4_bin              | utf8mb4 |  46 |         | Yes      |       1 |</span>
<span class="line">| utf8mb4_unicode_ci       | utf8mb4 | 224 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_icelandic_ci     | utf8mb4 | 225 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_latvian_ci       | utf8mb4 | 226 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_romanian_ci      | utf8mb4 | 227 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_slovenian_ci     | utf8mb4 | 228 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_polish_ci        | utf8mb4 | 229 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_estonian_ci      | utf8mb4 | 230 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_spanish_ci       | utf8mb4 | 231 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_swedish_ci       | utf8mb4 | 232 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_turkish_ci       | utf8mb4 | 233 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_czech_ci         | utf8mb4 | 234 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_danish_ci        | utf8mb4 | 235 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_lithuanian_ci    | utf8mb4 | 236 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_slovak_ci        | utf8mb4 | 237 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_spanish2_ci      | utf8mb4 | 238 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_roman_ci         | utf8mb4 | 239 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_persian_ci       | utf8mb4 | 240 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_esperanto_ci     | utf8mb4 | 241 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_hungarian_ci     | utf8mb4 | 242 |         | Yes      |       8 |</span>
<span class="line">| utf8mb4_sinhala_ci       | utf8mb4 | 243 |         | Yes      |       8 |</span>
<span class="line">+--------------------------+---------+-----+---------+----------+---------+</span>

调整字符集方式

调整当前客户端字符集

语法格式 set names [character set]

在登录时调整字符集

语法格式 mysql -u[user] -p

 此处含有隐藏内容,需要正确输入密码后可见!

–default-character-set=[character set]

实战演练set names更改字符集方式

  • 创建一个数据库为名称为db
<span class="line">1</span>
2
<span class="line">mysql> create database db;</span>
<span class="line">Query OK, 1 row affected (0.00 sec)</span>
  • 查看db库字符集
<span class="line">1</span>
2
3
4
5
6
7
<span class="line">mysql> show create database db;</span>
<span class="line">+----------+-------------------------------------------------------------+</span>
<span class="line">| Database | Create Database                                             |</span>
<span class="line">+----------+-------------------------------------------------------------+</span>
<span class="line">| db       | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 */ |</span>
<span class="line">+----------+-------------------------------------------------------------+</span>
1 row in set (0.00 sec)
  • 进入库db,创建一个表名称为db_table
<span class="line">1</span>
2
3
4
<span class="line">mysql> use db</span>
<span class="line">Database changed</span>
<span class="line">mysql> create table db_table(id int(4),name char(10));</span>
<span class="line">Query OK, 0 rows affected (0.01 sec)</span>
  • 查看表db_table的字符集
<span class="line">1</span>
2
3
4
5
6
7
8
<span class="line">mysql> show create table db_table\G</span>
<span class="line">*************************** 1. row ***************************</span>
<span class="line">       Table: db_table</span>
<span class="line">Create Table: CREATE TABLE `db_table` (</span>
<span class="line">  `id` int(4) DEFAULT NULL,</span>
<span class="line">  `name` char(10) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
  • 往字段id和name插入内容
<span class="line">1</span>
2
<span class="line">mysql> insert into db_table(id,name) values(1,<span class="string">'张三'</span>);</span>
<span class="line">Query OK, 1 row affected (0.00 sec)</span>
  • 查看刚插入的内容,现在我们发现并没有乱码
<span class="line">1</span>
2
3
4
5
6
7
<span class="line">mysql> select * from db_table;</span>
<span class="line">+------+--------+</span>
<span class="line">| id   | name   |</span>
<span class="line">+------+--------+</span>
<span class="line">|    1 | 张三   |</span>
<span class="line">+------+--------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
  • 修改当前的字符集为gbk
<span class="line">1</span>
2
<span class="line">mysql> <span class="built_in">set</span> names gbk;</span>
<span class="line">Query OK, 0 rows affected (0.00 sec)</span>
  • 查看当前数据库的字符集设置情况,我们发现客户端的三个选项都变成了GBK的字符集
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
14
<span class="line">mysql> show variables like <span class="string">'character_set%'</span>;</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line">| Variable_name            | Value                                     |</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line">| character_<span class="built_in">set</span>_client     | gbk                                       |</span>
<span class="line">| character_<span class="built_in">set</span>_connection | gbk                                       |</span>
<span class="line">| character_<span class="built_in">set</span>_database   | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_filesystem | binary                                    |</span>
<span class="line">| character_<span class="built_in">set</span>_results    | gbk                                       |</span>
<span class="line">| character_<span class="built_in">set</span>_server     | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_system     | utf8                                      |</span>
<span class="line">| character_sets_dir       | /application/mysql-5.5.32/share/charsets/ |</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line">8 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
  • 往字段内插入内容
<span class="line">1</span>
2
<span class="line">mysql> insert into db_table(id,name) values(2,<span class="string">'李四'</span>);</span>
<span class="line">Query OK, 1 row affected (0.04 sec)</span>
  • 查看表内容,这是我们就发现乱码了
<span class="line">1</span>
2
3
4
5
6
7
8
<span class="line">mysql> select * from db_table;</span>
<span class="line">+------+--------+</span>
<span class="line">| id   | name   |</span>
<span class="line">+------+--------+</span>
<span class="line">|    1 | օɽ       |</span>
<span class="line">|    2 | 李四   |</span>
<span class="line">+------+--------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.03 sec)</span>
  • 再把字符集改为utf8
<span class="line">1</span>
2
<span class="line">mysql> <span class="built_in">set</span> names utf8;</span>
<span class="line">Query OK, 0 rows affected (0.00 sec)</span>
  • 查看当前数据库的字符集设置情况
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
14
<span class="line">mysql> show variables like <span class="string">'character_set%'</span>;</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line">| Variable_name            | Value                                     |</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line">| character_<span class="built_in">set</span>_client     | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_connection | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_database   | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_filesystem | binary                                    |</span>
<span class="line">| character_<span class="built_in">set</span>_results    | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_server     | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_system     | utf8                                      |</span>
<span class="line">| character_sets_dir       | /application/mysql-5.5.32/share/charsets/ |</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line">8 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
  • 再查看表内容,我们发现刚才修改字符集为gbk时插入的内容已经乱码了
<span class="line">1</span>
2
3
4
5
6
7
8
<span class="line">mysql> select * from db_table;</span>
<span class="line">+------+-----------+</span>
<span class="line">| id   | name      |</span>
<span class="line">+------+-----------+</span>
<span class="line">|    1 | 张三      |</span>
<span class="line">|    2 | 鏉庡洓    |</span>
<span class="line">+------+-----------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

小结:字符集不同我们创建或者查看的内容都会变成乱码,而set names只是修改当前的字符集情况

执行SQL文件插入中文数据不乱码方法

  • 在执行SQL语句之前更改字符集
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
<span class="line">[root@MySQL ~]<span class="comment"># cat db.sql </span></span>
<span class="line">SET NAMES UTF8;</span>
<span class="line">CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db` /*!40100 DEFAULT CHARACTER SET utf8 */;</span>
<span class="line">USE `db`;</span>
<span class="line">DROP TABLE IF EXISTS `db_table`;</span>
<span class="line">CREATE TABLE `db_table` (</span>
<span class="line">  `id` int(4) DEFAULT NULL,</span>
<span class="line">  `name` char(10) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8;</span>
<span class="line">LOCK TABLES `db_table` WRITE;</span>
<span class="line">INSERT INTO `db_table` VALUES (1,<span class="string">'张三'</span>),(2,<span class="string">'鏉庡洓'</span>);</span>
<span class="line">UNLOCK TABLES;</span>
  • SQL语句导入的时候指定字符集
<span class="line">1</span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456 --default-character-set=utf8 oldboy < db.sql</span></span>

永久更改字符集

通过修改更my.cnf实现修改mysql客户端的字符集

  • 客户端
<span class="line">1</span>
2
<span class="line">[client]</span>
<span class="line">character-set-server=utf8</span>
  • 服务端
<span class="line">1</span>
2
3
<span class="line">[mysqld]</span>
<span class="line">character-set-server=utf8  --适合5.5</span>
<span class="line">default-characyer-set=utf8 --适合5.1</span>

命令方式永久更改字符集

  • 修改数据库库默认编码
<span class="line">1</span>
2
3
<span class="line"><span class="built_in">help</span> alter database</span>
<span class="line">alter database [your db name] charset [your character settting]</span>
<span class="line">mysql> show character <span class="built_in">set</span>;</span>

下面方法只能适应新数据,老数据字符集没有更改,库内的表也没有改

<span class="line">1</span>
2
<span class="line">mysql> alter database oldboy CHARACTER SET latin1 COLLATE = latin1_swedish_ci;</span>
<span class="line">mysql> show create database oldboy\g</span>

更改表

<span class="line">1</span>
<span class="line">mysql> show create table <span class="built_in">test</span>\G</span>

统一MySQL数据库客户及服务端字符集总结

  1. 客户端字符集设置为“set names utf8”,这样可以确保插入后的中文,不出现乱码,但是对执行set names utf8;前插入的中文无效,此命令临时生效
  2. 和设置客户端字符集“set names utf8”命令有相同作用的方法还有,mysql命令指定utf8字符集参数登录,以及在my.cnf里更改参数实现。
  3. 在MySQL的my.cnf配置文件里的[client]模块下添加字符集配置,生效后,相当于命令行“set names utf8;”的效果,由于更改的是客户端、连接和返回结果三个字符集,应此无需重启服务就生效。
  4. 在MySQL的my.cnf配置文嘉你的[mysqld]模块下添加字符集配置,生效后,创建数据库和表默认都是这个设置的字符集,MySQL5.5和5.1的服务端字符集参数有变化,具体为character-set-server=utf8参数适合5.5,default-character-set=utf8参数适合5.1及以前版本。
  5. 不乱码的思想就是Linux服务器和MySQL数据库,以及创建的库和表的字符集都保持一致,还有客户端CRT或者Xshell的字符集也要保持一致。

更改字符集总的思想

  1. 数据库不要更新,到处所有数据。
  2. 把导出的数据进行字符集替换。
  3. 修改my.cnf,更改MySQL客户端服务端字符集,重启生效。
  4. 到处更改过字符集的数据包括表结构语句,提供服务。

如何调整已有MySQL数据库的字符集,例如:从UTF8改成GBK

  • 查看当前系统的字符集设置
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
14
<span class="line">mysql> show variables like <span class="string">'character%'</span>;</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line">| Variable_name            | Value                                     |</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line">| character_<span class="built_in">set</span>_client     | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_connection | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_database   | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_filesystem | binary                                    |</span>
<span class="line">| character_<span class="built_in">set</span>_results    | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_server     | utf8                                      |</span>
<span class="line">| character_<span class="built_in">set</span>_system     | utf8                                      |</span>
<span class="line">| character_sets_dir       | /application/mysql-5.5.32/share/charsets/ |</span>
<span class="line">+--------------------------+-------------------------------------------+</span>
<span class="line"><span class="comment">#可以看到客户端服务端和服务器的字符集都是utf8</span></span>
  • 查看db库和表的字符集,这里就以一个数据库为例子,如果有很多数据库和表可以用脚本实现
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
14
<span class="line">mysql> mysql -uroot -p3306 -S /data/3306/mysql.sock <span class="_">-e</span> <span class="string">"show create database db;</span>
<span class="line">+----------+-------------------------------------------------------------+</span>
<span class="line">| Database | Create Database                                             |</span>
<span class="line">+----------+-------------------------------------------------------------+</span>
<span class="line">| db       | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 */ |  --表的字符集为utf8</span>
<span class="line">+----------+-------------------------------------------------------------+</span>
<span class="line">mysql> show create table db.test\G</span>
<span class="line">*************************** 1. row ***************************</span>
<span class="line">       Table: test</span>
<span class="line">Create Table: CREATE TABLE `test` (</span>
<span class="line">  `id` int(4) DEFAULT NULL,</span>
<span class="line">  `name` char(16) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8			--库的字符集为utf8</span>
<span class="line">1 row in set (0.00 sec)</span></span>
  • 查看表的内容
<span class="line">1</span>
2
3
4
5
6
7
8
<span class="line">mysql> select * from db.test;</span>
<span class="line">+------+--------+</span>
<span class="line">| id   | name   |</span>
<span class="line">+------+--------+</span>
<span class="line">|    1 | sa     |</span>
<span class="line">|    2 | 测试   |			---中文并没有乱码</span>
<span class="line">+------+--------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
  • 把数据库db以SQL语句的形式导出
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
<span class="line">[root@MySQL ~]<span class="comment"># mysqldump -uroot -p3306 -S /data/3306/mysql.sock -B db>db.sql</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># egrep -v "^-|^\/|^$" db.sql </span></span>
<span class="line">CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db` /*!40100 DEFAULT CHARACTER SET utf8 */;</span>
<span class="line">USE `db`;</span>
<span class="line">DROP TABLE IF EXISTS `<span class="built_in">test</span>`;</span>
<span class="line">CREATE TABLE `<span class="built_in">test</span>` (</span>
<span class="line">  `id` int(4) DEFAULT NULL,</span>
<span class="line">  `name` char(16) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8;</span>
<span class="line">LOCK TABLES `<span class="built_in">test</span>` WRITE;</span>
<span class="line">INSERT INTO `<span class="built_in">test</span>` VALUES (1,<span class="string">'sa'</span>),(2,<span class="string">'测试'</span>);</span>
<span class="line">UNLOCK TABLES;</span>
<span class="line"><span class="comment">#现在可以看到有很多utf8的字符,这是都是自创建表的时候指定的字符集</span></span>
  • 修改备份文件的SQL语句utf8改为gbk
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
14
<span class="line">[root@MySQL ~]<span class="comment"># cp db.sql db_bak.sql		#先做备份</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># sed -i 's#utf8#gbk#g' db.sql   #把utf8全部改成gbk</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># egrep -v "^-|^\/|^$" db.sql </span></span>
<span class="line">CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db` /*!40100 DEFAULT CHARACTER SET gbk */;</span>
<span class="line">USE `db`;</span>
<span class="line">DROP TABLE IF EXISTS `<span class="built_in">test</span>`;</span>
<span class="line">CREATE TABLE `<span class="built_in">test</span>` (</span>
<span class="line">  `id` int(4) DEFAULT NULL,</span>
<span class="line">  `name` char(16) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=gbk;</span>
<span class="line">LOCK TABLES `<span class="built_in">test</span>` WRITE;</span>
<span class="line">INSERT INTO `<span class="built_in">test</span>` VALUES (1,<span class="string">'sa'</span>),(2,<span class="string">'测试'</span>);</span>
<span class="line">UNLOCK TABLES;</span>
<span class="line"><span class="comment">#可以看到现在的SQL语句字符集是GBK</span></span>
  • 修改配置文件my.cnf指定客户端和服务端字符集为gbk
<span class="line">1</span>
2
3
4
5
6
<span class="line">[root@MySQL ~]<span class="comment"># vim /data/3306/my.cnf</span></span>
<span class="line">[client]</span>
<span class="line">character-set-server=gbk</span>
<span class="line">[mysqld]</span>
<span class="line">character-set-server=gbk</span>
<span class="line"><span class="comment">#在客户端和服务端模块中指定字符集为gbk</span></span>
  • 更改系统的字符集为GBK
<span class="line">1</span>
2
3
4
5
6
7
<span class="line">[root@MySQL ~]<span class="comment"># vim /etc/sysconfig/i18n </span></span>
<span class="line"><span class="comment">#LANG="en_US.UTF-8"</span></span>
<span class="line">LANG=<span class="string">"zh_CN.GBK"</span></span>
<span class="line"><span class="comment">#LANG="zh_CN.UTF-8"</span></span>
<span class="line">SYSFONT=<span class="string">"latarcyrheb-sun16"</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># reboot</span></span>
<span class="line"><span class="comment">#原有的字符集注销掉不删除,以免出错</span></span>
  • 修改客户端Xshell字符集为gbk

MySQL字符集介绍

  • 删除数据库中的所有数据
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
<span class="line">mysql> drop database db;</span>
<span class="line">Query OK, 1 row affected (0.07 sec)</span>

<span class="line">mysql> show databases;</span>
<span class="line">+<span class="comment">######--+</span></span>
<span class="line">| Database           |</span>
<span class="line">+<span class="comment">######--+</span></span>
<span class="line">| information_schema |</span>
<span class="line">| mysql              |</span>
<span class="line">| performance_schema |</span>
<span class="line">+<span class="comment">######--+</span></span>
<span class="line">3 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.03 sec)</span>
<span class="line"><span class="comment">#这里只是测试,所以就只删除了db库</span></span>
  • 导入mysql的所有数据
<span class="line">1</span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p3306 -S /data/3306/mysql.sock < db.sql</span></span>
  • 查看字符集及数据
<span class="line">1</span>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
<span class="line">mysql> show variables like <span class="string">'character%'</span>;</span>
<span class="line">+<span class="comment">########--+##############-+</span></span>
<span class="line">| Variable_name            | Value                                     |</span>
<span class="line">+<span class="comment">########--+##############-+</span></span>
<span class="line">| character_<span class="built_in">set</span>_client     | gbk                                       |</span>
<span class="line">| character_<span class="built_in">set</span>_connection | gbk                                       |</span>
<span class="line">| character_<span class="built_in">set</span>_database   | gbk                                       |</span>
<span class="line">| character_<span class="built_in">set</span>_filesystem | binary                                    |</span>
<span class="line">| character_<span class="built_in">set</span>_results    | gbk                                       |</span>
<span class="line">| character_<span class="built_in">set</span>_server     | gbk                                       |</span>
<span class="line">| character_<span class="built_in">set</span>_system     | gbk                                       |</span>
<span class="line">| character_sets_dir       | /application/mysql-5.5.32/share/charsets/ |</span>
<span class="line">+<span class="comment">########--+##############-+</span></span>
<span class="line">8 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
<span class="line"><span class="comment">#现在系统的字符集都变成了gbk了</span></span>

<span class="line">mysql> show create database db;</span>
<span class="line">+<span class="comment">###-+####################+</span></span>
<span class="line">| Database | Create Database                                            |</span>
<span class="line">+<span class="comment">###-+####################+</span></span>
<span class="line">| db       | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET gbk */ |</span>
<span class="line">+<span class="comment">###-+####################+</span></span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
<span class="line"><span class="comment">#db库的字符集现在也是gbk了</span></span>

<span class="line">mysql> show create table db.test\G</span>
<span class="line">*************************** 1. row ***************************</span>
<span class="line">       Table: <span class="built_in">test</span></span>
<span class="line">Create Table: CREATE TABLE `<span class="built_in">test</span>` (</span>
<span class="line">  `id` int(4) DEFAULT NULL,</span>
<span class="line">  `name` char(16) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=gbk</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
<span class="line"><span class="comment">#表的字符集也是gbk</span></span>

<span class="line">mysql> show databases;</span>
<span class="line">+<span class="comment">######--+</span></span>
<span class="line">| Database           |</span>
<span class="line">+<span class="comment">######--+</span></span>
<span class="line">| information_schema |</span>
<span class="line">| db                 |</span>
<span class="line">| mysql              |</span>
<span class="line">| performance_schema |</span>
<span class="line">+<span class="comment">######--+</span></span>
<span class="line">4 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
<span class="line"><span class="comment">#查看有数据库db</span></span>

<span class="line">mysql> use db</span>
<span class="line">Database changed</span>
<span class="line">mysql> show tables;</span>
<span class="line">+<span class="comment">####--+</span></span>
<span class="line">| Tables_<span class="keyword">in</span>_db |</span>
<span class="line">+<span class="comment">####--+</span></span>
<span class="line">| <span class="built_in">test</span>         |</span>
<span class="line">+<span class="comment">####--+</span></span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
<span class="line"><span class="comment">#test表也存在</span></span>

<span class="line">mysql> select * from db.test;</span>
<span class="line">+<span class="comment">##+##--+</span></span>
<span class="line">| id   | name   |</span>
<span class="line">+<span class="comment">##+##--+</span></span>
<span class="line">|    1 | sa     |</span>
<span class="line">|    2 | 测试 |</span>
<span class="line">+<span class="comment">##+##--+</span></span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
<span class="line"><span class="comment">#查看表内容中文也没有乱码</span></span>

总结:utf8 >> gbk

  1. 建库及建表的语句导出,sed批量修改为utf8
  2. 带出所有mysql数据
  3. 修改mysql服务端和客户端编码为utf8
  4. 删除原有的库表及数据
  5. 导入新的建库及建表的语句
  6. 导入mysql的所有数据
admin
版权声明:本站原创文章,由admin2016-07-11发表,共计15049字。
转载提示:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)