第一种
SELECT concat('truncate table ',TABLE_NAME,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='buyer';
把生成的sql全部执行即可。
第二种
call test();
创建存储过程,然后调用一下即可,一下是mysql存储过程
DELIMITER $$
USE `buyer`$$
DROP PROCEDURE IF EXISTS `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a VARCHAR(200) DEFAULT '';
DECLARE mycursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='buyer';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN mycursor;
REPEAT
FETCH mycursor INTO a;
IF NOT done THEN
SET @v_sql := CONCAT('truncate table ',a) ;
PREPARE stmt FROM @v_sql;
/*执行此动态sql, 此动态sql的作用, 是从Goods中提取有效数据*/
EXECUTE stmt;
/*释放此资源*/
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE mycursor;
END$$
DELIMITER ;