存储过程中创建临时表,其中表名是参数,如何实现? 本帖最后由 forimprove 于 2009-11-21 10:41:06 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 MySQL中不支持表名为变量的动态直接用法,你需要通过prepare 预先准备SQL语句然后执行。mysql> DELIMITER $$mysql>mysql> DROP PROCEDURE IF EXISTS `CreateTempTable`$$Query OK, 0 rows affected (0.00 sec)mysql>mysql> CREATE PROCEDURE `CreateTempTable`(in paraName varchar(256)) -> BEGIN -> set @sql_CreateTempTable=concat('DROP TABLE IF EXISTS ',paraName); -> PREPARE stmt1 FROM @sql_CreateTempTable; -> EXECUTE stmt1 ; -> DEALLOCATE PREPARE stmt1; -> -> set @sql_CreateTempTable=concat('CREATE TABLE ',paraName, ' (', -> 'Owner varchar(256) default \'\',', -> 'CalleeNO varchar(256) default \'\',', -> 'DateTime datetime default NULL', -> ') ENGINE=MyISAM DEFAULT CHARSET=utf8' -> ); -> PREPARE stmt1 FROM @sql_CreateTempTable; -> EXECUTE stmt1 ; -> DEALLOCATE PREPARE stmt1; -> -> END$$Query OK, 0 rows affected (0.00 sec)mysql>mysql> DELIMITER ;mysql> call CreateTempTable('t_forimprove');Query OK, 0 rows affected (0.06 sec)mysql> desc t_forimprove;+----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+-------+| Owner | varchar(256) | YES | | | || CalleeNO | varchar(256) | YES | | | || DateTime | datetime | YES | | NULL | |+----------+--------------+------+-----+---------+-------+3 rows in set (0.06 sec)mysql> DELIMITER $$DROP PROCEDURE IF EXISTS `CreateTempTable`$$CREATE PROCEDURE `CreateTempTable`(in paraName varchar(256))BEGIN set @sql_CreateTempTable=concat('DROP TABLE IF EXISTS ',paraName); PREPARE stmt1 FROM @sql_CreateTempTable; EXECUTE stmt1 ; DEALLOCATE PREPARE stmt1; set @sql_CreateTempTable=concat('CREATE TABLE ',paraName, ' (', 'Owner varchar(256) default \'\',', 'CalleeNO varchar(256) default \'\',', 'DateTime datetime default NULL', ') ENGINE=MyISAM DEFAULT CHARSET=utf8' ); PREPARE stmt1 FROM @sql_CreateTempTable; EXECUTE stmt1 ; DEALLOCATE PREPARE stmt1; END$$DELIMITER ; 数据库查询问题?两张表的,请大神赐教! [求助]怎样把MYSQL数据库的JDBC驱动程序加入到WEBLOGIC SERVER的CLASSPATH red hat 用yum装完mysql和mysql-server 后怎么不能运行啊,而且我还找不到mysqld文件? MySQL 日期格式转换 Mysql初学的问题? SQL查询结果返回重复 MySQL获取当前时间问题... SQLyog不支持储存过程吗? 一个关于日期的问题,和其他帖子决不重复 PSQL COPY性能这么差劲?差了mysql十万八千里?! MYSQL中身份证号怎么确认出生日期和年龄? 能否用mysql 创建ODBC数据表?
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `CreateTempTable`$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE PROCEDURE `CreateTempTable`(in paraName varchar(256))
-> BEGIN
-> set @sql_CreateTempTable=concat('DROP TABLE IF EXISTS ',paraName);
-> PREPARE stmt1 FROM @sql_CreateTempTable;
-> EXECUTE stmt1 ;
-> DEALLOCATE PREPARE stmt1;
->
-> set @sql_CreateTempTable=concat('CREATE TABLE ',paraName, ' (',
-> 'Owner varchar(256) default \'\',',
-> 'CalleeNO varchar(256) default \'\',',
-> 'DateTime datetime default NULL',
-> ') ENGINE=MyISAM DEFAULT CHARSET=utf8'
-> );
-> PREPARE stmt1 FROM @sql_CreateTempTable;
-> EXECUTE stmt1 ;
-> DEALLOCATE PREPARE stmt1;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql> call CreateTempTable('t_forimprove');
Query OK, 0 rows affected (0.06 sec)mysql> desc t_forimprove;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Owner | varchar(256) | YES | | | |
| CalleeNO | varchar(256) | YES | | | |
| DateTime | datetime | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.06 sec)mysql>
BEGIN
set @sql_CreateTempTable=concat('DROP TABLE IF EXISTS ',paraName);
PREPARE stmt1 FROM @sql_CreateTempTable;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
set @sql_CreateTempTable=concat('CREATE TABLE ',paraName, ' (',
'Owner varchar(256) default \'\',',
'CalleeNO varchar(256) default \'\',',
'DateTime datetime default NULL',
') ENGINE=MyISAM DEFAULT CHARSET=utf8'
);
PREPARE stmt1 FROM @sql_CreateTempTable;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1; END$$DELIMITER ;