MySQL里整数类型那些事儿，实操中遇到的坑和技巧分享

说到MySQL里的整数类型,看起来很简单，不就是TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT这几种嘛，选一个能存下你数字的就行了，但真在项目里用起来，尤其是项目跑了一段时间后，各种意想不到的坑就冒出来了，我结合自己以前踩过的坑和一些经验，跟你唠唠。

第一件事：选类型不是只看“够不够大”，还得看“浪不浪费”

这几种类型占用的存储空间是不一样的,像TINYINT是1个字节，SMALLINT是2个字节，INT是4个字节，BIGINT是8个字节，字节数越多，能存的数字范围就越大，这个基础知识大家都知道。

但新手最容易犯的错就是“过度设计”，不管三七二十一，主键或者数量字段直接上BIGINT，心里想的是“反正现在硬盘便宜，免得以后不够用”，这话听起来有道理，但其实忽略了两个问题。

一是空间浪费是累积的,一个BIGINT字段比INT字段多占4个字节，一张表如果有几千万行数据，这个总容量差距就很大了，更重要的是索引，主键字段是会进入每个二级索引的叶子节点的，你用一个BIGINT当主键，意味着你所有的二级索引都会因此额外增加4个字节的存储，数据量大了，这带来的存储成本和性能开销（因为索引变大，内存能缓存的索引页就变少了）是不可忽视的，除非你确信这个表的数据量真的会超过20亿（INT UNSIGNED的上限），否则用INT作为主键是更常见和稳妥的选择，这个经验是以前看公司一位资深DBA的分享时学到的，他说“用最小的、合适的类型”是数据库设计的基本素养。

第二件事：无符号（UNSIGNED）的坑，主要在减法运算

很多时候,我们的ID、数量这些字段不可能是负数，所以很自然地会加上UNSIGNED关键字，比如INT UNSIGNED，这没问题，还能让正数的存储范围扩大一倍。

但这里有个大坑,就是做减法运算的时候，比如你有一个商品库存字段stock INT UNSIGNED，现在某商品库存是10，你要卖出11件，你可能会写一条更新语句：UPDATE goods SET stock = stock - 11 WHERE id = 123。

你猜会发生什么？因为stock是UNSIGNED的，它不允许出现负数，MySQL在这种情况下不会报错（取决于sql_mode设置，但老版本或默认设置下），而是会把它变成一个超级大的正数！也就是“下溢”了，变成了4294967295（如果是INT UNSIGNED的话），这会导致你的库存显示为有大量库存，业务逻辑就完全乱套了，这个坑我实实在在地踩过，当时排查了半天才发现是这个问题。

解决方案有两个：一是在应用层做校验，确保减后的值不会小于零；二是如果有可能出现负数，就不要用UNSIGNED，用有符号类型，然后在程序里判断如果值小于0就按0处理。

第三件事：AUTO_INCREMENT的“空洞”问题

用自增主键太常见了,但它有个特性：自增值一旦分配了，即使你回滚了事务或者删除了那条记录，这个值也不会被回收重用，这就会造成“空洞”。

你连续插入了3条记录,ID是1,2,3，然后你删除了ID=2的记录，下次插入的新记录，ID会是4，而不是2，2这个号就永远空着了，如果发生大量插入后又回滚的情况，这个空洞可能会非常大。

这个“坑”其实不算是bug，是MySQL为了性能故意这么设计的，避免重复分配ID带来的锁竞争，但你需要知道这个特性，别指望ID是严格连续无间断的，有些对数字连续性有强迫症的业务（比如订单号，虽然一般不用自增主键做订单号），就要避开这个方案。

第四件事：显示宽度（比如INT(11)）的误解

我们经常看到这种写法：id INT(11)，这个11是啥意思？很多人以为是能存储的数字长度，比如最多11位数，错了！

对于整数类型,括号里的数字只是“显示宽度”，它只有在字段设置了ZEROFILL（零填充）属性时才有意义，比如你定义了num INT(5) ZEROFILL，当你存储数字123时，查询结果显示出来的会是00123，它会用0在左边填充到5位宽度，如果你没设ZEROFILL，这个(11)和(5)是没有任何区别的，INT永远都是4个字节，范围固定。

在设计表的时候,别再纠结于这个数字设多少了，它不影响存储，只影响一种很特殊的显示效果，而这种效果在现代应用中几乎用不到。

第五个技巧：用BIT类型存储布尔值或状态集合

虽然这不是整数类型,但和整数操作很像，如果你有大量的true/false开关字段，比如用户的是否激活、邮件的是否已读等，用TINYINT(1)来模拟BOOL类型虽然可以，但每个字段要占用1个字节。

MySQL提供了BIT类型,比如BIT(1)就能存储一个布尔值，它只占1个比特位，你甚至可以用BIT(8)来存储最多8个不同的开关状态，而它仍然只占1个字节，查询的时候可以用位运算来操作，非常高效，这牺牲了一点可读性，但对于需要极致优化存储空间的场景，是个不错的选择，这个技巧是在做物联网项目时学到的，设备的大量状态标志位用BIT类型节省了非常可观的存储空间。

整数类型看似简单,但里面都是细节，核心就是：选择合适的尺寸，警惕UNSIGNED的运算陷阱，理解自增ID的特性，别被显示宽度迷惑，并在特殊场景下考虑BIT类型的优势，把这些搞明白了，能避免很多后期头疼的问题。