mySQL random unique numbers + insterting them

[ultimativebananedest]

Hey people,

I wanted to generate a random number (for a unique and unpredictable memberID) and insert it into every memberID column which has not a memberID already (equals NULL).
I dont want to change the already generated memberIDs in the meantime.

SELECT FLOOR(10000 + RAND() * 89999) AS random_number
FROM customer
WHERE "random_number" NOT IN (SELECT memberID FROM customer)
LIMIT 1

This is the SQL query to generate a unique random number (I hope :D).
So how can I manage the rest now?

Or even better, can I generate a random, unique and unpredictable ID automatically for every new member I create?

[psprianca25]

You should simply use UUID.

[ultimativebananedest]

That’s what I use now:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `randomPass`(OUT _password VARCHAR(7))
BEGIN
	DECLARE count INT DEFAULT 0;
	DECLARE alphanum INT;
	DECLARE randomCharacter CHAR(1);
	DECLARE password VARCHAR(10) DEFAULT "";
	
	WHILE count<7 DO
		SET count=count+1;
		SELECT ROUND(RAND()*10) INTO alphanum;

		IF alphanum<5 THEN
						SELECT CHAR(48+MOD(ROUND(RAND()*100),10)) INTO randomCharacter;
			
								ELSE
						SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) INTO randomCharacter;
			
								END IF;
				SELECT CONCAT(password,randomCharacter) INTO password;
	END WHILE;
	
			SET _password=password;
END$$
DELIMITER ;

As a procedure and:

CREATE TRIGGER `user_insert` BEFORE INSERT ON `customer`
 FOR EACH ROW BEGIN
		CALL tcsiegentermine.randomPass(NEW.memberID);
	END

As a trigger