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
WHERE "random_number" NOT IN (SELECT memberID FROM customer)
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?
You should simply use UUID.
That’s what I use now:
CREATE DEFINER=`root`@`localhost` PROCEDURE `randomPass`(OUT _password VARCHAR(7))
DECLARE count INT DEFAULT 0;
DECLARE alphanum INT;
DECLARE randomCharacter CHAR(1);
DECLARE password VARCHAR(10) DEFAULT "";
WHILE count<7 DO
SELECT ROUND(RAND()*10) INTO alphanum;
IF alphanum<5 THEN
SELECT CHAR(48+MOD(ROUND(RAND()*100),10)) INTO randomCharacter;
SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) INTO randomCharacter;
SELECT CONCAT(password,randomCharacter) INTO password;
As a procedure and:
CREATE TRIGGER `user_insert` BEFORE INSERT ON `customer`
FOR EACH ROW BEGIN
As a trigger
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.