mysql create table dynamically

Question

I am creating a database on mysql. First creating the principal tables which on average has 30 columns per table. And the standard of the log table is the pk of the referenced table plus each column * 2. Like this:

Referenced Table:

Field   |   Type      |   Null   |   Key   |   Default   |   Extra
--------+-------------+----------+---------+-------------+----------------
TableID | int(11)     | No       | PRI     | Null        | auto_increment
Col1    | varchar(50) | No       |         | Null        |

Log Table:

Field       |   Type      |   Null   |   Key   |   Default   |   Extra
------------+-------------+----------+---------+-------------+----------------
LogTableID  | int(11)     | No       | PRI     | Null        | auto_increment
TableID     | int(11)     | No       | MUL     | Null        |
NewCol1     | varchar(50) | No       |         | Null        |
UpdatedCol1 | varchar(50) | No       |         | Null        |

Now what I want is to create a procedure on which I pass the table name as a parameter and generates the creation of the table log query and executes it.

What is the best way to do this?

I'm not clear what this means: "generates the creation of the table log query and executes it." - are you wanting to create a new table or an entry in an existing table? both? — ethrbunny
– ethrbunny, Commented Jan 17, 2013 at 12:37
So you want a stored procedure, which given the name of an existing table generates another table based on it? — Dutow
– Dutow, Commented Jan 17, 2013 at 13:34

inhan · Accepted Answer · 2013-01-17 17:46:10Z

7

To make a string represent a table (or database) name you will need to concat your query string with the variable and prepare/execute a statement right in the stored procedure. Here's a basic example.

-- DROP PROCEDURE IF EXISTS createLogTable;
DELIMITER //
CREATE PROCEDURE createLogTable(tblName VARCHAR(255))
BEGIN
    SET @tableName = tblName;
    SET @q = CONCAT('
        CREATE TABLE IF NOT EXISTS `' , @tableName, '` (
            `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
            `something` VARCHAR(10) NOT NULL,
            `somedate` DATETIME NOT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    ');
    PREPARE stmt FROM @q;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    -- and you're done. Table is created.
    -- process it here if you like (INSERT etc)
END //

Then… CALL createLogTable('exampleTable');

So the basic idea is

concat the procedure parameter(s) with your query as necessary
prepare/execute a statement from this query string

edited Jan 17, 2013 at 17:46

answered Jan 17, 2013 at 14:15

inhan

7,5002 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jcvegan Over a year ago

It might work but I need to get all the columns of the table. I try using the show columns from MyTable, trying to set the result to a variable. But I got an error.

inhan Over a year ago

@JuanCarlosVegaNeira You can create a temporary table in the procedure when you need multiple rows or you can use a loop. However, your main question is not so clear. You might want to edit it.

Vlad Over a year ago

@inhan, Any ideas of how create tables while in a trigger? I have already created procedure, but when I call this procedure in a trigger, I get an error.

Collectives™ on Stack Overflow

mysql create table dynamically

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related