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)
mysql> show char set;mysql> show charset;mysql> show character set;
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)