mysql bulk insert with constraints

1 vote
1 answer
109 views
                          I created a table like this,

    CREATE TABLE Leaderboard(
      userId bigint not null,              
      matchId bigint not null,
      score mediumint not null,
      country CHAR(10),
      tournamentId int not null
    )

I am creating matches of 5 people, all from 5 distinct countries ("UK", "USA", "SPAIN", "GERMANY", "FRANCE)

I want to insert some a million entries to this table with following requirements,

A match will be formed like this

    usergermany | userUSA | userSPAIN | userUK | userFrance 

so a match will have 5 users, all from distinct countries. A tournament will have many matches, and a user can only play in one match in a tournament. 

So an example table will look like this

    userid    matchid    score     country   tournamentid
    
    ...
    988654    3877543    random    USA       177
    388654    3877543    random    GERMANY   177
    433432    3877543    random    FRANCE    177
    776212    3877543    random    UK        177
    1632987   3877543    random    SPAIN     177
    2113242   3877544    random    SPAIN     177
    2918974   3877544    random    USA       177
    111738    3877544    random    UK        177
    1772342   3877544    random    FRANCE    177
    1343243   3877544    random    GERMANY   177
    123131    3877545    random    UK        178
    1231414   3877545    random    FRANCE    178
    2858348   3877545    random    GERMANY   178
    1122432   3877545    random    USA       178
    2923434   3877545    random    SPAIN     178
    ...

A userId cannot exist in a tournament twice, 

A country cannot exists in a match twice (match groups are formed from distinct countries)

Also since 5 players compete in a match, a matchid will only appear 5 times in the table.

 

I want to insert a million entries into this table, userid being randomly from 1 to 3 million with the above constraints. 10 thousand matches for each tournament, so there will be 100 tournaments starting from 1.

100 tournaments, 10000 matches each,  make up to 1 million rows.


**--UPDATE--**

    create table seq_data as
    with recursive tmp(x) as (
        select 1
        union all
        select x+1 from tmp
        limit 3000000
    )
    select * from tmp;
     
    CREATE TABLE if not exists Leaderboard(
      userId bigint not null,
      matchId bigint not null,
      score mediumint not null,
      country CHAR(10),
      tournamentId int not null
    );
     
    DELIMITER //
    CREATE PROCEDURE InsertLeaderboardData()
    BEGIN
      DECLARE tournamentloop INT DEFAULT 0;
      DECLARE matchloop INT DEFAULT 0;
      DECLARE groupLoop INT DEFAULT 0;
      DECLARE matchidKey INT DEFAULT 1;
      WHILE tournamentloop < 1 DO
        SET tournamentloop = tournamentloop + 1;
        SET matchloop = 0;
        WHILE matchloop < 10000 DO
          SET matchidKey = matchidKey + 1;
          SET matchloop = matchloop + 1;
          SET groupLoop = 0;
          WHILE groupLoop < 5 DO
           SET groupLoop = groupLoop + 1;
           INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId)
           VALUES (1, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);
           SELECT matchidKey;
          END WHILE;
        END WHILE;
      END WHILE;
    END;
    //
     
    DELIMITER ;
    CALL InsertLeaderboardData();
    DROP PROCEDURE InsertLeaderboardData;


This runs but slowly, yet I want 1 more feature.

As to INSERT INTO's userId I set the value to 1 for now to edit it later. What I want to do is, I created sequenced numbers into the table seq_data starting from 1 up to 3 million. For each WHILE tournamentloop < 100 DO scope, I want to get 50 thousand DISTINCT numbers from the seq_data table. so I can give the users an id. 

The morale is, no userid can appear twice in a tournament. so for each tournament scope i need to retrieve random ids which are distinct and assign it to rows. I already created the table for sequenced numbers, I don't know what is the best approach from here.


-- Statement


    INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId) VALUES (useridKey, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);

I turn this into


    DECLARE tournamentloop INT DEFAULT 0;
    DECLARE matchloop INT DEFAULT 0;
    DECLARE groupLoop INT DEFAULT 0;
    DECLARE matchidKey INT DEFAULT 1;
    DECLARE useridKey INT DEFAULT 1;
    PREPARE insertStmt FROM 'INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId)VALUES (useridKey, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);';
     EXECUTE insertStmt;

I get error: Unknown column 'useridKey' in 'field list'

                        
Asked by umarkaa (47 rep)
Apr 4, 2024, 07:21 AM
Last activity: Apr 5, 2024, 10:56 AM
mysql bulk insert with constraints

Related Questions
