• 售前

  • 售后

热门帖子
入门百科

解决mysql的int型主键自增题目

[复制链接]
123456823 显示全部楼层 发表于 2021-8-14 15:05:47 |阅读模式 打印 上一主题 下一主题
引入

我们在使用mysql数据库时,风俗使用int型作为主键,并设置为自增,这既可以大概保证唯一,使用起来又很方便,但int型的长度是有限的,假如凌驾长度怎么办呢?
袒露问题

我们先创建一个测试表,创建语句如下:
  1. CREATE TABLE test1 (
  2.   id INT PRIMARY KEY AUTO_INCREMENT,
  3.   NAME VARCHAR(20)
  4. )
复制代码
然后我们插入两条数据:
  1. INSERT INTO test1 VALUES(NULL,'小牛');
  2. INSERT INTO test1 VALUES(NULL,'大牛');
复制代码
查询表显示正常:

int型的有符号的范围为231 -1 = 2147483647,我们直接插入一条数据id为2147483647,如下:
  1. INSERT INTO test1 VALUES(2147483647 ,'小华')
复制代码
效果显示正常:

此时自增ID已达到了int型的上限,假如我再插入数据,就会报错:
  1. INSERT INTO test1 VALUES(NULL,'母牛');
复制代码

此时主键已无法自增,插入的id仍然是2147483647,就违反了主键唯一的条件,以是报错。
办理问题

(1)使用更大的数据范例bigint

bigint的范围是263-1,所谓指数爆炸,此时的大小达到了9,223,372,036,854,775,807的可怕量级,简单来说就是用bigint 一天100w条数据也得存200亿年才气自增爆炸,以是在当前场景,几乎不消担心bigint会自增满
我们修改数据范例为bigint,如图

再执行插入语句:
  1. INSERT INTO test1 VALUES(NULL,'母牛');
复制代码
又可以大概正常插入了:

(2)使用UUID作为主键

我们都知道,UUID会根据当前系统性能,时间戳等一系列参数颠末运算得到一个全天下唯一的字符串,而且mysql提供了天生UUID的方法,用它作为主键可以大概保证数据的唯一性。
使用如下代码可以天生32位的UUID:
  1. -- 生成32位UUID
  2. SELECT REPLACE(UUID(),'-','') AS UUID;
复制代码

然后咱们再创建一个测试表:
  1. CREATE TABLE test2(
  2.   id VARCHAR(50) PRIMARY KEY,
  3.   NAME VARCHAR(20) NOT NULL
  4. )
复制代码
插入一条数据:
  1. -- 插入UUID
  2. INSERT INTO test2 VALUES(REPLACE(UUID(),'-',''),'老王');
复制代码

但如许写插入语句每次都要手写UUID函数,貌似有点太麻烦了,咱们可以写一个触发器,让触发器自动为我们设置ID:
  1. -- 创建触发器
  2. DELIMITER $$
  3. CREATE
  4. TRIGGER auto_id        -- 名称
  5. BEFORE INSERT          -- 触发时机
  6. ON test2 FOR EACH ROW   -- 作用于test2表,对每行数据生效
  7. BEGIN
  8. IF new.id = '' THEN     -- 当id为空字符串时设置UUID
  9.   SET new.id = REPLACE(UUID(),'-','');
  10. END IF;
  11. END$$
复制代码
插入一条数据:
  1. -- 插入一条数据
  2. INSERT INTO test2 VALUES('','小王');
复制代码
效果能正常添加

总结

(1) 用int型和bigInt型增编削查速率较UUID更快,而且更节省空间。
(2) 用UUID更方便。
为何要使用自增int作为主键

相信各人都知道要使用无符号自增int作为主键的数据范例,可你知道为何要使自用增int而不是使用varchar、text、varchar等范例吗?
各人也能说出一些长处:对上层业务透明,插入数据时无需显示指定;数据范例简单,更便于存储维护表布局
实在,使用自增int作为主键好处多多,本日我们就来一起学习一下,并猛烈发起各人在现实开发中使用自增int作为主键。
长处:

1、int 相比varchar、char、text使用更少的存储空间,而且数据范例简单,可以节约CPU的开销,更便于表布局的维护
2、默认都会在主键上建立主键索引,使用整形作为主键可以将更多的索引载入内存,提高查询性能
3、对于InnoDB存储引擎而言,每个二级索引都会使用主键作为索引值的后缀,使用自增主键可以镌汰索引的长度(大小),方便更多的索引数据载入内存
4、可以使索引数据更加紧凑,在数据插入、删除、更新时可以做到索引数据尽可能少的移动、分裂页,镌汰碎片的产生(可以通过optimize table 来重修表),镌汰维护开销
5、在数据插入时,可以保证逻辑相邻的元素物理也相邻,便于范围查找
固然,使用自增int作为主键也不是百利无一害,在高并发的情况下也可能会造成锁的争用问题。
以上为个人经验,希望能给各人一个参考,也希望各人多多支持草根技术分享。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作