sql query only inserting 5 rows

ghz 8months ago ⋅ 54 views

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 ?

Answers

The issue you're encountering is that the subquery to generate random countries is being executed only once, resulting in a single set of random countries being selected and then repeated for all rows in the SELECT statement.

To fix this, you can generate random countries for each row by incorporating the RAND() function directly into the subquery for the countries. Here's how you can modify your query:

INSERT INTO User (coins, level, country)
SELECT
    FLOOR(RAND() * 100000) AS coins,
    FLOOR(RAND() * 100 + 1) AS level,
    country
FROM
    (SELECT
        CASE FLOOR(RAND() * 5)
            WHEN 0 THEN 'SPAIN'
            WHEN 1 THEN 'FRANCE'
            WHEN 2 THEN 'UK'
            WHEN 3 THEN 'USA'
            ELSE 'GERMANY'
        END AS country
    FROM
        (SELECT n FROM (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS numbers) AS rand_nums
    ) AS countries
ORDER BY RAND()
LIMIT 3000000;

In this modified query, for each row, a random number between 0 and 4 is generated to select a country from the list. This ensures that each row will have a different random country selected. The ORDER BY RAND() statement is retained to shuffle the results before limiting to 3 million rows.

#mysql