如何在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屋！