MySQL数字类型自增的坑

在进行表结构设计时,数字类型是最为常见的类型之一,但要用好数字类型并不如想象得那么简单,比如:

  • 怎么设计一个互联网海量并发业务的自增主键?用 INT 就够了?
  • 怎么设计账户的余额?用 DECIMAL 类型就万无一失了吗?

以上全错!

数字类型看似简单,但在表结构架构设计中很容易出现上述“设计上思考不全面”的问题(特别是在海量并发的互联网场景下)

数字类型

整数类型

MySQL 数据库支持 SQL 标准支持的整型类型:INT、SMALLINT。此外,MySQL 数据库也支持诸如 TINYINT、MEDIUMINT 和 BIGINT 整型类型(表 1 显示了各种整型所占用的存储空间及取值范围):

MySQL数据类型 含义(有符号)
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)

在整型类型中,有 signed 和 unsigned 属性,其表示的是整型的取值范围,默认为 signed。在设计时,我不建议你刻意去用 unsigned 属性,因为在做一些数据分析时,SQL 可能返回的结果并不是想要得到的结果。

来看一个“销售表 sale”的例子,其表结构和数据如下。这里要特别注意,列 sale_count 用到的是 unsigned 属性(即设计时希望列存储的数值大于等于 0):

mysql> SHOW CREATE TABLE sale\G

*************************** 1. row ***************************

       Table: sale

Create Table: CREATE TABLE `sale` (

  `sale_date` date NOT NULL,

  `sale_count` int unsigned DEFAULT NULL,

  PRIMARY KEY (`sale_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 row in set (0.00 sec)

mysql> SELECT * FROM sale;

+------------+------------+

| sale_date  | sale_count |

+------------+------------+

| 2020-01-01 |      10000 |

| 2020-02-01 |       8000 |

| 2020-03-01 |      12000 |

| 2020-04-01 |       9000 |

| 2020-05-01 |      10000 |

| 2020-06-01 |      18000 |

+------------+------------+

6 rows in set (0.00 sec)

其中,sale_date 表示销售的日期,sale_count 表示每月的销售数量。现在有一个需求,老板想要统计每个月销售数量的变化,以此做商业决策。这条 SQL 语句需要应用到非等值连接,但也并不是太难写:

SELECT 

    s1.sale_date, s2.sale_count - s1.sale_count AS diff

FROM

    sale s1

        LEFT JOIN

    sale s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date

ORDER BY sale_date;

然而,在执行的过程中,由于列 sale_count 用到了 unsigned 属性,会抛出这样的结果:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`s2`.`sale_count` - `test`.`s1`.`sale_count`)'

可以看到,MySQL 提示用户计算的结果超出了范围。其实,这里 MySQL 要求 unsigned 数值相减之后依然为 unsigned,否则就会报错。

为了避免这个错误,需要对数据库参数 sql_mode 设置为 NO_UNSIGNED_SUBTRACTION,允许相减的结果为 signed,这样才能得到最终想要的结果:

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';

Query OK, 0 rows affected (0.00 sec)

SELECT

    s1.sale_date,

    IFNULL(s2.sale_count - s1.sale_count,'') AS diff

FROM

    sale s1

    LEFT JOIN sale s2

    ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date

ORDER BY sale_date;

+------------+-------+

| sale_date  | diff  |

+------------+-------+

| 2020-01-01 |       |

| 2020-02-01 | 2000  |

| 2020-03-01 | -4000 |

| 2020-04-01 | 3000  |

| 2020-05-01 | -1000 |

| 2020-06-01 | -8000 |

+------------+-------+

6 rows in set (0.00 sec)

浮点类型和高精度型

除了整型类型,数字类型常用的还有浮点和高精度类型。
MySQL 之前的版本中存在浮点类型 Float 和 Double,但这些类型因为不是高精度,也不是 SQL 标准的类型,所以在真实的生产环境中不推荐使用,否则在计算时,由于精度类型问题,会导致最终的计算结果出错。
更重要的是,从 MySQL 8.0.17 版本开始,当创建表用到类型 Float 或 Double 时,会抛出下面的警告:MySQL 提醒用户不该用上述浮点类型,甚至提醒将在之后版本中废弃浮点类型

Specifying number of digits for floating point data types is deprecated and will be removed in a future release

而数字类型中的高精度 DECIMAL 类型可以使用,当声明该类型列时,可以(并且通常必须要)指定精度和标度,例如:

salary DECIMAL(8,2)

其中,8 是精度(精度表示保存值的主要位数),2 是标度(标度表示小数点后面保存的位数)。通常在表结构设计中,类型 DECIMAL 可以用来表示用户的工资、账户的余额等精确到小数点后 2 位的业务。

然而,在海量并发的互联网业务中使用,金额字段的设计并不推荐使用 DECIMAL 类型,而更推荐使用 INT 整型类型(下文就会分析原因)。

业务表结构设计实战

整型类型与自增设计

在真实业务场景中,整型类型最常见的就是在业务中用来表示某件物品的数量。例如上述表的销售数量,或电商中的库存数量、购买次数等。在业务中,整型类型的另一个常见且重要的使用用法是作为表的主键,即用来唯一标识一行数据。
整型结合属性 auto_increment,可以实现自增功能,但在表结构设计时用自增做主键,希望你特别要注意以下两点,若不注意,可能会对业务造成灾难性的打击:

  • 用 BIGINT 做主键,而不是 INT;
  • 自增值并不持久化,可能会有回溯现象(MySQL 8.0 版本前)。

从表 1 可以发现,INT 的范围最大在 42 亿的级别,在真实的互联网业务场景的应用中,很容易达到最大值。例如一些流水表、日志表,每天 1000W 数据量,420 天后,INT 类型的上限即可达到。
因此,用自增整型做主键,一律使用 BIGINT,而不是 INT。不要为了节省 4 个字节使用 INT,当达到上限时,再进行表结构的变更,将是巨大的负担与痛苦。
那这里又引申出一个有意思的问题:如果达到了 INT 类型的上限,数据库的表现又将如何呢?是会重新变为 1?我们可以通过下面的 SQL 语句验证一下:

mysql> CREATE TABLE t (

    ->     a INT AUTO_INCREMENT PRIMARY KEY

    -> );

mysql> INSERT INTO t VALUES (2147483647);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t VALUES (NULL);

ERROR 1062 (23000): Duplicate entry '2147483647' for key 't.PRIMARY'

可以看到,当达到 INT 上限后,再次进行自增插入时,会报重复错误,MySQL 数据库并不会自动将其重置为 1。
第二个特别要注意的问题是,MySQL 8.0 版本前,自增不持久化,自增值可能会存在回溯问题!

mysql> SELECT * FROM t;

+---+

| a |

+---+

| 1 |

| 2 |

| 3 |

+---+

3 rows in set (0.01 sec)

mysql> DELETE FROM t WHERE a = 3;

Query OK, 1 row affected (0.02 sec)

mysql> SHOW CREATE TABLE t\G

*************************** 1. row ***************************

       Table: t

Create Table: CREATE TABLE `t` (

  `a` int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 row in set (0.00 sec

可以看到,在删除自增为 3 的这条记录后,下一个自增值依然为 4(AUTO_INCREMENT=4),这里并没有错误,自增并不会进行回溯。但若这时数据库发生重启,那数据库启动后,表 t 的自增起始值将再次变为 3,即自增值发生回溯。具体如下所示:

mysql> SHOW CREATE TABLE t\G

*************************** 1. row ***************************

       Table: t

Create Table: CREATE TABLE `t` (

  `a` int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 row in set (0.00 s

若要彻底解决这个问题,有以下 2 种方法:

  • 升级 MySQL 版本到 8.0 版本,每张表的自增值会持久化;
  • 若无法升级数据库版本,则强烈不推荐在核心业务表中使用自增数据类型做主键。

其实,在海量互联网架构设计过程中,为了之后更好的分布式架构扩展性,不建议使用整型类型做主键,更为推荐的是字符串类型。

资金字段设计

在用户余额、基金账户余额、数字钱包、零钱等的业务设计中,由于字段都是资金字段,通常程序员习惯使用 DECIMAL 类型作为字段的选型,因为这样可以精确到分,如:DECIMAL(8,2)。

CREATE TABLE User (

  userId BIGINT AUTO_INCREMENT,

  money DECIMAL(8,2) NOT NULL,

  ......

)

在海量互联网业务的设计标准中,并不推荐用 DECIMAL 类型,而是更推荐将 DECIMAL 转化为 整型类型。也就是说,资金类型更推荐使用用分单位存储,而不是用元单位存储。如1元在数据库中用整型类型 100 存储。

金额字段的取值范围如果用 DECIMAL 表示的,如何定义长度呢?因为类型 DECIMAL 是个变长字段,若要定义金额字段,则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99,百万级的资金存储。

用户的金额至少要存储百亿的字段,而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义,不好统一。
另外重要的是,类型 DECIMAL 是通过二进制实现的一种编码方式,计算效率远不如整型来的高效。因此,推荐使用 BIG INT 来存储金额相关的字段。

字段存储时采用分存储,即便这样 BIG INT 也能存储千兆级别的金额。这里,1兆 = 1万亿。

这样的好处是,所有金额相关字段都是定长字段,占用 8 个字节,存储高效。另一点,直接通过整型计算,效率更高。
注意,在数据库设计中,我们非常强调定长存储,因为定长存储的性能更好。

我们来看在数据库中记录的存储方式,大致如下:

若发生更新,记录 1 原先的空间无法容纳更新后记录 1 的存储空间,因此,这时数据库会将记录 1 标记为删除,寻找新的空间给记录1使用,如:

上图中*记录 1 表示的就是原先记录 1 占用的空间,而这个空间后续将变成碎片空间,无法继续使用,除非人为地进行表空间的碎片整理。

那么,当使用 BIG INT 存储金额字段的时候,如何表示小数点中的数据呢?其实,这部分完全可以交由前端进行处理并展示。作为数据库本身,只要按分进行存储即可。

到此这篇关于MySQL数字类型自增的坑的文章就介绍到这了,更多相关MySQL数字类型自增内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2021-05-06

mysql修改自增长主键int类型为char类型示例

原来有一个表中的主键是int自增长类型, 因为业务变化需要把int改成char类型的主键.同时因为原来的表中已经存在了数据,不能删除表重建,只能修改表结构. 首先去掉自增长属性: alter table table_name  change indexid indexid int; 然后去掉主键: ALTER TABLE table_name   DROP   primary key; 修改表结构为char类型: alter table table_name change indexid ind

怎么重置mysql的自增列AUTO_INCREMENT初时值

重置 MySQL 自增列 AUTO_INCREMENT 初时值 注意, 使用以下任意方法都会将现有数据删除. 方法一: delete from tb1; ALTER TABLE tbl AUTO_INCREMENT = 100; (好处, 可以设置 AUTO_INCREMENT 为任意值开始) 提示:如果表列和数据很多, 速度会很慢, 如90多万条, 会在10分钟以上. 方法二: truncate tb1; (好处, 简单, AUTO_INCREMENT 值重新开始计数.) 怎么重置mysql的

MySQL中的主键以及设置其自增的用法教程

1.声明主键的方法: 您可以在创建表的时候就为表加上主键,如: CREATE TABLE tbl_name ([字段描述省略...], PRIMARY KEY(index_col_name)); 也可以更新表结构时为表加上主键,如: ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,-); /* 创建一个qq表,将qq_id设为主键,且没有对其进行NOT NULl约束 */ create table qq( qq_id int(10), n

MySQL字段自增自减的SQL语句示例介绍

MySQL的自增语句大家应该都很熟悉 也很简单 复制代码 代码如下: update `info` set `comments` = `comments`+1 WHERE `id` = 32 这样就可以了,但是有时候我们会涉及到做减法, 例如:文章的评论数,在删除或者锁定了一条评论之后需要对该文章总评论数减一 comments smallint(5) unsigned 文章评论总数统计字段 无符号即 0 ~ 65535 之间的数值 1. 通常情况下是可以类似上面自增的方法 把 +号 改成 -号 就

mysql中自增auto_increment功能的相关设置及问题

mysql中的自增auto_increment功能相信每位phper都用过,也都知道如何设置字段为自增字段,但并不是所有phper都知道auto_increment的起始值和递增量是如何设置的!本文就为大家分享一下mysql字段自增功能的具体查看及设置方法. 涉及知识介绍: mysql服务器维护着2种mysql的系统参数(系统变量):全局变量(global variables)和会话变量(session variables).它们的含义与区别如其各占的名称所示,session variables

mysql如何让自增id归0解决方案

最近老是要为现在这个项目初始化数据,搞的很头疼,而且数据库的Id自增越来越大,要让自增重新从1开始:那么就用下面的方法吧: 方法一: 如果曾经的数据都不需要的话,可以直接清空所有数据,并将自增字段恢复从1开始计数 truncate table 表名 方法二: dbcc checkident ('table_name', reseed, new_reseed_value) 当前值设置为 new_reseed_value.如果自创建表后没有将行插入该表,则在执行 DBCC CHECKIDENT 后插

mysql自增ID起始值修改方法

在mysql中很多朋友都认为字段为AUTO_INCREMENT类型自增ID值是无法修改,其实这样理解是错误的,下面介绍mysql自增ID的起始值修改与设置方法.通常的设置自增字段的方法:创建表格时添加: 复制代码 代码如下: create table table1(id int auto_increment primary key,...) 创建表格后添加: 复制代码 代码如下: alter table table1 add id int auto_increment primary key 自

mysql自增id超大问题的排查与解决

引言 小A正在balabala写代码呢,DBA小B突然发来了一条消息,"快看看你的用户特定信息表T,里面的主键,也就是自增id,都到16亿了,这才多久,在这样下去过不了多久主键就要超出范围了,插入就会失败,balabala......" 我记得没有这么多,最多1k多万,count了下,果然是1100万.原来运维是通过auto_increment那个值看的,就是说,表中有大量的删除插入操作,但是我大部分情况都是更新的,怎么会这样? 下面话不多说了,来一起看看详细的介绍吧 问题排查 这张表

两种mysql对自增id重新从1排序的方法

最近老是要为现在这个项目初始化数据,搞的很头疼,而且数据库的Id自增越来越大,要让自增重新从1开始:那么就用下面的方法吧: 方法一:如果曾经的数据都不需要的话,可以直接清空所有数据,并将自增字段恢复从1开始计数 truncate table 表名 方法二:dbcc checkident ('table_name', reseed, new_reseed_value) 当前值设置为 new_reseed_value.如果自创建表后没有将行插入该表,则在执行 DBCC CHECKIDENT 后插入的

深入探寻mysql自增列导致主键重复问题的原因

废话少说,进入正题. 拿到问题后,首先查看现场,发现问题表的中记录的最大值比自增列的值要大,那么很明显,当有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错.首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常.问题是解决了,接下来要搞清楚问题原因,什么操作导致了这种现象的发生呢? 这里有一种可能,即业务逻辑包含更新自增主键的代码,由于mysql的update动作不会同时更新自增列值,若更新主键值比自增列大,也会导致上述现象:记录最大值比

利用mysql事务特性实现并发安全的自增ID示例

项目中经常会用到自增id,比如uid,最简单的方法就是用直接用数据库提供的AUTO_INCREMENT,但是如果用户量非常大,几千万,几亿然后需要分表存储的时候呢,这种方案就搞不定了,所以最好有一个全局的自增ID的生成器,不管是否分表,都能从生成器中获取到全局自增的ID. 实现方法应该有很多,不过所有的方案都需要解决一个问题,就是保证在高并发的情景下,数据获取依然正确,每次获取的ID都不会重复. 这里我分享两种利用mysql的innodb的事务特性来实现的方案,一种是实现过了的,另一种没有试验过

PHP利用Mysql锁解决高并发的方法

前面写过利用文件锁来处理高并发的问题的,现在我们说另外一个处理方式,利用Mysql的锁来解决高并发的问题 先看没有利用事务的时候并发的后果 创建库存管理表 CREATE TABLE `storage` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `number` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=l

利用MySQL统计一列中不同值的数量方法示例

前言 本文实现的这个需求其实十分普遍,举例来说,我们存在一个用户来源表,用来标记用户从哪个渠道注册进来.表结构如下所示- 其中 origin 是用户来源,其中的值有 iPhone .Android .Web 三种,现在需要分别统计由这三种渠道注册的用户数量. 解决方案1 SELECT count(*) FROM user_operation_log WHERE origin = 'iPhone'; SELECT count(*) FROM user_operation_log WHERE ori

聊聊MySQL事务的特性和隔离级别

网上对于此类的文章已经十分饱和了,那还写的原因很简单--作为自己的理解笔记. 前言   此篇文章作为自己学习MySQL的一些个人理解,使用的引擎是InnoDb.首先先讲讲事务的概念,在<高性能MySQL>第三版中其对事务的描述是这样的: 事务就是一组原子性的SQL查询,或者说一个独立的工作单元.如果数据库引擎能够成功地对数据库应用该组查询的全部语句,那么就执行该组查询.如果其中有任何一条语句因为崩溃或其他原因无法执行,那么所有的语句都不会执行. ​​  换句话说,事务就是一个整体单位,里面的S

MySQL InnoDB如何保证事务特性示例详解

前言 如果有人问你"数据库事务有哪些特性"?你可能会很快回答出原子性.一致性.隔离性.持久性即ACID特性.那么你知道InnoDB如何保证这些事务特性的吗?如果知道的话这篇文章就可以直接跳过不看啦(#^.^#) 先说结论: redo log重做日志用来保证事务的持久性 undo log回滚日志保证事务的原子性 undo log+redo log保证事务的一致性 锁(共享.排他)用来保证事务的隔离性 重做日志 redo log 重做日志 redo log 分为两部分:一部分是内存中的重做

讲解MySQL中的事务特性

一个事务是一个连续的一组数据库操作,就好像它是一个单一的工作单元进行.换言之,永远不会是完整的事务,除非该组内的每个单独的操作是成功的.如果在事务的任何操作失败,则整个事务将失败. 实际上,会俱乐部许多SQL查询到一个组中,将执行所有的人都一起作为事务的一部分. 事务的特性: 事务有以下四个标准属性的缩写ACID,通常被称为: 原子性: 确保工作单元内的所有操作都成功完成,否则事务将被中止在故障点,和以前的操作将回滚到以前的状态. 一致性: 确保数据库正确地改变状态后,成功提交的事务. 隔离性:

MySQL事务的基础学习以及心得分享

事务是逻辑上的一组操作,组成这组操作的各个单元,要不全都成功要不全都失败,这个特性就是事务,下面就是关于MySQL事务学习中的心得分享: 事务的特性 1.原子性(Atomicity):原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生. 2.一致性(Consistency):在一个事务中,事务前后数据的完整性必须保持一致,可以想象银行转账.火车购票. 3.隔离性(Isolation):多个事务,事务的隔离性是指多个用户并发访问数据库时, 一个用户的事务不能被其它用户的事

利用MySQL系统数据库做性能负载诊断的方法

某大师曾说过,像了解自己的老婆 一样了解自己管理的数据库,个人认为包含了两个方面的了解: 1,在稳定性层面来说,更多的是关注高可用.读写分离.负载均衡,灾备管理等等high level层面的措施(就好比要保证生活的稳定性) 2,在实例级别的来说,需要关注内存.IO.网络,热点表,热点索引,top sql,死锁,阻塞,历史上执行异常的SQL(好比生活品质细节)MySQL的performance_data库和sys库提供了非常丰富的系统日志数据,可以帮助我们更好地了解非常细节的,这里简单地列举出来了

分析Mysql事务和数据的一致性处理问题

这篇文章通过安全性,用法,并发处理等方便详细分析了Mysql事务和数据的一致性处理问题,以下就是全部内容: 在工作中,我们经常会遇到这样的问题,需要更新库存,当我们查询到可用的库存准备修改时,这时,其他的用户可能已经对这个库存数据进行修改了,导致,我们查询到的数据会有问题,下面我们就来看解决方法. 在MySQL的InnoDB中,预设的Tansaction isolation level 为REPEATABLE READ(可重读) 如果SELECT 后面若要UPDATE 同一个表单,最好使用SEL

详解 Mysql 事务和Mysql 日志

事务特性 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节. 2.一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 .比如A向B转账,不可能A扣了钱,B却没收到. 3.隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰.比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账. 4.持久性(Durability):事务完成后,事务对数据库的所有更新