mysql存储过程，清除出现一个小时之前的数据(truncate table)

1.建表和插入数据

DROP TABLE IF EXISTS `tt`;
CREATE TABLE `tt` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `datetime` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tt
-- ----------------------------
INSERT INTO `tt` VALUES ('1', 'z', 'p202009162000');
INSERT INTO `tt` VALUES ('2', 'y', 'p202009162100');
INSERT INTO `tt` VALUES ('3', 'f', 'p202009162200');
INSERT INTO `tt` VALUES ('4', 'g', 'p202009171000');
INSERT INTO `tt` VALUES ('5', 'h', 'p202009172200');

2.存储过程

delimiter  $$
create procedure drop_all(t_name varchar(200))
BEGIN
		DECLARE cnt_model INT;
		set @result = CONCAT("select count(1) from ",t_name," where right(date_format(date_sub(now(), interval 1 hour), '%Y%m%d%H%i'),12)>=right(datetime,12)","INTO @t_count");
		prepare stmt FROM @result;
		EXECUTE stmt;
		set cnt_model =@t_count;
		if(cnt_model>=1)then 
			set @d_name = CONCAT("truncate table ",t_name);
			prepare stmt1 FROM @d_name;
			EXECUTE stmt1;
		end if;
END $$
delimiter ;

3.调用存储过程

call drop_all('tt');//动态传入表名称

4.删除存储过程

drop PROCEDURE drop_all;

5.查询当前数据库中的所有存储过程

select `name` from MySQL.proc where db = 'databaseName' and `type` = 'PROCEDURE';