工具猿之MySQL教程-02-数据类型

MySQL数据类型

  数据类型是数据的一种属性，其可以决定数据的存储格式、有效范围和相应的限制。MySQL包括整数类型、浮点类型、定点数类型、日期和时间类型、字符串类型、二进制、ENUM类型和SET类型等数据类型。

一、整数类型

  整数类型是数据库中最基本的数据类型。

整数类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT(INTEGER)	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775807

二、浮点类型和定点类型

  MySQL中使用浮点类型和定点类型来表示小数。浮点类型包括单精度浮点数（FLOAT型）和双精度浮点数（DOUBLE型），定点数类型就是DECIMAL型。

类型	字节数	负数的取值范围	非负数的取值范围
FLOAT(M,D)	4	-3.402823466E+38~-1.17549435E-38	0和1.17549435E-38~3.402823466E+38
DOUBLE (M,D)	8	-1.7976931348623157E+308~-2.2250738585072014E-308	0和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL(M,D)或者DEC(M,D)	M+2	同DOUBLE型	同DOUBLE型

  浮点数型在数据库中存放的是近似值，而定点数类型在数据库中存放的是精确值。

三、CHAR类型和VARCHAR类型

名称	含义	字符个数
CHAR(n)	固定长度的字符串	最多255个字符
VARCHAR(n)	可变长度的字符串	最多65535个字符

  CHAR和VARCHAR区别：
  a、都可以通过指定n来限制存储的最大字符数长度，CHAR(20)和VARCHAR(20)将最多只能存储20个字符，超过的字符会被截掉。n必须小于该类型允许的最大字符数。
  b、CHAR类型指定了n之后，如果存入的字符数小于n，后面将会以空格补齐，查询的时候再将末尾的空格去掉，所以CHAR类型存储的字符串末尾不能有空格，VARCHAR不受此限制。
  c、内部存储机制不同，CHAR是固定长度，CHAR（4）不管是存入1个字符，2个字符或者是4个字符（英文的），都将占用4个字节。VARCHAR是存入的实际字符数+1个字字节（n<=255）或2个字节（n>255），所以VARCHAR(4),存入1个字符将占用2个字节，2个字符占用3个字节，4个字符占用5个字节。
  d、CHAR类型的字符串检索速度要比VARCHAR类型的快。

四、TEXT类型和BLOB类型

  BLOB和TEXT是对应的，不过存储方式不同，TEXT是以文本方式存储的，而BLOB是以二进制方式存储的。如果存储英文的话，TEXT区分大小写，而BLOB不区分大小写。TEXT可以指定字符集，BLOB不用指定字符集。

名称	字符个数
tinytext	最多255个字符
text	最多65535个字符
mediumtext	最多224 - 1 个字符
longtext	最多232 - 1 个字符

  二进制类型实在数据库中存储二进制数据的数据类型，如数码照片、视频和扫描的文档等数据。在MySQL中是用BLOB数据类型存储这些数据。BLOB有四种类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB,其中最大长度对应4中TEXT数据类型。

名称	字节长度
TINYBLOB	最多255个字节
BLOB	最多65535个字节(65KB)
MEDIUMBLOB	最多224 - 1 个字节(16MB)
LONGBLOB	最多232 - 1 个字节(4GB)

五、BINARY和VARBINARY

  BINARY和VARBINARY数据类型类似于CHAR和VARCHAR。不同之处在于BINARY与VARBINARY使用字节为存储单位，而CHAR与VARCHAR使用字符为存储单位。例如，BINARY(5)表示5字节的二进制数据，CHAR(5)表示存储5个字符的数据。
BINARY(n)：固定n个字节二进制数据。n的取值范围为1~255，默认为1.若输出的字节长度小于n，则不足部分以0填充。BINARY(n)数据存储的长度为n+4个字节。
VARBINARY(n)：可变长度，n的取值范围是1~65535，默认为1，数据存储长度为实际长度+4个字节。

六、时间和日期类型

   日期和时间类型是为了方便在数据库中存储日期和时间而设计的。

名称	含义	取值范围
YEAR	年份，如‘2019’	1901~2155
TIME	时间，如‘12:34:36’
DATE	日期，如‘2019-05-14’	‘1000-01-01’~‘9999-12-31’
DATETIME	日期时间，如‘2019-06-15 21:05:33’。日期时间用空格隔开	年份在1000-9999，不支持时区
TIMESTAMP	日期时间，如‘2019-06-17 21:06:35’	年份在1970~2037，支持时区

  TIMESTAMP类型比较特殊，如果定义一个字段的类型为TIMESTAMP，这个字段的时间会在其他字段修改的时候自动刷新。所以这个数据类型字段可以存放这条记录最后被修改的时间，而不是真正的存放时间。

七、ENUM类型和SET类型

  这两种类型是比较特殊的字符串数据列类型，它们的取值范围是一个预先定义好的的列表。被枚举的值必须用引号包围，不能为表达式或者一个变量估值。如果想用数值作为枚举值，那也必须用引号引起。ENUM（枚举）类型，最多可以定义65535种不同的字符串，从中做出选择，只能并且必须选择其中一种，占用存储空间是一个或两个字节，由枚举值的数目决定。例如，要表示性别字段，可以用ENUM数据类型，ENUM(‘男’,‘女’)只有两种选择，要么是‘男’，要么是‘女’，而且只需要一个字节。
SET（集合）类型，其值同样来自于一个逗号分隔列表，最多可以由64个成员，可以选择其中的0个到不限定的多个，占用空间是1~8字节，由集合可能的成员数目决定。例如，某个表示业余爱好字段，要求提供多项选择，这时可以用SET数据类型，如SET(‘篮球’,‘足球’,‘乒乓球’,‘看书’,‘绘画’,‘跳舞’,‘音乐’,‘电影’)，表示可以选择’篮球’,‘足球’,‘乒乓球’,‘看书’,‘绘画’,‘跳舞’,‘音乐’,'电影’中的0项或者多项。

八、如何选择数据类型

  SMALLINT：存储相对较小的整数，如年纪、数量、工龄和学分等。
  INT：存储中等整数，如距离。
  BIGINT：存储超大整数，如科学/科学数据。
  FLOAT：存储笑得数据，如成绩、温度和测量。
  DOUBLE：存储双精度的小数据，如科学数据。
  DECIMAL：以特别高的精度存储小数据，如货币数额、单价和科学数据。
  CHAR：存储通常包含预定义字符串的变量，如国家、邮编和身份证号。
  VARCHAR：存储不同长度的字符串值，如名字、商品名称和密码。
  TEXT：存储大型文本数据，如新闻事件、产品描述和备注信息。
  BLOB：存储二进制数据，如图片、声音、附件和二进制文档。
  DATE：存储日期，如生日和进货日期。
  TIME：存储时间或时间间隔，如开始/结束时间、两时间之间的间隔。
  DATETIME：存储包含日期和时间的数据，如事件提醒。
  TIMESTAMP：存储即时时间，如当前时间、事件提醒器。
  YEAR：存储年份，如毕业年、工作年和出生年。
  ENUM：存储字符属性，只能选择其中之一，如性别、布尔值。
  SET：次年初字符属性，可从中选择多个字符的联合，如多项选择、业余爱好和兴趣。

九、数据类型的附加属性

  在建表的时候，除了要根据字段存储的数据不同选择合适的数据类型外，还可以附加相关的属性。例如，在创建学生表时，学号字段要求不能为空值，是主键并且唯一的，这个时候可以对该字段添加 NOT NULL PRIMARY KEY属性。如创建商品销售表时，假如有一个字段为‘销售编号’，要求每销售一笔自动编一个递增的编号，这时可以指定该字段类型TINYINT ，属性为AUTO_INCREMENT。

MySQL关键字	含义
NULL/NOT NULL	数据列包含（不包含）NULL值
DEFAULT xxx	默认值，如果插入记录的时候没有指定值，将去这个默认值
PRIMARY KEY	指定列为主键
AUTO_INCREMENT	递增，如果插入记录的时候没有指定值，则在上一条记录的值加1.仅适用于整数类型
UNSIGNED	无符号，属性只针对于整型
CHARACTER SET name	指定一个字符集