Mysql之字符集
Mysql 常见数据类型
INT类型 TINTINT 1字节（256） 2的8次 INT 4字节（4294967296）2的32次 BIGINT 8字节 DATE类型 DATE 3字节 TIMESTAMP 4字节 STRING类型 CHAR(M) M字节 1<=M<=255 （左边对齐） VARCHAR(M) L+1字节 L<=M 1<=M<=255 BLOB,TEXT L+2字节 L<2^8
Mysql数据库字符集知识
字符集就是一套文字符号及其编码、比较规则的集合
Mysql数据库字符集包括字符集（character）和校对规则（collation）两个概念。字符集定义mysql数据字符串的存储方式，校对规则定义比较字符串的方式。
mysql数据库常见字符集
一个汉字长度 说明 GBK 2 不是国际标准，对中文支持较好（需支持中文，数据量大，性能要求高） UTF-8 3 扩展B区以后的汉字占四个字节中英混合环境，用的最多，没特别需求，建议此种 latin1 1 yum安装默认字符集 utf8mb4 4 UTF-8 Unicode，移动互联网
企业场景创建什么字符集的数据库呢？
1、根据开发程序确定的字符集（建议utf8）
2、在编译时候指定字符集
3、建库的时候默认创建即可mysql> show char set;
查看字符集设置情况
1、查看当前数据库字符集设置
mysql> show variables like "character_set%"; +--------------------------+----------------------------------------+ | 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/mysql5.5.49/share/charsets/ | +--------------------------+----------------------------------------+
2、查看当前数据库内表的字符集设置
mysql> show table status\G *************************** 1. row *************************** Name: test Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 5 Avg_row_length: 28 Data_length: 140 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2017-03-16 19:14:49 Update_time: 2017-03-16 22:05:12 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:
3、查看表的数据列字段字符集
mysql> show full columns from test; +----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | int(4) | NULL | YES | | NULL | | select,insert,update,references | | | name | varchar(16) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | phonenum | char(12) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | +----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
4、查看当前安装的Mysql所支持的字符集
+----------+-----------------------------+---------------------+--------+ | 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)
5、查看建库建表字符集
mysql> show create database gtms; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | gtms | CREATE DATABASE `gtms` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+
mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(4) DEFAULT NULL, `name` varchar(16) DEFAULT NULL, `phonenum` char(12) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
客户端连接字符集控制
1、set names gbk 临时生效
mysql> set names gbk; Query OK, 0 rows affected (0.00 sec) mysql> show variables like "%char%"; +--------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql5.5.49/share/charsets/ | +--------------------------+----------------------------------------+
2、mysql登陆时候--default-character-set=gbk 临时生效
3、my.cnf中指定客户端字符集
[client]
default-character-set=utf8
或（在每个普通用户连接上来的时候都会触发执行，这个命令对具有super权限的用户是不生效）
[mysqld] init_connect = 'SET NAMES utf8'
数据库服务端字符集控制
1、编译时指定
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all #编译时如未指定或者二进制包，默认使用拉丁字符集
2、my.cnf中指定服务端字符集
[mysqld] default-character-set = utf8 mysql5.1版本 character-set-server = utf8 >mysql5.5版本
更改字符集总的思想
1、不直接更改数据库字符集，导出所有数据
2、把导出的数据进行字符集替换
3、修改my.cnf，更改Mysql客户端及服务端字符集，重启生效
4、导入更改过字符集的数据包括表结构语句，提供服务
5、开发把程序更改为对应字符集
数据库中文不乱码注意点（utf8推荐）
1、linux客户端字符集
2、linux服务器端字符集 LANG="zn_CH.UTF-8"
3、数据库客户端字符集 set names字符集 临时生效
4、数据库服务端字符集
5、具体数据库字符集
6、表的字符集
7、php/java字符集
utf8数据库环境改变客户端的字符集测试
mysql> set names gbk; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(5,"托马斯",13701800009); Query OK, 1 row affected, 2 warnings (0.07 sec) mysql> select * from test; +------+----------+-------------+ | id | name | phonenum | +------+----------+-------------+ | 1 | ?? | 13701800001 | | 2 | ?Υ | 13701800002 | | 3 | ?y | 13701800003 | | 1 | .? | 13701800001 | | 5 | ?°? | 13701800008 | | 5 | 托马斠 | 13701800009 | +------+----------+-------------+ mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> select * from test; +------+--------------+-------------+ | id | name | phonenum | +------+--------------+-------------+ | 1 | 张三 | 13701800001 | | 2 | 王五 | 13701800002 | | 3 | 赵六 | 13701800003 | | 1 | 李四 | 13701800001 | | 5 | 王八 | 13701800008 | | 5 | 鎵橀┈鏂 | 13701800009 | +------+--------------+-------------+
#此种几乎无法解决
环境本身不对，插入错误数据，很难解决
环境对的时候插入了正确数据，客户端环境破坏，查看就乱码了。切回正确环境查看就没问题了。
更改数据库及表字符集方法（默认表的字符集随库）
alter table test character set = gbk collate gbk_chinese_ci; alter database gtms character set latin1 collate = latin1_swedish_ci; 不能通过上述方法直接修改字符集,对新数据生效，对老数据不生效
已有记录的字符集调整要点
1、mysqldump 数据备份（数据量大的话，先导出表结构，sed替换。再倒数据，还原表结构，还原数据）
2、在备份的数据中更改字符集 sed
3、修改系统配置调整字符集生效
4、mysql数据还原
5、开发调好程序的字符集
模拟将utf8字符集的数据库修改成GBK字符集的过程：
(1) 导出表结构
[root@node80 ~]#mysqldump -uroot -prootabcd --default-character-set=gbk -d gtms>/tmp/createdb.sql
--default-character-set=gbk表示设置以什么字符集连接 -d表示只导出表结构，不导出数据
(2) 修改createdb.sql中表结构定义中的字符集为新的字符集
[root@node80 ~]sed -i 's/utf8/gbk/' /tmp/createdb.sql
(3) 确保记录不再更新，导出所有记录
[root@node80 ~]#mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 gtms > /tmp/createdata.sql --quick:该选项用于转储大的表。它强制Mysqldump从服务器一次一行的检索表中的行，而不是检索所有的行，并输出前将它缓存在内存中 --extended-insert:使用包括几个values列表的多行Insert语法，这样使转储文件更小，重载文件更快 --no-create-info:不需重新创建每个转储表的create table语句 --default-character-set=utf8:按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码
(4) 修改createdb.sql中表结构定义中的字符集为新的字符集
[root@node80 ~]#sed -i 's/utf8/gbk/' /tmp/createdata.sql
(5) 修改my.cnf配置调整字符集，重启生效
[client] default-character-set=gbk [mysqld] character-set-server = gbk
(6) 通过utf8建库（或者创建新库后删除原库）
mysql> create database gtmsgbk default charset gbk collation gbk_chinese_ci;
(7) 创建表，执行createdb.sql
[root@node80 ~]# mysql -uroot -prootabcd gtmsgbk< /tmp/createdb.sql
(8) 导入数据，执行createdata.sql
[root@node80 ~]# mysql -uroot -prootabcd gtmsgbk< /tmp/createdata.sql
注意，选择目标字符集时，要注意最好大于等于原库字符集
验证
mysql> show create database gtmsgbk; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | gtmsgbk | CREATE DATABASE `gtmsgbk` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(4) DEFAULT NULL, `name` varchar(16) DEFAULT NULL, `phonenum` char(12) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=gbk 1 row in set (0.00 sec) mysql> select * from test; +------+--------------+-------------+ | id | name | phonenum | +------+--------------+-------------+ | 1 | 张三 | 13701800001 | | 2 | 王五 | 13701800002 | | 3 | 赵六 | 13701800003 | | 1 | 李四 | 13701800001 | | 5 | 王八 | 13701800008 | | 5 | 鎵橀┈鏂 | 13701800009 | +------+--------------+-------------+ 6 rows in set (0.00 sec)
