MySQL学习笔记之数据类型

　　数据类型在所有的数据库使用当中，都是避免不了的部分。以前每次写SQL语句，对于定义成哪种数据类型总是迷迷糊糊，今天就来彻底弄清。以下介绍仅针对MySQL 5.5以上版本。

一、字符串类型

1、char和varchar

　　以前在建表语句中，定义到字符串类型只会用varchar，但是它后面要指定一个数字，这个数字具体代表什么一知半解。char和varchar两个类型后面都要跟上一个数字，例如char(10),varchar(20)。

　　看过官方文档后发现，这里的数字表示的是最大字符个数，注意是字符数，而不是字节数！在严格的SQL模式下，插入的字符串数据如果字符数超过这个值，分两种情况：

　　①如果尾部超出部分全都是空格，则截去超出部分后插入，产生一个警告；

　　②如果尾部超出部分含非空格字符，插入失败，直接报错。

　　在非严格SQL模式下，两种情况都会直接截去超出部分后插入。

　　括号里面的数字究竟能设为多大呢？官方说明是，char的可以设为0~255，varchar的可以设为0~65535。对于char没有任何问题，但对于varchar，经过实际操作发现没那么简单。

　　MySQL不管对于什么样的存储引擎，有一个统一的设定，那就是一行数据的总字节数，不能超过65535。而字符有英文字符，有中文字符，还有其他语言的字符，而且各种编码下，有的字符一个占1字节，有的字符一个占2个或3个字节。而MySQL的默认编码类型是utf-8，一个中文字符占到3个字节，如果某个字段指定为varchar(65535)，那么单这一个字段，最大字节数就可能达到65535*3，远远超出了一行数据的最大设定65535字节。比如执行如下语句：

create table t_planet(  name varchar(65535));

会提示如下错误： create table t_planet ( name varchar(65535) ) Error Code: 1074. Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead

注意这里的错误提示，它提示name字段的最大字符数只能为21845,也就是65535除以3。因为在默认的utf-8编码下，最坏情况下会出现全为中文字符的情况，根据一个中文字符占3字节，所以最大字符数就是65535/3。

如果把字符集改为所有字符只占1字节的latin1，字符数最大值就可以达到65535了。

create table t_planet(  name varchar(65535)) character set latin1;　　//整张表格的字符集指定为latin1

 　　以上只是对最大字符数做的设定。具体到一个字符串究竟占用了多少字节，char和varchar的计算方法不一样。首先当然要根据实际的字符集，算出各个字符各自占用的字节数，然后求和。这里为了方便起见，假定每个字符都占一个字节。这样，对于char(M)，实际占用的字节数就永远都是M，因为如果字符数不满M，后面用空格补充，空格也占用字节数。当然这是存储时的情况，select显示时是把尾部空格全部去掉的。

　　而对于varchar(M)，实际占用的字节数绝大多数小于M，输入几个字符就是几个字符，不会自动填充。当然，因为字符数不确定，就要额外增加1到2个字节记录字符个数，<=255个字符用1字节记录，>255&&<=65535个字符就用2字节记录。这样，一个varchar(M)的字符串，实际占用空间大小就是“实际字符数+1或+2”。

　　由上面分析可以得知,varchar表面上完全可以取代char，实际未必，因为varchar有额外的空间占用。更重要的是，在以字符串创建索引、进行排序等操作时，由于char的字符数固定，拥有更高的效率。比方说有一个学号字段，有9位数也有10位数，表面上应该用可变字符串varchar，实际上用固定字符串char(10)是更好的选择。

　　未完待续。。。

原标题：MySQL学习笔记之数据类型

关键词：MYSQL