数据库表字段类型选择

前言

​ 数据库中有很多字段结构，例如整型、浮点型、文本、日期与时间等常用类型，每个结构中又分了很多种类型，如果我们类型定义的合理，就能节省数据库的存储空间，提升数据查询和处理的速度。如何合理去定义类型？带着这个疑问，我们今天来探讨一下数据库类型的合理定义。

整型

类型	有符号数取值范围	无符号数取值范围	占用字节数	适用场景
TINYINT	-128~127	0~255	1	一般用于枚举数据，比如系统设定等取值范围很小且固定的场景
SMALLINT	-32768~32767	0~65535	2	可以用于较小范围的统计数据，例如统计工厂的固定资产库存数量等
MEDIUMINT	-8388608~8388607	0~16777215	3	用于较大整数的计算，比如车站每日的客流量
INT	-2147483648~2147483637	0~4294967295	4	使用最广，取值范围足够大
BIGINT	写不下	写不下	5	只有当你处理特别巨大的整数才会用到。比如双十一的交易量，大型门户的点击量

浮点数与定点数

类型	描述	占用字节	适用场景
FLOAT	单精度浮点数	4	浮点数有一个缺陷是不精确，所以对于精度要求高的场景并不适合使用浮点数。适用在如计算化学、分子建模、流体动力学
DOUBLE	双精度浮点数	8	浮点数有一个缺陷是不精确，所以对于精度要求高的场景并不适合使用浮点数。适用在如计算化学、分子建模、流体动力学
DECIMAL	16进制定点数		定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景比如涉及金额计算的场景

文本

CHAR与VARCHAR

CHAR：用于存储固定长度的数据，CHAR字段上索引效率极高，但是不适用于字符长度不确定的数据。例如CHAR（10），不论数据是否达到了10个字节，都会占用10个字节的空间

VARCHAR：为了解决CHAR类型存在的问题而专门设计出来的字段，但相应的会损失存储的效率。果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合适的。

​ 所以，如果需要追求空间，则选择VARCHAR，如果是要追求效率，则选择CHAR。

TEXT与BLOB

TEXT：存储可变长度的非Unicode字符数据数据。最大长度为 2^16-1个字符

MEDIUMTEXT ：存储可变长度的非Unicode字符数据数据，最大长度为 16777215 (2^24-1) 个字符。

LONGTEXT ：存储可变长度的非Unicode字符数据数据，最大长度为 2147483647 (2^32-1) 个字符。

TINYTEXT ：存储可变长度的非Unicode字符数据数据，最大长度为 255 (2^8-1) 个字符。

与TEXT类似的数据类型是BLOB

BLOB：保存二进制数据，最大65k

TINYBLOB:保存二进制数据，最大255k

MEDIUMBLOB：保存二进制数据，最大16M

LONGBLOB：保存二进制数据，最大4G

​ 使用BLOB的优势在于文本和图片都可以以二进制的形式存储在数据库中。但是，不幸的是，现在大部分得图片都是以标签引入到前端的，而且图床和CDN的出现直接导致我们自己的数据库中只会存储文本数据，也就是说比较常用的是 TEXT。

​ 需要注意的是，TEXT由于实际存储长度不固定，MySQL 不允许 TEXT 类型的字段做主键。

NCHAR、NVARCHAR、NTEXT

在前面几种类型前加N。它表示存储的是Unicode数据类型的字符。

日期与时间

类型	日期格式	范围	占用字节数
YEAR	YYYY	1901~2155	1
TIME	HH:MM:SS	-838:59:59~838:59:59	3
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC~2038-01-1903:14:07 UTC	4

​ 在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，可以确保数据的完整性和系统的稳定性，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，就会很不容易记，而且查询的时候，SQL 语句也会更加复杂。