MySQL学习笔记之数据类型_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&&

 

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

 

2、enum

 

enum可以理解为枚举类型，它给出n个现成的字符串，插入的数据只能从这几个字符串里面选择。

 

create table country

(

    place enum('Japan','China','Russia','Brazil','America')

);

 

insert into country values ('China');

 

插入数据的语句跟正常的一样。一旦插入的字符串不在范围里面，如果处于严格的SQL模式，会直接报错，插入失败；否则会插入空字符串('')。当然也可以插入null。

 

如果enum限定为not null，默认值就是第一个字符串，否则就是null。

 

使用enum类型除了可以限定插入的具体值以外，还有很重要的一点就是节省空间。如上例插入'China'的时候，实际上并没有插入5个字节的字符，而是仅插入一个字节的数组索引，这个索引是2，指向第二个字符串，而不是额外为字符开辟存储空间。记录达到成千上万的时候，能节省多少空间可想而知。

 

另外，建议枚举值不要用'1','2','3'这样的数值型表示，MySQL对这种形式处理起来往往会出现意想不到的结果。

 

二、整数类型

 

 MySQL中的整数类型使用起来很简单，样式就是：INT [UNSIGNED] [ZEROFILL]。其中第一个参数是类型名，可以是其他的int型；第二个参数指明是否带符号整数，默认含负数范围，指定为unsigned后只能为正数或0，可以使正数范围扩大一倍；第三个参数指明数字前是否以0填充，以使该字段所有数据位数一致。

 

MySQL中整数类型众多，其中INT和SMALLINT是SQL语言的标准类型，其他是MySQL的补充类型。

三、小数类型

 

浮点数：float和double，跟编程语言里面的单双精度一样。

 

float占4字节，表示范围：负数部分-3.402823466E+38 到 -1.75494351E-38，正数部分与之对称 1.75494351E-38 到 3.402823466E+38

 

double占8字节，因为平时极少涉及很高的精度，此处略。

 

float和double都可以定制符合自己要求的位数，格式float(M,D)，其中M表示整数和小数部分所有数字的个数最大值，D表示小数点后的小数位数。实际应用中，一旦设定，小数部分必须达到D位，不足补0，超出四舍五入；整数部分最多只能为M-D位。

 

注意在用==或!=比较时会产生误差，比较结果往往不确定。