I would create a table with a pool of numbers:

Create Table pool (number int PRIMARY KEY AUTO_INCREMENT);
Insert Into pool (),(),(),(),(),(),(),(),…;

And then define a trigger which picks one random number from that pool:

CREATE TRIGGER pickrand BEFORE INSERT ON mytable 
FOR EACH ROW BEGIN
  DECLARE nr int;
  SET nr = (SELECT number FROM pool order by rand() limit 1);
  DELETE FROM pool WHERE number = nr;
  SET NEW.nr = nr;
END

In order to avoid concurrency issues you have to run queries in transactions. If performance becomes an issue (because of the slow order by rand()) you can change the way to select a random record.

I would create a table with a pool of numbers:

Create Table pool (number int PRIMARY KEY AUTO_INCREMENT);
Insert Into pool (),(),(),(),(),(),(),(),…;

And then define a trigger which picks one random number from that pool:

CREATE TRIGGER pickrand BEFORE INSERT ON mytable 
FOR EACH ROW BEGIN
  DECLARE nr int;
  SET nr = (SELECT number FROM pool order by rand() limit 1);
  DELETE FROM pool WHERE number = nr;
  SET NEW.nr = nr;
END

In order to avoid concurrency issues you have to run queries in transactions. If performance becomes an issue (because of the slow order by rand()) you can change the way to select a random record.

I would create a table with a pool of numbers. And then define a trigger which picks one random number from that pool, removes it from the pool and inserts that number into your target record. [Elaboration follows]

I would create a table with a pool of numbers:

Create Table pool (number int PRIMARY KEY AUTO_INCREMENT);
Insert Into pool (),(),(),(),(),(),(),(),…;

And then define a trigger which picks one random number from that pool:

CREATE TRIGGER pickrand BEFORE INSERT ON mytable 
FOR EACH ROW BEGIN
  DECLARE nr int;
  SET nr = (SELECT number FROM pool order by rand() limit 1);
  DELETE FROM pool WHERE number = nr;
  SET NEW.nr = nr;
END

In order to avoid concurrency issues you have to run queries in transactions. If performance becomes an issue (because of the slow order by rand()) you can change the way to select a random record.