1

I have a table called events defined as follow:

enter image description here

rep_id, hcp_id, brand_id, email_id and content_id is the IDs in other tables.

event_type should be kind of array containing this values:

[
    "rep_login",
    "rep_share",
    "email_viewed",
    "brand_page_opened",
    "media_viewed",
    "rep_contacted",
]

I need to insert 10k rows on events table but compliance with these conditions:

  • Each row should contains UNIQUE values for each column
  • event_type should be randomize for populate the table with several event types

This data is for testing purpose. Can this be done from MySQL query? How?

  • For your table structures, can you please give us a "SHOW CREATE TABLE my_table\G"? "For Each row should contains UNIQUE values for each column" - just put a UNIQUE index on each one. It's your array structure that's going to be much more difficult. JSON (will be/is in 5.7 I think) might be the way to go. You could look at PostgreSQL 9.4 which has JSON support. For further MySQL opinion, could you give some sample data and your desired result and the logic you used to get it? – Vérace May 22 '15 at 2:16
1

You can use this insert statement to insert some totally useless data in there (assuming the FK accuracy is not important here) but it satisfies your conditions.

    DELIMITER $$
DROP PROCEDURE IF EXISTS Events_insertion$$
CREATE PROCEDURE Events_insertion()
BEGIN
    DECLARE i  INT;
    DECLARE dTime DATETIME;
    SET i = 1;
    SET dTime = NOW();
    WHILE i  <= 10000 DO
       INSERT INTO EVENTS_TEST VALUES
        (
        null,
        (ELT(0.5 + RAND() * 6, "rep_login",
            "rep_share",
            "email_viewed",
            "brand_page_opened",
            "media_viewed",
            "rep_contacted")),
        LAST_INSERT_ID()+2,
        LAST_INSERT_ID()+3,
        LAST_INSERT_ID()+4,
        LAST_INSERT_ID()+5,
        LAST_INSERT_ID()+6,
        (dTime + interval (LAST_INSERT_ID()) second),
        (dTime + interval (LAST_INSERT_ID()) minute)
        );
       SET  i = i + 1; 
    END WHILE;
END$$
DELIMITER ;

You can call this procedure like this

call Events_insertion();

and also try not to use name events as your table name since it is a reserved word. Hope this could help you :)

  • I have no experience with scripts or stored procedures, can you add that also to your answer? Right now I am trying to get this done from PHP side using a script and doing multiple queries – ReynierPM May 22 '15 at 2:37
  • i made a stored procedure for it to insert 10K rows (since we don't post PHP codes here) – Masoud May 22 '15 at 2:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.