MySQL random select doesn't return anything-CodePudding

I am trying to make a app that generates a random username. I have a simple table with just an ID and a name column. Basically:

id	name
1	dude
2	chill
...	...

I want to select 3 random values from there. This is my attempt:

SELECT COUNT(id) INTO @count -- gets the length
FROM username_generator;
 
SELECT name FROM username_generator -- selects 3 names with random IDs
WHERE id IN (
    (FLOOR(RAND() * @COUNT) 1), 
    (FLOOR(RAND() * @COUNT) 1),
    (FLOOR(RAND() * @COUNT) 1)
);

The idea is that it counts the length and selects 3 IDs inside the length range.

The problem is that this sometimes returns NOTHING and I can't figure out why. The random numbers are always in the range and I have names in the table. Eg:

Normal:

Not normal:

CodePudding user response：

The problem is there could be gaps in the id range.

The simplest way around this, and indeed the simplest solution, is to forget about using count and just pick 3 names randomly as follows:

select name
from username_generator
order by rand()
limit 3

See live demo (refresh the demo page to see different names being returned each time the query runs).