学习每一门语言前，我们都会接触每个语言中的数据类型，在SQL语言中也存在许许多多的数据类型，我们今天来一探究竟。
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)] | 8byte | 取值范围 -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，则值没有小数点和小数部分。 |
在真实开发的过程中，描述金额的数据类型一般有两种方式：
1.用不损失数据精度的decimal类型，但是有小数位，计算的时候也比较麻烦，常见于金融系统，精度严格不能损失 例如：银行
2.对于一般的商城和消费网站，对金额精度要求的不是很严格，就可以选择一种便于运算的数据类型，比如bigint，可以把钱单位定义为分 例如1元=100分
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个字节 ，若使用字符集latin1，能存放的字符个数为65535，若使用字符集utf8mb4那么能存放的字符个数为65535/4=16386，所以有效字符个数取决于实际字符数和使用的字符集 |
| TINYTEXT（tinytext） | 小文本类型，最大长度为 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排序规则是utf8mb4_0900_ai_ci⼆进制的列以字节为单位，可以指定_bin结尾的排序规则，比如排序规则是utf8mb4_bin，这时以比较和排序基于数字字符代码值
3.3 CHAR与VARCHAR的区别
- CHAR固定长度的字符串，M表示以字符为单位的列长度，取值范围0 ~ 255 ，省略则长度为1，在存储时总是用空格向右填充到指定的长度，获取列的值时会从尾部删除空格。允许定义CHAR(0)，此时列的值只能为NULL或空字符串，主要的目的是为了旧系兼容，比如类中必须有这个属性，但不使用这个属性的值，也就是说值并没有意义，但列又不能没有。
- VARCHAR 可变长度字符串。M表示以字符为单位的最大列长度，取值范围0 ~ 65535 (在所有列中共享)，有效长度取决于实际字符数和使用的字符集，并且用额外的⼀或两个字节记录实际使用的字节数，当实际字节数不超过255个字节用⼀个字节记录长度，超过255个字节时，使用两个字节记录长度，获取列的值时不会从尾部删除空格，插入数据时会删除超出长度的空格。
3.4 如何选择CHAR与VARCHAR
- 如果数据确定长度都⼀样，就使⽤定长CHAR 类型，比如：⾝份证、学号、邮政编码
- 如果数据长度有变化,就使用变长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:00.000000 ~ 2038-01-19 03:14:07.499999 | 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] | 0000-00-00 00:00:00 |
| DATE | 3bytes | ⽇期类型⽀持范围1000-01-01 ~ 9999-12-31显⽰格式为YYYY-MM-DD | 0000-00-00 |
| TIME[(fsp)] | 3byte | 时间类型⽀持范围-838:59:59.000000 ~ 838:59:59.000000显⽰格式为hh:mm:ss[.fraction] | 00:00:00 |
| YEAR[(4)] | 1byte | 4位格式的年份⽀持范围1901 ~ 2155显⽰格式为YYYY | 0 |
4.2 解释
- fsp为可选设置，⽤来指定⼩数秒精度，范围从0到6，值为0表⽰没有⼩数部分，如果省略，默认精度为0
CURRENT_DATE和CURRENT_DATE()是CURDATE()的同义词⽤于获取当前⽇期CURRENT_TIME和CURRENT_TIME([fsp])是CURTIME()的同义词⽤于获取当前时间CURRENT_TIMESTAMP和CURRENT_TIMESTAMP([fsp])是NOW()的同义词⽤于获取当前⽇期和时间
