MySQL杂记2020.10.22

分区表自动增加/删除分区

• 增加分区的存储过程

DELIMITER $$
#该表所在数据库名称
CREATE DEFINER=`root`@`%` 
PROCEDURE `proc_create_paratition`(IN `tableName` VARCHAR(128) CHARSET utf8, 
                                   IN `timeColName` VARCHAR(128) CHARSET utf8)
BEGIN
    DECLARE p_id int;
    DECLARE nextDate date;
    DECLARE lastDate date;
    DECLARE p_name varchar(16);
    
    SELECT COUNT(partition_name) INTO p_id 
    FROM INFORMATION_SCHEMA.partitions
    WHERE TABLE_NAME=tableName;
    
    IF p_id=0 THEN
        
        SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH) INTO nextDate  
        FROM DUAL;
 
        SET p_name=DATE_FORMAT(CURDATE(),'%Y%m');
        SET @v_add=CONCAT('ALTER table ',tableName
                ,' PARTITION by range('
                , timeColName
                ,')(partition '
                , CONCAT('p',p_name)
                ,' values less than '
                , nextDate,'))');
 
   ELSE
 
        SELECT STR_TO_DATE(replace(max(partition_description),'''',''),'%Y-%m-%d') des INTO lastDate 
        FROM INFORMATION_SCHEMA.partitions
        WHERE TABLE_NAME=tableName;
 
        SELECT DATE_ADD(lastDate,INTERVAL 1 MONTH) INTO nextDate 
        FROM DUAL;
 
        SET p_name=DATE_FORMAT(lastDate,'%Y%m');
        SET @v_add=CONCAT('alter table ',tableName
                ,' add partition (partition '
                , CONCAT('p',p_name)
                ,' values less than ("'
                ,nextDate,'"))');
    END IF; 
 
     PREPARE stmt from @v_add;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
END $$
DELIMITER;

• 删除分区的存储过程

DELIMITER $$
--
-- proc_drop_partition 删除数据表中最前面的一个分区。
--
CREATE DEFINER=`root`@`%` 
PROCEDURE `proc_drop_paratition`(IN `tableName` VARCHAR(128) CHARSET utf8)
BEGIN
    DECLARE p_id int;
    DECLARE p_name varchar(16);
 
    SELECT COUNT(partition_name) into p_id 
    FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_NAME=tableName;
    
    IF p_id > 2 THEN 
    
      SELECT PARTITION_NAME into p_name 
      FROM INFORMATION_SCHEMA.PARTITIONS 
      WHERE TABLE_NAME=tableName and TIMESTAMPDIFF(MONTH,replace(partition_description,'''',''), DATE_FORMAT(now(), '%Y-%m-%d')) > 1
      ORDER BY partition_ordinal_position LIMIT 1;
      
      IF p_name is not null THEN 
          SET @v_add=CONCAT('alter table ',tableName,' drop partition ',p_name);
          PREPARE stmt from @v_add;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
       END IF; 
    END IF;   
END $$
DELIMITER;

• 剩下的就是JOB的创建和调用了，这里暂时省略

MySQL查询表碎片

select table_schema,concat('alter table ',table_name, ' engine=Innodb ;'),(data_length+index_length)/1024/1024 length,engine,data_free,table_rows from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema')      and data_free !=0 group by table_name order by table_rows asc;