mysql - 使用动态 MySQL 创建新表

Question

我想更改此查询:

CREATE TABLE diablo.diablo_sales2 LIKE diablo.diablo_sales;

像这样动态的:

set @old = 'diablo_sales';
set @new = 'diablo_sales2 ';
set @schema = 'diablo';

set @query = 'create table @schema.@new like @schema.@old';

这在 MySQL 中甚至可能吗？如何执行@query？

编辑:下面是我试图动态化的整个查询

 -- create same table structure - blank table
CREATE TABLE diablo.diablo_sales2 LIKE diablo.diablo_sales;

-- add the new column(s)
ALTER TABLE diablo.diablo_sales2 
add column new_column decimal(10);


-- insert the old data into new table - new column will remain blank
INSERT INTO diablo.diablo_sales2 
(
column1,
column2

)
SELECT * FROM diablo.diablo_sales;

-- rename the new table to its original name. Drop the old table.
RENAME table diablo_sales TO `old`, diablo_sales2 TO diablo_sales;
DROP TABLE old;

Answer 1

您可以使用 SQL Syntax for Prepared Statements :

SET @sql := CONCAT('
  CREATE TABLE `',REPLACE(@schema,'`','``'),'`.`',REPLACE(@new,'`','``'),'`
          LIKE `',REPLACE(@schema,'`','``'),'`.`',REPLACE(@old,'`','``'),'`
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我冒昧地引用了标识符并调用了 REPLACE()转义其中包含的任何引号，以防止 SQL 注入(inject)。