mysql 存儲過程_mysql存儲過程執行SHOW CREATE TABLE

我正在嘗試編寫一個存儲過程來包裝SHOW CREATE TABLE。我的最終目標是動態查找information_schema中的模式名稱並運行SHOW CREATE TABLE schema.tableName。mysql存儲過程執行SHOW CREATE TABLE

看來我不能在存儲過程中運行SHOW CREATE TABLE。

DELIMITER $$

DROP PROCEDURE IF EXISTS ct$$

CREATE PROCEDURE ct (tableName VARCHAR(50))

BEGIN

SHOW CREATE TABLE tableName;

END$$

DELIMITER ;

mysql> CALL ct('users');

ERROR 1146 (42S02): Table 'adcentraldb.tableName' doesn't exist

mysql>

對於那些有興趣這就是我最終的SHOW創建一個工作

DELIMITER $$

DROP PROCEDURE IF EXISTS ct$$

-- Wraps around SHOW CREATE TABLE. Look at other schemas other than current.

CREATE PROCEDURE ct (tableName VARCHAR(50))

BEGIN

DECLARE dbName VARCHAR(50);

SET dbName = (SELECT `TABLE_SCHEMA` FROM `INFORMATION_SCHEMA`.`TABLES`

WHERE `TABLE_NAME` = tableName LIMIT 1);

SET @a=CONCAT("SHOW CREATE TABLE ", dbName, '.', tableName);

PREPARE stmt1 FROM @a;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

END$$

DELIMITER ;

2012-04-13

Yada

+0

無法將數據庫或表名稱作爲參數傳遞給MySQL存儲過程。相反，您需要構建一個SQL字符串和'EXECUTE()'它，這意味着您還必須謹慎對待SQL注入。 –

2012-04-13 01:20:36

+0

查看[這個問題](http://stackoverflow.com/questions/9993908/passing-fieldname-as-parameter-in-mysql-stored-procedure) –

2012-04-13 01:22:10

+0

@Yada我的答案是否適合你？ –

2012-04-13 12:13:18