0%

MySQL 必知必会 笔记

MySQL 必知必会
作者: 朱晓峰

02 | 字段:这么多字段类型,该怎么定义?

浮点数类型和定点数类型

MySQL 用 4 个字节存储 FLOAT 类型数据,用 8 个字节来存储 DOUBLE 类型数据。无论哪个,都是采用二进制的方式来进行存储的。比如 9.625,用二进制来表达,就是1001.101,或者表达成 1.001101×2^3。看到了吗?如果尾数不是 0 或 5(比如9.624),你就无法用一个二进制数来精确表达。怎么办呢?就只好在取值允许的范围内进行近似(四舍五入)。

现在你一定明白了,为什么数据类型是 DOUBLE 的时候,我们得到的结果误差更小一些,而数据类型是 FLOAT 的时候,误差会更大一下。原因就是,DOUBLE 有 8 位字节,精度更高。

说到这里,我想你已经彻底理解了浮点数据类型不精准的原因了。
那么,MySQL 有没有精准的数据类型呢?当然有,这就是定点数类型:DECIMAL。

就像浮点数类型的存储方式,决定了它不可能精准一样,DECIMAL 的存储方式决定了它一定是精准的。

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

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

由于 DECIMAL 数据类型的精准性,在我们的项目中,除了极少数(比如商品编号)用到整数类型外,其他的数值都用的是 DECIMAL,原因就是这个项目所处的零售行业,要求精准,一分钱也不能差。

文本类型

因为不需要预先知道字符串的长度,系统会按照实际的数据长度进行存储,所以 TEXT 类型最为灵活方便;由于实际存储的长度不确定,MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况,你只能采用 CHAR(M),或者VARCHAR(M)。

我建议你,在你的项目中,只要不是主键字段,就可以按照数据可能的最大长度,选择这几种 TEXT 类型中的的一种,作为存储字符串的数据类型。

  • TINYTEXT:255 字符(这里假设字符是 ASCII 码,一个字符占用一个字节,下同)。
  • TEXT: 65535 字符。
  • MEDIUMTEXT:16777215 字符。
  • LONGTEXT: 4294967295 字符(相当于 4GB)。

日期与时间类型

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

04丨增删改查:如何操作表中的数据?

INSERT…ON DUPLICATE 语句存在死锁的可能

https://medium.com/@mingwho/mysql-deadlock-caused-by-insert-on-duplicate-key-update-893ef30993a8

05丨主键:如何正确设置主键?

用业务字段做主键,看起来很简单,但是我们应该尽量避免这样做。因为我们无法预测未来会不会因为业务需要,而出现业务字段重复或者重用的情况。

自增字段做主键,对于单机系统来说是没问题的。但是,如果有多台服务器,各自都可以录入数据,那就不一定适用了。因为如果每台机器各自产生的数据需要合并,就可能会出现主键重复的问题。

我们可以采用手动赋值的办法,通过一定的逻辑,确保字段值在全系统的唯一性,这样就可以规避主键重复的问题了。

07丨条件语句:WHERE 与 HAVING有什么不同?

首先,你要知道它们的 2 个典型区别。

第一个区别是,如果需要通过连接从关联表中获取需要的数据,WHERE 是先筛选后连接,而 HAVING 是先连接后筛选。

这一点,就决定了在关联查询中,WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选,用一个筛选后的较小数据集和关联表进行连接,这样占用的资源比较少,执行效率也就比较高。HAVING 则需要先把结果集准备好,也就是用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,这样占用的资源就比较多,执行效率也较低。

第二个区别是,WHERE 可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;HAVING 必须要与 GROUP BY 配合使用,可以把分组计算的函数和分组字段作为筛选条件。

这决定了,在需要对数据进行分组统计的时候,HAVING 可以完成 WHERE 不能完成的任务。这是因为,在查询语法结构中,WHERE 在 GROUP BY 之前,所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后,可以使用分组字段和分组中的计算函数,对分组的结果集进行筛选,这个功能是 WHERE 无法完成的。

欢迎关注我的其它发布渠道