mysql怎么动态创建一个表,mysql动态创建表

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?

解决方案

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