MySql Event with LOOP based on a select statemet

by

Not too familiar with the coding inside events, but the goal is:

  1. Select the “company_id” field of table “companies”. (simple select id from table)
  2. Use the list of step 1 into a loop to fetch data from a custom table: eq: “select fid from table_%id% where...“
  3. Now create a record in another table from data together “insert into resulttable (id, fid) values (%id, %fid)

Then create an event to do this every hour

I’d like to do this without creating a procedure, but might need to.

This is as close as I can get:

DECLARE cur_orgs CURSOR FOR SELECT orgId FROM organizations;  
OPEN cur_orgs;
Reading_Orgs: LOOP
    FETCH cur_orgs INTO _org_id;
    
    DECLARE cur_fids CURSOR FOR SELECT fid FROM organization_+'_org_id';  
    OPEN cur_fids;
    Reading_fids: LOOP
        FETCH cur_fids INTO _fid_id;

        insert into queue(orgId, fid) values (_org_id, _fid_id);
    END LOOP;
    CLOSE cur_fids;

    insert into queue(orgId) values (_org_id);
END LOOP;
CLOSE cur_orgs;

  • Coding inside events is similar to coding inside stored procedures. But there are no procedure parameters. It’s all documented on the web site.

    – 




  • Do you have a specific question about this? If you show what you tried we’ll help you fix it, we won’t write it for you.

    – 

  • 1

    The fact that you need to access table names dynamically suggests a poor schema design. Dynamic information should be in table contents, not table and column names. You shouldn’t have a different table for each company.

    – 




  • If you really have to do it this way, you need to use dynamic SQL using PREPARE and EXECUTE.

    – 

  • Like @Barmar said, change your data model. Your current model creates many problems, this is just one of those. Read about 3NF and give it a try

    – 

Several comments:

  • MySQL does not use + for string concatenation; that’s a string concatenation operator only in Microsoft SQL Server and Microsoft Access, as far as I know.

  • You can’t make a table name from an expression like organization_+'_org_id' anyway. Table names are identifiers, not strings. Identifiers must be fixed in the query at the time it is parsed.

  • You wrote in a comment that your separate tables are there for a legal requirement, so I won’t give you grief over designing the database this way.

I tested the following with MySQL 8.2.0:

CREATE EVENT myevent
ON SCHEDULE AT current_timestamp + INTERVAL 1 MINUTE
DO
BEGIN
    DECLARE org_id BIGINT UNSIGNED;
    DECLARE cur_orgs CURSOR FOR SELECT orgId FROM organizations;

    OPEN cur_orgs;

    Reading_Orgs: LOOP
        FETCH cur_orgs INTO org_id;
        SET @org_id = org_id;

        SET @sql = CONCAT(
          'INSERT INTO queue(orgId, fid)
          SELECT ?, fid FROM organization_', @org_id);

        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @org_id;
        DEALLOCATE PREPARE stmt;

        INSERT INTO queue(orgId) VALUES (@org_id);
    END LOOP;
    CLOSE cur_orgs;
END

Things to notice:

  • I used INSERT...SELECT. This is because cursors in MySQL don’t support dynamic SQL.

  • String concatenation of the query string is done with CONCAT(), not +.

  • PREPARE and EXECUTE only work with user variables (the type with the @ sigil), but cursors only work to fetch into declared local variables, so I had to copy org_id to @org_id. These are two different types of variables in MySQL.

  • Be careful about the potential SQL injection risk. I assume the org_id variable has a numeric type, so there’s no way it can contain risky characters. If org_id is a string instead of a number, then this code has an SQL injection vulnerability, because the value of an org id could cause the prepared statement to do something you don’t intend.

The result:

mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| organization_123 |
| organization_456 |
| organizations    |
| queue            |
+------------------+

mysql> select * from organizations;
+-------+
| orgId |
+-------+
|   123 |
|   456 |
+-------+

mysql> select * from organization_123;
+------+
| fid  |
+------+
|  123 |
+------+

mysql> select * from organization_456;
+------+
| fid  |
+------+
|  456 |
+------+

… I wait a minute for the event to execute …

mysql> select * from queue;
+-------+------+
| orgId | fid  |
+-------+------+
|   123 |  123 |
|   123 | NULL |
|   456 |  456 |
|   456 | NULL |
+-------+------+

I don’t know what your purpose is of inserting rows in the queue table in this fashion, but I’ll trust you that it makes sense for your application. This answer is only meant to show the syntax for your looping routine, not the wisdom of designing your database as it is.

To be honest, I find MySQL stored routines so awkward to use, that I would recommend coding your events in some client language outside the database. It would be easier to develop and debug in practically any other language other than the MySQL stored routine language.

Leave a Comment