sql query only inserting 5 rows

Question

I have this table that I am creating with sql query

CREATE TABLE if not exists User (
    id bigint AUTO_INCREMENT,
    coins bigint not null,
    level bigint not null,
    country VARCHAR(128),
    PRIMARY KEY (id)
);

I want to insert 3 million entries

with coins being a random number from 0-99999

level being a random number from 1-100

with country being a random country from the 5 countries in the list

INSERT INTO User (coins, level, country)
SELECT
    FLOOR(RAND() * 100000) AS coins,
    FLOOR(RAND() * 100 + 1) AS level,
    countries.country AS country
FROM
    (SELECT "SPAIN" AS country UNION SELECT "FRANCE" UNION SELECT "UK" UNION SELECT "USA" UNION SELECT "GERMANY") countries
ORDER BY RAND()
LIMIT 3000000;

Yet when I get this I only get 5 rows. Adding 1 from each country. How can I fix this ?

Answer 1

INSERT INTO User (coins, level, country)
WITH RECURSIVE
  cte (id) AS (SELECT 1 UNION ALL SELECT id + 1 FROM cte LIMIT 3000000)
SELECT
    FLOOR(RAND() * 100000) AS coins,
    FLOOR(RAND() * 100 + 1) AS level,
    ELT(RAND() * 4 + 1, "SPAIN", "FRANCE", "UK", "USA", "GERMANY")
FROM cte;

fiddle