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: 140Max_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: testCreate Table: CREATE TABLE `test` ( `id` int(4) DEFAULT NULL, `name` varchar(16) DEFAULT NULL, `phonenum` char(12) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf81 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: testCreate Table: CREATE TABLE `test` ( `id` int(4) DEFAULT NULL, `name` varchar(16) DEFAULT NULL, `phonenum` char(12) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=gbk1 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)