mysql 存储 过程执行动态语句

yiguangtia 发布于 2015/08/06 09:17
阅读 126
收藏 0
DELIMITER // 
CREATE
  PROCEDURE dynamic_table(IN id int)
  BEGIN
     DECLARE table_name VARCHAR(20); 
     DECLARE sql_text VARCHAR(2000); 
     SET table_name=CONCAT("dish_",id);
     SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
  `dish_id` int(11) NOT NULL AUTO_INCREMENT,
  `dish_name` varchar(50) NOT NULL,
  `dish_restaurant_id` int(11) NOT NULL,
  `dish_price` float NOT NULL,
  `dish_number` int(11) NOT NULL,
  `dish_type` varchar(10) NOT NULL,
  `dish_image` varchar(300) DEFAULT NULL,
  `dish_discription` varchar(100) DEFAULT NULL,
  `dish_saled_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`dish_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;' );
      SELECT sql_text; 
      SET @sql_text=sql_text;
      PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;  
  END//
      
DELIMITER ;


show procedure status;
drop trigger tg1;
CREATE TRIGGER tg1 AFTER insert ON restaurant FOR EACH ROW 

CALL dynamic_table(NEW.restaurant_id);

执行过程总出现:Dynamic SQL is not allowed in stored function or trigger ?

加载中
返回顶部
顶部