本篇文章小编给大家分享一下解决mysql的int型主键自增问题方法,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
引入
我们在使用mysql数据库时,习惯使用int型作为主键,并设置为自增,这既能够保证唯一,使用起来又很方便,但int型的长度是有限的,如果超过长度怎么办呢?
暴露问题
我们先创建一个测试表,创建语句如下:
CREATE TABLE test1 ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) )
然后我们插入两条数据:
INSERT INTO test1 VALUES(NULL,'小牛'); INSERT INTO test1 VALUES(NULL,'大牛');
查询表显示正常:
int型的有符号的范围为231 -1 = 2147483647,我们直接插入一条数据id为2147483647,如下:
INSERT INTO test1 VALUES(2147483647 ,'小华')
结果显示正常:
此时自增ID已达到了int型的上限,如果我再插入数据,就会报错:
INSERT INTO test1 VALUES(NULL,'母牛');
此时主键已无法自增,插入的id仍然是2147483647,就违反了主键唯一的条件,所以报错。
解决问题
(1)使用更大的数据类型bigint
bigint的范围是263-1,所谓指数爆炸,此时的大小达到了9,223,372,036,854,775,807的可怕量级,简单来说就是用bigint 一天100w条数据也得存200亿年才能自增爆炸,所以在当前场景,几乎不用担心bigint会自增满
我们修改数据类型为bigint,如图
再执行插入语句:
INSERT INTO test1 VALUES(NULL,'母牛');
又能够正常插入了:
(2)使用UUID作为主键
我们都知道,UUID会根据当前系统性能,时间戳等一系列参数经过运算得到一个全世界唯一的字符串,并且mysql提供了生成UUID的方法,用它作为主键能够保证数据的唯一性。
利用如下代码可以生成32位的UUID:
-- 生成32位UUID SELECT REPLACE(UUID(),'-','') AS UUID;
然后咱们再创建一个测试表:
CREATE TABLE test2( id VARCHAR(50) PRIMARY KEY, NAME VARCHAR(20) NOT NULL )
插入一条数据:
-- 插入UUID INSERT INTO test2 VALUES(REPLACE(UUID(),'-',''),'老王');
但这样写插入语句每次都要手写UUID函数,貌似有点太麻烦了,咱们可以写一个触发器,让触发器自动为我们设置ID:
-- 创建触发器 DELIMITER $$ CREATE TRIGGER auto_id -- 名称 BEFORE INSERT -- 触发时机 ON test2 FOR EACH ROW -- 作用于test2表,对每行数据生效 BEGIN IF new.id = '' THEN -- 当id为空字符串时设置UUID SET new.id = REPLACE(UUID(),'-',''); END IF; END$$
插入一条数据:
-- 插入一条数据 INSERT INTO test2 VALUES('','小王');
结果能正常添加
总结
(1) 用int型和bigInt型增删改查速度较UUID更快,并且更节省空间。
(2) 用UUID更方便。