如何在SQL中为主键生成唯一的随机数 [英] How to generate a unique and random number for a primary key in sql
问题描述
我正在处理一个需求,其中我需要生成一个至少有10位数字的唯一(不可重复)和随机数(不可预测).我尝试了 SELECT FLOOR(RAND()* 9999999) ..,但是不能保证唯一性. 每月大约会插入约2万个值.
I am working on a requirement where I need to generate a unique(non-repeatable) and random number(unpredictable) with atleast 10 digits. I tried SELECT FLOOR(RAND() * 9999999).. but the uniqueness cant be guaranteed with this. About 20k values will be inserted per month approx.
我也想为插入的每个条目增加表的int列(number_of_hits)...我正在使用Spring Boot将值插入表中. 在创建表时尝试 number_of_hits int AUTO_INCREMENT ,但后来知道这仅适用于主键. 预先谢谢.!
Also I want to increment an int column of my table(number_of_hits) for every entry inserted... I am using spring boot to insert values into table. Tried number_of_hits int AUTO_INCREMENT while creating the table but later got to know this will only work on primary key.. Thanks in advance.!
推荐答案
我已经采用了以下解决方案,它也可以处理数百万条记录..谢谢大家的回答.
I have gone with the below solution and it works fine with millions of records too.. Thank you all for your answers.
Calendar cal = Calendar.getInstance();
long currentTime = cal.getTimeInMillis();
long Max = 9999999999999L;
long Min = 1000000000000L;
long range = Math.abs((long) (Math.random() * (Max - Min)) + Min);
long id = Math.addExact(currentTime, range);
String uniqueID = createUniqueID(id);
boolean isRepeated = urlShortenerRepository.existsByShortUrlKey(uniqueID);
while (isRepeated) {
range = Math.abs((long) (Math.random() * (Max - Min)) + Min);
id = Math.addExact(currentTime, range);
uniqueID = createUniqueID(id);
isRepeated = urlShortenerRepository.existsByShortUrlKey(uniqueID);
}
这篇关于如何在SQL中为主键生成唯一的随机数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!