MySQL stored procedure to INSERT DELAYED but CREATE TABLE first if needed?

Question

I'm planning on doing a lot of INSERT DELAYED into MyISAM tables. But the tables might not exist yet. Let's say e.g. a new table will exist for each day.

So instead of detecting absence of table in my client and creating then retrying the insert, it seems like a good case for a stored procedure ("function").

Is this possible, and what would it look like? Are there any downsides to this approach?

Answer 1

It seems a waste of time to check with every single insert whether the table exist. If you need day by day tables (in my opinion they defy much of the logic usually found in databases, so I try to avoid them, I'd):

1. Create the default table to insert 'my_logging_table'.
2. Do all inserts / updates / deletes on that table.
3. Run a daily cronjob to create the table of yesterday ('my_logging_table-2010-06-15'), and inserting all of yesterdays records in it, after which they're deleted from the main table.
4. If that is to much of a rewrite for your application you could make an updatable view with the current tablename+day ('my_logging_table-2010-06-16'), which just inserts in the 'my_logging_table'

Another approach would be a MERGE table (see http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html), with a daily cronjob creating the 'today' table & updating the tablelist for the merge table with an alter table statement.

Answer 2

You may make a good scheme to accomplish the same.

For example, if you have a table "Logged_users", which will be created every day, you could just append a column to the users table, or make an extra table with *ID_USER, DAY* columns (that would be a better choice). Then, you may program a CRON job to delete every rows of past days, and execute that job every day at 12:00 am.

Answer 3

Wouldn't it be an idea to create a scheduled event that runs every night before midnight, and create the log-table for the next day. That way you could always rely on the table beeing created before any INSERT would occur?

CREATE EVENT createLogTable
ON SCHEDULE EVERY 1 DAY  
STARTS '2010-6-20 23:59:00'   
DO
    BEGIN
        SET @tablename = CONCAT('my_logging_table-',DATE(DATE_ADD(NOW(),INTERVAL 1 DAY)));
        SET @query = CONCAT('CREATE TABLE IF NOT EXISTS`',@tablename,'` (`timestamp` INT,`event` VARCHAR( 20 )) ENGINE = MYISAM');
        PREPARE createStatement FROM @query;
        EXECUTE createStatement;
        DEALLOCATE PREPARE createStatement;
    END