Mysql必知必会-数据类型

一、整数

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT，它们的区别如下表所示：

二、浮点数类型

2.1、浮点数类型介绍

MySQL 支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

FLOAT 表示单精度浮点数；

DOUBLE 表示双精度浮点数；

REAL 默认就是 DOUBLE。如果你把 SQL 模式设定为启用“REAL_AS_FLOAT”，那么，MySQL 就认为 REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，就可以通过以下 SQL 语句实现：

FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？其实就是，FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。

 浮点数类型有个缺陷，就是不精准。因此，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。

2.2、浮点数类型不精确举例

新建表结构：

CREATE TABLE demo.goodsmaster
(
  barcode TEXT,
  goodsname TEXT,
  price DOUBLE,
  itemnumber INT PRIMARY KEY AUTO_INCREMENT
);

插入数据

-- 第一条
INSERT INTO demo.goodsmaster
(
  barcode,
  goodsname,
  price
)
VALUES 
(
  '0001',
  '书',
  0.47
);
-- 第二条
INSERT INTO demo.goodsmaster
(
  barcode,
  goodsname,
  price
)
VALUES 
(
  '0002',
  '笔',
  0.44
);
-- 第三条
INSERT INTO demo.goodsmaster
(
  barcode,
  goodsname,
  price
)
VALUES 
(
  '0002',
  '胶水',
  0.19
);

查询存入的数据

SELECT
	SUM( price ) 
FROM
	goodsmaster;

 我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1。

为什么会存在这样的误差呢？问题还是出在 MySQL 对浮点类型数据的存储方式上。 MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。无论哪个，都是采用二进制的方式来进行存储的。比如 9.625，用二进制来表达，就是 1001.101，或者表达成 1.001101×2^3。如果尾数不是 0 或 5（比如 9.624），你就无法用一个二进制数来精确表达。怎么办呢？就只好在取值允许的范围内进行近似（四舍五入）。数据类型是 DOUBLE 的时候，我们得到的结果误差更小一些，而数据类型是 FLOAT 的时候，误差会更大一下。原因就是，DOUBLE 有 8 位字节，精度更高。

三、定点数类型

DECIMAL 的存储方式决定了它一定是精准的。

浮点数类型是把十进制数转换成二进制数存储，DECIMAL 则不同，它是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数，进行存储。这样，所有的数值，就都可以精准表达了，不会存在因为无法表达而损失精度的问题。

MySQL 用 DECIMAL（M,D）的方式表示高精度小数。其中，M 表示整数部分加小数部分，一共有多少位，M<=65。D 表示小数部分位数，D<M.

简单小结下浮点数和定点数的特点：浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）；定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）

四、文本类型

MySQL 还支持 CHAR、VARCHAR、ENUM 和 SET 等文本类型。

CHAR(M)：固定长度字符串。CHAR(M) 类型必须预先定义字符串长度。如果太短，数据可能会超出范围；如果太长，又浪费存储空间。

VARCHAR(M)： 可变长度字符串。

VARCHAR(M) 也需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的。

TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度。

ENUM： 枚举类型，取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值。

SET：是一个字符串对象，取值必须是在预先设定的字符串值范围之内的 0 个或多个，也必须知道字符串所有可能的取值。

对于 ENUM 类型和 SET 类型来说，你必须知道所有可能的取值，所以只能用在某些特定场合，比如某个参数设定的取值范围只有几个固定值的场景。

TEXT 类型也有 4 种，它们的区别就是最大长度不同。

TINYTEXT：255 字符（这里假设字符是 ASCII 码，一个字符占用一个字节，下同）。

TEXT： 65535 字符。

MEDIUMTEXT：16777215 字符。

LONGTEXT： 4294967295 字符（相当于 4GB）。

TEXT 也有一个问题：由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用 CHAR(M)，或者 VARCHAR(M)。

五、日期与时间类型

用得最多的日期时间类型，就是 DATETIME。虽然 MySQL 也支持 YEAR（年）、TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但是我建议你，在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，就会很不容易记，而且查询的时候，SQL 语句也会更加复杂。

为了确保数据的完整性和系统的稳定性，优先考虑使用 DATETIME 类型。 

六、总结

在定义数据类型时，如果确定是整数，就用 INT；如果是小数，一定用定点数类型 DECIMAL；如果是字符串，只要不是主键，就用 TEXT；如果是日期与时间，就用 DATETIME。 整数：INT。 小数：DECIMAL。 字符串：TEXT。 日期与时间：DATETIME。