作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO
联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬
学习必须往深处挖，挖的越深，基础越扎实！
阶段1、深入多线程
阶段2、深入多线程设计模式
阶段3、深入juc源码解析
码哥源码部分
码哥讲源码-原理源码篇【2024年最新大厂关于线程池使用的场景题】
码哥讲源码-原理源码篇【揭秘join方法的唤醒本质上决定于jvm的底层析构函数】
码哥源码-原理源码篇【Doug Lea为什么要将成员变量赋值给局部变量后再操作？】
码哥讲源码【谁再说Spring不支持多线程事务，你给我抽他！】
打脸系列【020-3小时讲解MESI协议和volatile之间的关系，那些将x86下的验证结果当作最终结果的水货们请闭嘴】
概述
MySQL支持很多数据类型，以便我们能在复杂的业务场景中支持各种各样的数据格式，存储适当的数据内容。我们在设计数据库时，正确的使用数据库类型对整个数据库的整洁和高效，会有很大的帮助。
目前常用的数据类型大致上可以分为4大类：整数类型、浮点数类型、字符串(字符)类型、日期/时间类型。详细如下
| 4大类 | 主要类型 |
|---|---|
| 4大类 | 主要类型 |
| 整数类型 | tinyint、smallint、mediumint、int、bigint |
| 浮点数类型 | float、double、decimal |
| 字符串(字符)类型 | char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext |
| 日期/时间类型 | Date、DateTime、TimeStamp、Time、Year |
数值类型
MySQL支持所有标准SQL数值数据类型,包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，还有近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)，所以MySQL中数据类型是丰富且完整的。
而作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。在下面的表格中，我们总结了5个整数类型的存储大小和取值范围。
| 类型 | 字节数 | 有符号值范围 | 无符号值范围 | 作用 |
|---|---|---|---|---|
| TINYINT | 1byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2bytes | (-32768，32767) | (0，65535) | 大整数值 |
| MEDIUMINT | 3bytes | (-8388608，8388607) | (0，16777215) | 大整数值 |
| INT或INTEGER | 4bytes | (-2147483648，2147483647) | (0，4294967295) | 大整数值 |
| BIGINT | 8bytes | (-9,223,372,036,854,775,808，9223372036854775807) | (0，18446744073709551615) | 极大整数值 |
我们创建表赋予的类型默认是有符号类型的，无符号的需要在类型需要后面跟上unsigned 标记，上面的数据范围是根据字节对应二进制换算，比如一个字节转化为十进制最大为255（11111111），最小为0（00000000），具体可以去查下二进制表示相关资料。
测试一下：
1 mysql> create table tinyintdemo(
2 tiny TINYINT,
3 tinyunsi TINYINT UNSIGNED
4 );
5 Query OK, 0 rows affected
6
7 mysql> insert into tinyintdemo values(-128,-128);
8 1264 - Out of range value for column 'tinyunsi' at row 1
9 mysql> insert into tinyintdemo values(-128,0);
10 Query OK, 1 row affected
11
12 mysql> insert into tinyintdemo values(255,255);
13 1264 - Out of range value for column 'tiny' at row 1
14 mysql> insert into tinyintdemo values(127,255);
15 Query OK, 1 row affected
上面我们故意输入不正确的值，无符号下我们输入-128，默认有符号情况下我们输入255，都会提示 Out of range，超出范围。
1 mysql> select * from tinyintdemo;
2 +------+----------+
3 | tiny | tinyunsi |
4 +------+----------+
5 | -128 | 0 |
6 | 127 | 255 |
7 +------+----------+
8 2 rows in set
另外需注意的点：我们经常在创建表的时候定义字段为int(n)，比如int(11) , 或者bitint(19) , 其实这不是描述字段长度的意思，在int(n)中，int占据的字节是固定4个，所以他的范围也固定是在 (-2 147 483 648，2 147 483 647) 之间，
并不会因为n的长度是多少而改变，而n的目的是为了表实显示宽度用的，在显示宽度不足的时候补充0。
再测试一下：
1 mysql> CREATE TABLE lengthdemo (
2 `nor` int,
3 `norlen` int(8),
4 `norfill` int(8) zerofill
5 );
6 Query OK, 0 rows affected
7
8 mysql> insert into lengthdemo values(12345,12345,12345);
9 Query OK, 1 row affected
10
11 mysql> select * from lengthdemo;
12
13 +-------+--------+---------+
14 | nor | norlen | norfill |
15 +-------+--------+---------+
16 | 12345 | 12345 | 000 12345 |
17 +-------+--------+---------+
18 1 row in set
19
20 mysql> show create table lengthdemo;
21 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 | Table | Create Table |
23 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 | lengthdemo | CREATE TABLE `lengthdemo` (
25 `nor` int(11) DEFAULT NULL,
26 `norlen` int(8) DEFAULT NULL,
27 `norfill` int(8) unsigned zerofill DEFAULT NULL
28 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
29 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
30 1 row in set
从这个执行结果中，我们可以总结如下：
1、n的值不管填写多少，int类型只占据4个字节。
2、如果设置了n的显示宽度之后，再加上unsigned zerofill，显示宽度不足的用0补足，超过则直接显示完整数值。
3、加 zerofill属性的字段，会默认为 unsigned 无符号类型。
浮点类型
float数值类型表示单精度浮点数值，double数值类型表示双精度浮点数值，float和double都是浮点型，而decimal是定点型。浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。
float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。
| 类型 | 大小 | 有符号值范围 | 无符号值范围 | 作用 |
|---|---|---|---|---|
| FLOAT | 4bytes | (-3.402823466E+38，-1.175494351E-38)，0，(1.175494351E-38，3.402823466351E+38) | 0，(1.175494351E-38，3.402823466E+38) | 单精度浮点数值 |
| DOUBLE | 8bytes | (-1.7976931348623157E+308，-2.2250738585072014E-308)，0，(2.2250738585072014E-308，1.7976931348623157E+308) | 0，(2.2250738585072014E-308，1.7976931348623157E+308) | 双精度浮点数值 |
| DECIMAL | 对DECIMAL(M,D)，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
浮点型和定点型可以用类型名称后加(M,D)来表示,M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。
float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认只取整数，小数丢弃。
同样的，我们来测试下三个类型的使用：
1 mysql> insert into accuracy values (1,1,1),(2.124,2.124,2.124),(3.125,3.125,3.125),(4.115,4.115,4.115),(5.136,5.136,5.136);
2 Query OK, 5 rows affected, 4 warnings (0.07 sec)
3 Records: 5 Duplicates: 0 Warnings: 4
4
5 mysql> select * from accuracy;
6 +------+------+------+
7 | a | b | c |
8 +------+------+------+
9 | 1.00 | 1.00 | 1.00 |
10 | 2.12 | 2.12 | 2.12 |
11 | 3.12 | 3.12 | 3.13 |
12 | 4.12 | 4.12 | 4.12 |
13 | 5.14 | 5.14 | 5.14 |
14 +------+------+------+
15 5 rows in set (0.00 sec)
从这个执行结果，总结如下：
1、c是decimal类型，采用的是四舍五入
2、a和b分别为float 和double，采用的是四舍六入五成双
说明下四舍六入五成双：5以下舍弃，5以上进位，遇到5的时候，如果5后面还有不为0的任何数字，进位，如果没有，需要检查5前面的数字，奇数进位，偶数丢弃，观察结果中 3.125和4.115，可以得到规律。
我们再将浮点类型的(M,D)精度和标度都去掉，结果如下：
1 mysql> create table accuracy2(a float,b double,c decimal);
2 Query OK, 0 rows affected
3
4 mysql> insert into accuracy2 values (1,1,1),(1.23,1.23,1.23);
5 Query OK, 2 rows affected
6 Records: 2 Duplicates: 0 Warnings: 1
7
8 mysql> select * from accuracy2;
9 +------+------+---+
10 | a | b | c |
11 +------+------+---+
12 | 1 | 1 | 1 |
13 | 1.23 | 1.23 | 1 |
14 +------+------+---+
15 2 rows in set
总结如下：
如果浮点数float、double如果不写精度和标度，则会按照实际的结果显示，而decimal会将小数四舍五入，并且插入时发出警告信息。
float，double等非标准类型，在DB中保存的是近似值，而decimal则以字符串的形式保存准确的数值，比如银行、金融系统之类的对统计精度要求比较高的，建议采用decimal。
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
| 类型 | 大小 | 值范围 | 格式 | 作用 |
|---|---|---|---|---|
| DATE | 3 bytes | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 bytes | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 bytes | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 bytes | 1000-01-0100:00:00/9999-12-3123:59:59 | YYYY-MM-DDHH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 bytes | 1970-01-0100:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-1911:14:07 ，格林尼治时间2038年1月19日凌晨03:14:07 | YYYYMMDDHHMMSS | 混合日期和时间值，时间戳 |
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
| 类型 | 大小 | 作用 |
|---|---|---|
| CHAR | 0-255bytes | 定长字符串 |
| VARCHAR | 0-65535bytes | 变长字符串 |
| TINYBLOB | 0-255bytes | 不超过255个字符的二进制字符串 |
| TINYTEXT | 0-255bytes | 短文本字符串 |
| BLOB | 0-65535bytes | 二进制形式的长文本数据 |
| TEXT | 0-65535bytes | 长文本数据 |
| MEDIUMBLOB | 0-16777215bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16777215bytes | 中等长度文本数据 |
| LONGBLOB | 0-4294967295bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4294967295bytes | 极大文本数据 |
注意点：
1、CHAR(N) 和 VARCHAR(N) 中N代表字符的个数，但并不代表字节个数，比如 CHAR(10) 代表可以存储 10 个字符。
2、CHAR 和VARCHAR类型类似，但它们保存和检索的方式不同，最大长度和是否尾部空格被保留等方面也不同，同时在存储或检索过程中不进行大小写转换。
如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。
3、BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值。
4、BLOB是一个二进制大对象，可以容纳可变数量的数据，包含4 种类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，区别是可容纳存储范围不同。
5、TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际业务场景的选择。
MySQL、Java类型对照
| MySQLTypeName | ReturnvalueofGetColumnClassName | ReturnedasJavaClass |
|---|---|---|
| MySQLTypeName | ReturnvalueofGetColumnClassName | ReturnedasJavaClass |
| BIT(1)(newinMySQL-5.0) | BIT | java.lang.Boolean |
| BIT(>1)(newinMySQL-5.0) | BIT | byte[] |
| TINYINT | TINYINT | java.lang.BooleaniftheconfigurationpropertytinyInt1isBitissettotrue(thedefault)andthestoragesizeis1,orjava.lang.Integerifnot. |
| BOOL,BOOLEAN | TINYINT | SeeTINYINT,aboveasthesearealiasesforTINYINT(1),currently. |
| SMALLINT[(M)][UNSIGNED] | SMALLINT[UNSIGNED] | java.lang.Integer(regardlessifUNSIGNEDornot) |
| MEDIUMINT[(M)][UNSIGNED] | MEDIUMINT[UNSIGNED] | java.lang.Integer,ifUNSIGNEDjava.lang.Long |
| INT,INTEGER[(M)][UNSIGNED] | INTEGER[UNSIGNED] | java.lang.Integer,ifUNSIGNEDjava.lang.Long |
| BIGINT[(M)][UNSIGNED] | BIGINT[UNSIGNED] | java.lang.Long,ifUNSIGNEDjava.math.BigInteger |
| FLOAT[(M,D)] | FLOAT | java.lang.Float |
| DOUBLE[(M,B)] | DOUBLE | java.lang.Double |
| DECIMAL[(M[,D])] | DECIMAL | java.math.BigDecimala |
| DATE | DATE | java.sql.DateJa |
| DATETIME | DATETIME | java.sql.Timestamp |
| TIMESTAMP[(M)] | TIMESTAMP | java.sql.Timestamp |
| TIME | TIME | java.sql.Time |
| YEAR[(2 | 4)]公 | YEAR众 |
| CHAR(M) | CHAR | java.lang.String(unlessthecharactersetforthecolumnisBINARY,thenbyte[]isreturned. |
| VARCHAR(M)[BINARY] | VARCHAR | java.lang.String(unlessthecharactersetforthecolumnisBINARY,thenbyte[]isreturned. |
| BINARY(M) | BINARY | byte[] |
| VARBINARY(M) | VARBINARY | byte[] |
| TINYBLOB | TINYBLOB | byte[] |
| TINYTEXT | VARCHAR | java.lang.String |
| BLOB | BLOB | byte[] |
| MySQLTypeName | ReturnvalueofGetColumnClassName | ReturnedasJavaClass |
| TEXT | VARCHAR | java.lang.String |
| MEDIUMBLOB | MEDIUMBLOB | byte[] |
| MEDIUMTEXT | VARCHAR | java.lang.String |
| LONGBLOB | LONGBLOB | byte[] |
| LONGTEXT | VARCHAR | java.lang.String |
| ENUM('value1','value2',...) | CHAR | java.lang.String |
| SET('value1','value2',...) | CHAR | java.lang.String |
使用建议
不建议选择较大的数值范围，尽量选择合适的数据范围，越小的数据类型会更快，占用磁盘，内存和CPU缓存也更小。
越简单的类型执行的成本越低，比如整型比字符类型操作代价要小得多，很多固定范围的文本内容字段可以用整型表示。
尽量制定列为NOT NULL，有NULL的列值会使得索引、索引统计和值比较更加复杂。
浮点类型的建议统一选择decimal，精度会好很多。
记录时间的建议使用时间戳格式，存储方便，索引高效
656
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
