主页 / dba / 问题 / 14397
Accepted
Mark D
Asked: 2012-03-06 08:43:33 +0800 CST

从变量名创建表

  • 772

是否可以从变量名创建表？例如，我正在尝试编写以下存储过程，但它不会使用 create 语句编译过去。

CREATE DEFINER = 'sabretooth'@'%' PROCEDURE `sp_archive_sabretooth`(
    IN vhost INTEGER(11),
    IN record_age DATETIME,
    IN delete_records BOOLEAN
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i_exist BOOLEAN DEFAULT FALSE;
    DECLARE table_to_create, backup_table_name, database_name VARCHAR(255);
    DECLARE backup_tables_cursor CURSOR FOR 
            SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE 'vhost%' 
                             OR
                             INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid') 
                         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'sabretooth%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    

    INSERT INTO sabretooth.debug (message) VALUES (CONCAT('Initializing sp_archive_sabretooth; vhost = ', vhost, '; record age = ', record_age, '; delete_records = ', delete_records)); 

    SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
      INTO database_name
      FROM INFORMATION_SCHEMA.SCHEMATA
     WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'sabretooth%'
       AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'sabretooth_archive';

      OPEN backup_tables_cursor;

    create_tables_loop: LOOP
        FETCH backup_tables_cursor INTO table_to_create;
        IF done THEN
            LEAVE create_tables_loop;
        END IF;
        CREATE DATABASE IF NOT EXISTS sabretooth_archive;

        SELECT CONCAT(database_name, '.', table_to_create,'_archive') INTO backup_table_name;

        INSERT INTO sabretooth.debug (message) VALUES (CONCAT('I am here 38 backup_table_name = ',backup_table_name));

        CREATE TABLE CONCAT('sabretooth_archive.',table_to_create) LIKE CONCAT(database_name,'.',table_to_create);
    END LOOP;
END
mysql stored-procedures

1 个回答

  1. Best Answer

    您正在寻找的是一个称为动态 sql 的概念。为此，您将不得不使用准备好的语句。幸运的是，MySQL 允许在存储过程中准备好语句，但不允许存储函数或触发器。

    另外，幸运的是，MySQL 允许CREATE TABLE在命令上准备好语句。

    所以你将不得不准备这样的CREATE TABLE东西：

    SET @SQL = CONCAT('CREATE TABLE ',CONCAT('sabretooth_archive.',table_to_create),' LIKE ',CONCAT(database_name,'.',table_to_create));

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    • 6

相关问题