MySQL

1.Mysql存储过程及定时事件

由于当前为实时表增量增长，增长到一定容量后，应用就会断掉。当前代码层面有delelte指定天数的逻辑；也有定时事件和存储过程来删除数据，但查了一下，delete不会释放空间(但行占用 空间可复用)，需要手动optimize，不过optimize操作也会锁表,如下所示：

1、drop table table_name 立刻释放磁盘空间 ，不管是 InnoDB和MyISAM

2、truncate table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM 。
      truncate table其实有点类似于drop table 然后create。只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等，就不需要重新再搞一把。所以速度上应该是接近drop table的速度。
3、对于delete from table_name  删除表的全部数据
      对于MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）；     InnoDB 不会释放磁盘空间

4、对于delete from table_name where xxx带条件的删除

     不管是innodb还是MyISAM都不会释放磁盘空间。

5、delete操作以后 使用optimize table table_name 会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。
      所以要想达到清理数据的目的，请delete以后执行optimize table 操作。

想来想去，下周要休假回大东北，不改代码了，不然还得测试，想先搞个存储过程来应对一下：
思路：（1）把当前表按月分表并重命名为xxxx_202104_ssss （2） 新建一个当前表继续当前业务操作(3) 删除N月前的历史表
并加一个定时的事件去调用ps，于是就有了下面的内容供大家参考

CREATE DEFINER=`root`@`%` PROCEDURE `create_table_t_source_file2_month`()
BEGIN
#历史表名后缀，格式为202004
DECLARE `@suffix` VARCHAR(15);
#创建新表的变量
DECLARE `@sqlstrcreate` VARCHAR(2560);
#对当前业务表进行重命名
DECLARE `@sqlstrupdate` VARCHAR(2560);
#待删除历史表的后缀 eg:202101
DECLARE `@delsuffix` VARCHAR(15);
#删除历史表的SQL语句变量
DECLARE `@sqlstrdel` VARCHAR(2560);

SET `@suffix` = DATE_FORMAT(NOW(),'%Y%m');

SET `@delsuffix` = DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL-3 MONTH) ,"%Y%m");
 
SET @sqlstrupdate = CONCAT(
"alter table t_source_file2 rename to t_source_file2_",
@suffix
);
 
SET @sqlstrcreate = CONCAT(
"CREATE TABLE `t_source_file2` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `FRONT_ID` int(11) DEFAULT NULL,
  `LAST_SEND_TIME` datetime DEFAULT NULL,
  `KAFKA_SUCC_TIME` datetime DEFAULT NULL,
  `SERVER_SUCC_TIME` datetime DEFAULT NULL,
  `SEND_STATUS` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `Index_2` (`KAFKA_SUCC_TIME`,`SEND_STATUS`) USING BTREE,
  KEY `Index_1` (`INIT_TIME`,`SEND_STATUS`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT
"
);
SET @sqlstrdel = CONCAT(
"drop table t_source_file2_",
@delsuffix, " purge"
);
PREPARE tsfupdate FROM @sqlstrupdate;
EXECUTE tsfupdate;
PREPARE tsfcreate FROM @sqlstrcreate;
EXECUTE tsfcreate;
PREPARE tsfdel FROM @sqlstrdel;
EXECUTE tsfdel;
END

以上为PS,并加一个事件如下所示：

DELIMITER $$
SET GLOBAL event_scheduler = 1;
CREATE EVENT event_create_table_every_month
ON SCHEDULE EVERY 1 MONTH
STARTS '2021-05-01 00:00:00'
ON  COMPLETION  PRESERVE
ENABLE
DO
BEGIN
CALL create_table_t_source_file2_month();
END

就这些，结束…

批量删除表且带查询条件

误导入了好几千张历史表结构，表名类似如下：zhanglu_202104，有日期的都为历史表，需要干掉，想着表名匹配
，批全干掉，查了半天没有一步完成的，那就分批来吧，第一步拼好删除语句，第二步批量执行语句

(1) 按条件查询所有待删除表并且拼好删除语句

select 'drop table '||table_name||' purge'||';' from user_tables where table_name like '%2021%';

执行完上述语句如下图所示：

（2）把所有语句复掉到Excel里，删除第一列行号，然后把语句再复制到SQL窗口，执行吧，自己跑吧~~
但要

• 注意：drop会锁表，可能还会导致应用报错，不过给大家提供一个思路，可根据实际情况自行解决

最近很久没更了，积攒了好多东西想整理下，结果太懒，导致没能及时整理又忘记了要写啥了。。。。。