MySQL 数据类型

参考链接：

https://www.cnblogs.com/wlzjdm/p/6953398.html

https://www.cnblogs.com/alterem/p/12001817.html

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于 M 和 D 的值	依赖于M和D的值	小数值

DECIMAL

MySQL DECIMAL 数据类型用于在数据库中存储精确的数值。经常用于保留准确精确度的列，例如会计系统中的货币数据。

要定义数据类型为DECIMAL的列，请使用以下语法：

column_name DECIMAL(P,D);

• P是表示有效数字数的精度。 P范围为 1-65。
• D是表示小数点后的位数。 D的范围是 0~30。MySQL 要求 D <= P。

DECIMAL(P，D) 表示列可以存储D位小数的 P 位数。十进制列的实际范围取决于精度和刻度。

与 INT 数据类型一样，DECIMAL 类型也具有 UNSIGNED 和 ZEROFILL 属性。 如果使用 UNSIGNED 属性，则DECIMAL UNSIGNED 的列将不接受负值。

如果使用 ZEROFILL，MySQL 将把显示值填充 0 以显示由列定义指定的宽度。 另外，如果对 DECIMAL 列使用ZERO FILL，MySQL 将自动将 UNSIGNED属性添加到列。

以下示例使用 DECIMAL数据类型定义的一个叫作 amount的列。

amount DECIMAL(6,2);

在此示例中，amount 列最多可以存储6位数字，小数位数为2位; 因此，amount 列的范围是从-9999.99到9999.99。

MySQL 允许使用以下语法：

column_name DECIMAL(P);

这相当于：

column_name DECIMAL(P,0);

在这种情况下，列不包含小数部分或小数点。

此外，甚至可以使用以下语法。

column_name DECIMAL;

在这种情况下，P的默认值为10。

DECIMAL 存储

MySQL 分别为整数和小数部分分配存储空间。 MySQL 使用二进制格式存储 DECIMAL 值。它将 9 位数字包装成 4个字节。

对于每个部分，需要 4 个字节来存储 9 位数的每个倍数。剩余数字所需的存储如下表所示：

剩余数字	位
0	0
1–2	1
3–4	2
5–6	3
7-9	4

？？例如，DECIMAL(19,9)对于小数部分具有9位数字，对于整数部分具有10位数字，小数部分需要4个字节。 整数部分对于前9位数字需要4个字节，1个剩余字节需要1个字节。DECIMAL(19,9)列总共需要9个字节。

DEClMAL(18 ，9)小数点两边将各存储 9个 数字，一共使用 9 个字节：小数点前的数字用 4 个字节，小数点后的数字用 4 个字节， 小数点本身占 1 个字节。

DECIMAL 应用

经常使用DECIMAL数据类型的货币数据，如价格，工资，账户余额等。如果要设计一个处理货币数据的数据库，则可参考以下语法 -

amount DECIMAL(19,2);

但是，如果要遵守公认会计原则(GAAP)规则，则货币栏必须至少包含4位小数，以确保舍入值不超过$0.01。 在这种情况下，应该定义具有4位小数的列，如下所示：

amount DECIMAL(19,4);

另：

DECIMAL 存储财务数据。但在数据量比较大的时候，可以考虑使用 BIGINT 代替：将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在 BIGINT 里，这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。

日期类型

MySql 中关于日期的类型有 Date / Datetime / Timestamp 三种类型。

日期赋值时，允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。

例如，‘98-12-31 11:30:45’、‘98.12.31 11+30+45’、‘98/12/31 11*30*45’ 和 ‘98@12@31 11³⁰45’ 都是等价 的，对于不合法的日期将会转换为：0000-00-00 00:00:00。

Date

存储数据格式为：YYYY-MM-DD，范围为 ‘1000-01-01’ 到 ‘9999-12-31’，并且允许使用字符串或数字赋值。

update t set t_date = 12331212 where t_name = 'name';
update t set t_date = '1233-12-12' where t_name = 'name';

Datetime

日期和时间的组合，存储格式为：YYYY-MM-DD HH:MM:SS。

范围为 ‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’，并且允许使用字符串或数字赋值。

update t set t_date = 12331212121212 where t_name = 'name';
update t set t_date = '1233-12-12 12:12:12' where t_name = 'name';

TimeStamp

时间戳，从 1970-01-01 00:00:00 到当前的时间差值，它精确到毫秒级别，范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。设置值时只允许设置数字类型的值。

在为 TimeStamp 类型字段赋值的时候，值必须大于19700101000000，否则就会抛出错误。

TIMESTAMP 列必须有默认值，默认值可以为“0000-00-00 00:00:00”，但不能为 null。

update t set t_date = 20081212121212 where t_name = 'name';

注意：

• 当在 Java 中输出 new Date(0) 的时候，输出的是：Thu Jan 01 08:00:00 CST 1970，这是因为时区的概念，中国是东八区，对应的是早上八点。

• TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。

• TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。

  如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。

  一般建表时候，创建时间用datetime，更新时间用 TIMESTAMP。

时间类型

MySQL 中时间类型用 Time 表示。

Time

存储格式为 ‘HH:MM:SS’。范围可以从’-838:59:59’到’838:59:59’。小时部分会因此大的原因是 Time 类型不仅可以用于表示一天的时间(必须小于24小时)，还可以用于表示某个事件过去的时间或两个事件之间的时间间隔，所以可以大于24小时，甚至为负。

格式说明：

• ‘HHMMSS’格式的没有间割符的字符串，假定是有意义的时间。例如，‘101112’被理解为’10:11:12’，但’109712’是不合法的(它有一个没有意义的分钟部分)，将变为’00:00:00’。
• 超出TIME范围但合法的值被裁为范围最接近的端点。例如，’-850:00:00’和’850:00:00’被转换为’-838:59:59’和’838:59:59’。

年类型

MySQL 中用 Year 表示年类型。

Year

存储格式为 YYYY。范围是 1901 到 2155。

可以指定各种格式的 Year 值：

• 四位字符串，范围为’1901’到’2155’。
• 四位数字，范围为 1901 到 2155。
• 两位字符串，范围为’00’到’99’。'00’到’69’和’70’到’99’范围的值被转换为 2000 到 2069 和 1970 到 1999 范围的 Year 值。
• 两位整数，范围为 1 到 99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的 Year 值。注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为 2000。你必须将它指定为一个字符串’0’或’00’或者它被解释为0000。
• 非法YEAR值被转换为0000。

日期兼容性问题

1. MySQL 服务器采用了 Unix 的时间功能，对于 TIMESTAMP 值，可处理的日期至 2037 年。对于 DATE 和 DATETIME 值，可接受的日期可至 9999 年。
2. 所有的 MySQL 日期函数均是在 1 个源文件：sql/time.cc中实现的，并经过了恰当编码以确保 2000 年安全。
3. 在 MySQL 3.22 和以后的版本中，YEAR 列类型能够在1个字节内保存 0 年以及1901～2155年，并能使用两位或四位数字显示它们。所有的两位数字年份均被视为介于1970～2069年之间，这意味着，如果你在 YEAR 列中保存了01，MySQL 服务器会将其当作 2001 年。

5 种类型对比

日期时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	2038 年的某个时刻	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

注：TIMESTAMP 最小值为 1970-01-01 08:00:01 是因为时区。timestamp 类型的起始时间是1970-01-01 00:00:01 UTC，和时区是关系的。此外，Timestamp 在不同版本的 MySQL 中有细微差别。

Datetime 和 Timestamp 对比

通常首选 Timestamp。

理由1：DateTime 类型没有时区信息

DateTime 类型是没有时区信息的（时区无关） ，DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。这样就会有什么问题呢？当你的时区更换之后，比如你的服务器更换地址或者更换客户端连接时区设置的话，就会导致你从数据库中读出的时间错误。不要小看这个问题，很多系统就是因为这个问题闹出了很多笑话。

Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样。

扩展：一些关于 MySQL 时区设置的一个常用 sql 命令

# 查看当前会话时区
SELECT @@session.time_zone;
# 设置当前会话时区
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
# 数据库全局时区设置
SELECT @@global.time_zone;
# 设置全局时区
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';

理由2：DateTime 类型耗费空间更大

Timestamp 只需要使用 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

数值型时间戳

很多时候也会使用 int 或者 bigint 类型的数值也就是时间戳来表示时间。

这种存储方式的具有 Timestamp 类型的所具有一些优点，并且使用它的进行日期排序以及对比等操作的效率会更高，跨系统也很方便，毕竟只是存放的数值。

缺点也很明显，就是数据的可读性太差了，你无法直观的看到具体时间。

时间戳的定义是从一个基准时间开始算起，这个基准时间是 1970-1-1 00:00:00，从这个时间开始，用整数表示，以秒计时，随着时间的流逝这个时间整数不断增加。这样一来，只需要一个数值，就可以完美地表示时间了，而且这个数值是一个绝对数值，即无论的身处地球的任何角落，这个表示时间的时间戳，都是一样的，生成的数值都是一样的，并且没有时区的概念，所以在系统的中时间的传输中，都不需要进行额外的转换了，只有在显示给用户的时候，才转换为字符串格式的本地时间。