MySQL-- 数据类型

1. 常⽤数据类型分类

我们在学习JAVA语言⾯向对象软件开发的过程中，通常会先进⾏需求分析从⽽得到类和属性，类是⾯向对象中的概念，对应到数据库中的概念就是实体，类中的属性对应实体中的属性。实体通常以表的形式存在，每个实体对应⼀张表，表中的每条记录(数据⾏)就是实体的⼀个实例，每条记录⼜包含若⼲字段(或称为列)，每个字段代表实体的⼀个属性。

如果要定义实体的属性，就要为属性命名并指定合适的数据类型。与其他编程语⾔类似，SQL中规定了⽤于描述属性的数据类型。常⽤的数据类型有以下⼏类：

数据值类型
字符串类型
⼆进制类型
⽇期类型

2. 数据值类型

2.1 类型列表

类型	大小	说明
BIT[(M)]	默认bit	位置类型，M表示每个值的位数，取值范围1~64，如果省略M默认为1
`TINYINT[(M)]`（tiny int）	1byte	取值范围是-2^7 - 2 ^ 7-1,无符号取值范围2^8-1
BOOL（bool）	1byte	TINYINT(1)的同义词。值为零被认为是假，⾮零值被认为是true
SMALLINT[(M)] (small int)	2byte	取值范围 -2^15 ~ 2^15-1 ，⽆符号取值范围 2^16-1 。
MEDIUMINT[(M)] (medium int)	3byte	取值范围 -2^23 ~ 2^23-1 ，⽆符号取值范围 2^24-1
`INT[(M)]`	4byte	取值范围 -2^31 ~ 2^31-1 ，⽆符号取值范围 2^32-1
INTEGER[(M)] (integer)	4byte	INT[(M)]的同义词
`BIGINT[(M)]`	4byte	取值范围 -2^63 ~ 2^63-1 ，⽆符号取值范围 2^64-1
FLOAT[(M,D)]	4byte	单精度浮点型，M是总位数，D是⼩数点后⾯的位数，⼤约可以精确到⼩数点后7位
DOUBLE[(M,D)]	8byte	双精度浮点型，M是总位数，D是⼩数点后⾯的位数，⼤约可以精确到⼩数点后15位。
`DECIMAL[(M[,D])]`（decimal）	动态	不存在精度损失，M是总位数，D是⼩数点后的位数。DECIMAL的最⼤位数(M)为65，最⼤⼩数位数(D)为30。如果省略M，则默认为10，如果省略D，则默认为0。M中不计算⼩数点和负数的-号，如果D为0，则值没有⼩数点和⼩数部分。

3. 字符串类型/二进制类型

3.1 类型列表

类型	说明
`CHAR[(M)]`	固定⻓度字符串， M 表⽰字符的⻓度，以字符为单位，取值范围 0 ~ 255个字符，占用的字节=字符数字符集表示字符所占用的单个字符的字节，例如utf8mb4单个字符所占字节长度为1~4个字节，那么255个字符占用的总字节树就是255 4 ， M 省略则⻓度为 1，若给定了M的值为255，即使只存放一个数据，后面的254个字符用0补齐
`VARCHAR(M)`（varchar）	可变⻓度字符串， M 表⽰字符最⼤⻓度，所占的字节范围 0 ~ 65535个字节，若使用字符集utf8mb4那么能存放的字符个数为65535/4=16386，所以有效字符个数取决于实际字符数和使⽤的字符集
TINYTEXT（tiny text）	⼩⽂本类型，最⼤⻓度为 255 (2^8 - 1)个字节，有效字符个数取决于使⽤的字符集
`TEXT[(M)]`	⽂本类型，最⼤⻓度为 65535 (2^16 - 1)个字节，有效字符个数取决于使⽤的字符集
MEDIUMTEXT	中⽂本类型，最⼤⻓度为 16,777,215 (2^24 - 1)个字节，有效字符个数取决于使⽤的字符集
LONGTEXT	⼤⽂本类型，最⼤⻓度为 4,294,967,295 即 4GB (2^32 - 1)个字节，有效字符个数取决于使⽤的字符集
`BINARY[(M)]` (binary)	固定⻓度⼆进制字节，于CHAR类似，但存储的是⼆进制字节⽽不是字符串。 M 表⽰⻓度，以字节为单位，取值范围 0 ~ 255 ， M 省略则⻓度为1
`VARBINARY（M）`（varbinary）	可变⻓度⼆进制字节，于VARCHAR类似，但存储的是⼆进制字节⽽不是字符串。M 表⽰⻓度，以字节为单位
TINYBLOB	⼩⼆进制字节类型，最⼤⻓度为 255 (2^8 - 1)个字节
`BLOB[(M)]` （blob）	⼆进制字节类型，最⼤⻓度为 65535 (2^16 - 1)个字节
MEDIUMBLOB	中⼆进制字节类型，最⼤⻓度为 16,777,215 (2^24 - 1)个字节
LONGBLOB	⼤⼆进制字节类型，最⼤⻓度为 4,294,967,295 即 4GB (2^8 - 1)个字节
ENUM(‘value1’,‘value2’,…)	枚举，从值列表 ‘value1’,‘value2’ 或 ‘’(空字符串) 和 NULL 中选⼀个值，最多可以有 65,535 个不同的元素，单个元素的最⼤⻓度是 M <= 255 或 (M x w) <= 1020 ，其中 M 是元素字符⻓度， w 是字符集中字符所需的最⼤字节数， NUM的值在内部表⽰为整数
SET(‘value1’,‘value2’,…)	集合• 从值列表 ‘value1’,‘value2’ 中选零个或多个值• 最多64个元素• 单个元素的最⼤⻓度是 M <= 255 或 (M x w) <= 1020 ，其中 M 是元素字符⻓度， w 是字符集中字符所需的最⼤字节数• SET值在内部表⽰为整数

3.2 关于排序

字符串类型的列以字符为单位，并且可以单独指定字符集和排序规则，⽐如字符集是 utf8mb4 ，排序规则 utf8_0900_ai_ci
⼆进制的列以字节为单位，可以指定_bin结尾的排序规则，⽐如排序规则是 utf8mb4_bin ，这时以⽐较和排序基于数字字符代码值.

3.3 CHAR与VARCHAR的区别

CHAR 固定⻓度的字符串， M 表⽰以字符为单位的列⻓度，取值范围 0 ~ 255 ，省略则⻓度为1，在存储时总是⽤空格向右填充到指定的⻓度，获取列的值时会从尾部删除空格。允许定义CHAR(0)，此时列的值只能为NULL或空字符串，主要的⽬的是为了旧系兼容，⽐如类中必须有这个属性，但不使⽤这个属性的值，也就是说值并没有意义，但列⼜不能没有。
VARCHAR 可变⻓度字符串。 M 表⽰以字符为单位的最⼤列⻓度，取值范围 0 ~ 65,535(在所有列中共享)，有效⻓度取决于实际字符数和使⽤的字符集，并且⽤额外的⼀或两个字节记录实际使⽤的字节数，当实际字节数不超过 25 个字节⽤⼀个字节记录⻓度，超过 255 个字节时，使⽤两个字节记录⻓度，获取列的值时不会从尾部删除空格，插⼊数据时会删除超出⻓度的空格。

3.4 如何选择CHAR与VARCHAR

-== 如果数据确定⻓度都⼀样==，就使⽤定⻓ CHAR 类型，⽐如：⾝份证，md5，学号，邮编。

如果数据⻓度有变化,就使⽤变⻓ VARCHAR , ⽐如：名字，地址，但要规划好⻓度，保证最⻓的字符串能存的进去。
定⻓ CHAR 类型⽐较浪费磁盘空间，但是效率⾼。
变⻓ VARCHAR 类型⽐较节省磁盘空间，但是效率低。
定⻓ CHAR 类型会直接开辟好对应的存储空间。
变⻓ VARCHAR 类型在不超过定义⻓度范围的情况下⽤多少开辟多少存储空间。

3.5 VARCHAR与TEXT的区别

容量⼤⼩： VARCHAR 最⼤⽀持 65535 个字节； TEXT 最⼤⽀持 65535 个字节，在指定TEXT ⻓度时，当超过 65535 时⾃动转换为 MEDIUMTEXT 类型，当超过 16,777,215 时⾃动转换为 LONGTEXT 类型
存储位置： VARCHAR 类型的列实际内容⼩于768个字节时存在当前⾏，⼤于768时存在溢出⻚，当前⾏保存溢出⻚的地址； TEXT 类型的列整体保存在溢出⻚，当前⾏只保存溢出⻚地址
查询性能：对于频繁查询的 VARCHAR 列可以创建索引，提升查询性能；TEXT 类型的列⽆法直接创建普通索引，但可以使⽤ FULLTEXT 索引，由于索引的⽀持和存储位置的不同， VARCHAR 列的性能⾼于 TEXT 类型的列
适⽤场景：如果存储的数据⻓度较⼩且需要创建索引进⾏检索，可以选择 VARCHAR 类型，⽐如姓名，⽤⼾，邮箱等；如果存储的数据⻓度较⼤且不需要频繁以该列为条件进⾏检索可以选择 TEXT 类型，⽐如⽂章内容等。

4 日期类型

4.1 类型列表

类型	大小	说明	0值
TIMESTAMP[(fsp)] (timestamp)	4bytes	• 时间戳类型 • ⽀持范围 1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.499999 • 0值为 0000-00-00 00:00:00	0000-00-00 00:00:00
`DATETIME[(fsp)]`	8bytes	⽇期类型和时间类型的组合 • ⽀持范围 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999 • 显⽰格式为 YYYY-MM-DD hh:mm:ss[.fraction] • 0值为 0000-00-00 00:00:00	0000-00-00 00:00:00
`DATE`	3bytes	⽇期类型⽀持范围1000-01-01 ~ 9999-12-31显⽰格式为YYYY-MM-DD0值为0000-00-00
`TIME[(fsp)]`	3byte	时间类型⽀持范围-838:59:59.000000 ~ 838:59:59.000000显⽰格式为hh:mm:ss[.fraction]0值为00:00:00	00:00:00
YEAR[(4)]	1byte	4位格式的年份⽀持范围1901 ~ 2155显⽰格式为YYYY 0值为0	0