MySQL数据类型

MySQL中的数据类型主要有 【数值类型、日期和时间类型、字符串类型】

数值类型

MySQL 支持所有标准的 SQL 数据类型，这些数据类型包括严格数据类型的严格数值类型，这些数据类型有 INTEGER、SMALLINT、DECIMAL、NUMERIC。

近似数值数据类型 并不用严格按照指定的数据类型进行存储，这些有 FLOAT、REAL、DOUBLE PRECISION。
还有经过扩展之后的数据类型，它们是 TINYINT、MEDIUMINT、BIGINT、BIT，其中 INT 是 INTEGER 的缩写，DEC 是 DECIMAL 的缩写。

数据类型汇总表

整数

在整数类型中，按照取值范围和存储方式的不同，分为

TINYINT 占用1字节
SAMLLINT 占用2字节
MEDIUMINT 占用3字节
INT、INTEGER 占用4字节
BIGINT 占用8字节

五种数据类型，如果超出类型范围的操作，会发生错误提示，所以选择合适的数据类型非常重要。

// 指定数据类型的长度，默认为11
int(7)

所有的整数都有一个可选属性 UNSIGNED (无符号) ，如果需要在字段里面保存非负数或者是需要较大上限值时，可以使用此选项，它的取值范围是正常值的下限取 0 ，上限取原值的 2 倍。如果一个列为 zerofill ，它会自动为该列添加 UNSIGNED 属性。
除此之外，整数还有一个类型就是 AUTO_INCREMENT，在需要产生唯一标识符或者顺序值时，可利用此属性，这个属性只用于整数字符。一个表中最多只有一个 AUTO_INCREMENT 属性，一般用于 自增主键 ，而且 NOT NULL ，并且是 PRIMARY KEY 和 UNIQUE 的，主键必须保证唯一性而且不为空。

小数

小数有两种数据类型：一种是 浮点数 类型，一种是 定点数 类型。

浮点数有两种
单精度浮点型 - float 型
双精度浮点型 - double 型
定点数只有一种 decimal .定点数在 MySQL 内部中以字符串的形式存在，比浮点数更为准确，适合用来表示精度特别高的数据。
浮点数和定点数都可以使用 (M，D) 的方式来表示，M 表示的就是 【整数位 + 小数位】的数字，D 表示位于 . 后面的小数。M 也被称为精度，D 被称为标度。

CREATE TABLE test2 (
aId float(6,2) default null,
bId double(6,2) default null,
cId decimal(6,2) default null
);

浮点数如果不写精度和标度，会按照实际的精度值进行显示。
定点数如果不写精度和标度，会按照 decimal(10,0)来进行操作，如果数据超过了精度和标度，MySQL会报错。

位类型

对于位类型，用于存放字段值，BIT(M)可以用来存放多位二进制数，M的范围是1-64，如果不写的话默认为1位。

// 新建test3表，表中只有一个位类型的字段
create table test3(
id bit(1)
);

// 插入数据
insert into test3 values(1);

在数据插入test3时，会首先把数据转换成二进制数，如果位数允许，则将成功插入；如果位数小于实际定义的位数，则插入失败。

// 将会报错，因为2的二进制数表示的是10，而表中定义的是bit(1),所以无法插入。
insert into test3 values(2);

日期时间类型

MySQL 中的日期与时间类型，主要包括【YEAR、TIME、DATE、DATETIME、TIMESTAMP】。

YEAR

YEAR 可以使用三种方式来表示
1、用4位的数字或者字符串表示，两者效果相同，表示范围 1901 ~ 2155 ，插入超出范围的数据会报错。
2、以2位字符串格式表示，范围是 ‘00’ ~ ‘99’。‘00’ ~ ‘69’ 表示 2000 ~ 2069，‘70’ ~ ‘99’ 表示 1970 - 1999。‘0’ 和 ‘00’ 都会被识别为 2000，超出范围的数据也会被识别为 2000。
3、以2位数字格式表示，范围为 1 ~ 99。1 ~ 69 表示 2001 ~ 2069，70 ~ 99 表示1970 ~ 1999。但0值会被识别为0000，这和2位字符串被识别为2000有所不同。

// 默认创建的year为4位
create table test4(
id year
);

// 数字格式
insert into test4 values(2020);
// 字符串格式
insert into test4 values('2020');

// 使用两位字符串表示
insert into test4 values('0'),('00'),('11'),('88'),('20'),('21');

// 使用两位数字来表示
insert into test4 values(0),(00),(11),(88),(20),(21);

TIME

create table test5(
id TIME
);

insert into test5 values('15:11:23'),('20:13'),('2 11:11'),('3 05'),('33');

DATE

create table test5(
id date
);

insert into test5 values('2020-06-13'),('20200613'),(20200613);

DATE的表示形式：

DATETIME

DATETIME类型，包含日期和时间部分，可以使用引用字符串或者数字，年份可以是4位也可以是2位。

create table test6(
id datetime
);

insert into test6 values('2020-06-13 11:11:11'),(20200613111111),('20200613111111'),(20200613080808);

TIMESTAMP

TIMESTAMP类型和DATETIME类型的格式相同，存储4个字节（比DATETIME少），取值范围比DATETIME小。
时间类型的使用场景：
1、一般表示 年月日，通常用 DATE 类型。
2、用来表示 时分秒 ，通常用 TIME 类型。
3、年月日时分秒，通常用 DATETIME 类型。
4、如果需要插入的是当前时间，通常使用 TIMESTAMP 来表示，TIMESTAMP值返回后显示为 YYYY-MM-DD HH:MM:SS 格式的字符串。
5、如果只表示年份，则应该使用 YEAR 、它比 DATE类型需要更小的空间。

字符串类型

CHAR 和 VARCHAR 类型

它们都是用来保存字符串的数据类型，主要区别在于存储方式不同。CHAR 类型的长度定义多少就显示多少。占用 M 字节 ，比如你声明一个 CHAR(20)的字符串类型，那么每个字符串占用20字节，M 的取值范围是 0 ~ 255。VARCHAR 是可变长的字符串，范围是 0 ~ 65535，在字符串检索的时候，CHAR会去掉尾部的空格，而 VARCHAR 会保留这些空格。

create table vctest1(
vc varchar(6),
ch char(6)
);

insert into vctest1 values("abc  ","abc ");

select length(vc),length(ch) from vctest1;

可以看到 vc 的字符串时 varchar ，长度是 5，ch 的字符串类型是 char ，长度是3,。可以得出结论，varchar 会保留最后的空格，char 会去掉最后的空格。

BINARY 和 VARBINARY

BINARY 和 VARBINARY 与CHAR 和 VARCHAR 非常类似，不同的是它们包含二进制字符串而不包含非二进制字符串。BINARY 与 VARBINARY 的最大长度和 CHAR 与 VARCHAR 是一样的，只不过它们是定义字节长度，而CHAR 和 VARCHAR 对应的是字符长度。

BLOB 类型

BLOB 是一个二进制大对象，可以容纳可变数量的数据，有4种BLOB类型，TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们区别在于可容纳存储范围不同。

TEXT类型

有4中TEXT类型，TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。对应的这4种BLOB类型，可存储的最大长度不同，可根据实际情况选择。

ENUM类型

ENUM 我们在Java中经常会用到，它表示的是枚举类型。它的范围需要在创建表时显示指定，对1 ~ 255 的枚举需要1个字节存储，对于 255 ~ 65535 的枚举需要2个字节存储。ENUM会忽略大小写，在存储时都会转换为大写。

SET类型

SET类型和ENUM类型有两处不同，
1、存储方式，SET对每个 0~8各成员，分别占用1个字节，最大到64，占用8个字节。
2、SET和ENUM除了存储之外，最主要的区别在于SET类型一次可以选取多个成员，而ENUM则只能选一个。