0

I tried to write a SQL-function that generates an unused unique ID in a range between 1000000 and 4294967295. I need numeric values, so UUID() alike is not a solution. It doesn't sound that difficult, but for some reason, the code below does not work when called within an INSERT-statement on a table as value for the primary key (not auto_increment, of course). The statement is like INSERT INTO table (id, content) VALUES ((SELECT getRandomID(0,0)), 'blabla bla'); (Since default values are not allowed in such functions, I shortly submit 0 for each argument and set it in the function to the desired value.)

Called once and separated from INSERT or Python-code, everything is fine. Called several times, something weird happens and not only the whole process but also the server might hang within REPEAT. The process is then not even possible to kill/restart; I have to reboot the machine -.- It also seems to only have some random values ready for me, since the same values appear again and again after some calls, allthough I actually thought that the internal rand() would be a sufficient start/seed for the outer rand(). Called from Python, the loop starts to hang after some rounds although the very first one in my tests always produces a useful, new ID and therefore should quit after the first round. Wyh? Well, the table is empty...so SELECT COUNT(*)... returns 0 which actually is the signal for leaving the loop...but it doesn't.

Any ideas? I'm running MariaDB 10.something on SLES 12.2. Here is the exported source code:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getRandomID`(`rangeStart` BIGINT UNSIGNED, `rangeEnd` BIGINT UNSIGNED) RETURNS bigint(20) unsigned
READS SQL DATA
BEGIN
    DECLARE rnd BIGINT unsigned; 
    DECLARE i BIGINT unsigned; 

    IF rangeStart is null OR rangeStart < 1 THEN
        SET rangeStart = 1000000;
    END IF;
    IF rangeEnd is null OR rangeEnd < 1 THEN
        SET rangeEnd = 4294967295; 
    END IF; 

    SET i = 0;

    r: REPEAT
        SET rnd = FLOOR(rangeStart + RAND(RAND(FLOOR(1 + rand() * 1000000000))*10) * (rangeEnd - rangeStart));
        SELECT COUNT(*) INTO i FROM `table` WHERE `id` = rnd;    
    UNTIL i = 0 END REPEAT r; 

    RETURN rnd;
END$$
DELIMITER ;
meistermuh
  • 393
  • 3
  • 11

1 Answers1

2

A slight improvement:

    SELECT COUNT(*) INTO i FROM `table` WHERE `id` = rnd;    
UNTIL i = 0 END REPEAT r; 

-->

UNTIL NOT EXISTS( SELECT 1 FROM `table` WHERE id = rnd ) REPEAT r; 

Don't pass any argument to RAND -- that is for establishing a repeatable sequence of random numbers.

mysql> SELECT RAND(123), RAND(123), RAND(), RAND()\G
*************************** 1. row ***************************
RAND(123): 0.9277428611440052
RAND(123): 0.9277428611440052
   RAND(): 0.5645420109522921
   RAND(): 0.12561983719991504
1 row in set (0.00 sec)

So simplify to

    SET rnd = FLOOR(rangeStart + RAND() * (rangeEnd - rangeStart));

If you want to include rangeEnd in the possible outputs, add 1:

    SET rnd = FLOOR(rangeStart + RAND() * (rangeEnd - rangeStart + 1));
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • thanks a lot! after a mistake in applying the changes to the function, everything is working fine now. maybe it was because of the argument of RAND() that it hung before? or something magical else. in >1000 rounds now I did not get any random integer twice :) – meistermuh Nov 24 '17 at 09:16
  • You should not make any assumptions about non-dups in a sequence of calls to `RAND()`. To be _truely_ random, there can be _consecutive_ return values that are the same. (I don't think it happens in practice.) Do you want to discuss getting a sequence of random, but distinct, values? – Rick James Apr 17 '18 at 15:52