mysql动态创建表 [英] mysql create table dynamically

问题描述

我正在mysql上创建数据库.首先创建主体表，每个表平均有30列. 日志表的标准是引用表的pk加上每列* 2. 像这样:

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:

参考表:

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

日志表:

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 //

然后…CALL createLogTable('exampleTable');

所以基本思想是

1. 根据需要与查询关联过程参数
2. 根据此查询字符串准备/执行语句

这篇关于mysql动态创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！