使用存储过程水平分表

​ 我们经常会遇到一些数据表，数据量过大，比如日志表，每天产生几十万条数据，这样积累下来非常不利于数据库查询和维护，有时候甚至连select count(*) from table a 都查不出来，为避免这种情况产生，通常会按照数据创建时间，来按年按月或者按季度进行分表，原表只保留几个月的数据。如下所示，用存储过程来进行处理。

​ 逻辑 就是 先创建分表，结构和原表相同，然后再插入数据，再删除原表插入历史表的数据。

分表存储过程

CREATE DEFINER=`XXX`@`%` PROCEDURE `up_clear_table_a`()
BEGIN
    -- 自动前30天前的数据到历史表中
	DECLARE v_stat_date DATE;
	DECLARE v_table_name VARCHAR(50);
	DECLARE v_str VARCHAR(2000);
	SET v_stat_date=DATE_SUB(CURRENT_DATE(),INTERVAL 30 DAY);
	SET v_table_name=CONCAT('table_a_',DATE_FORMAT(v_stat_date,'%Y'));
       
	SET v_str=CONCAT('create table if not exists ',v_table_name,'(
         id varchar(40) NOT NULL DEFAULT '''' COMMENT ''id'',
         type tinyint(4) DEFAULT ''0'' COMMENT ''类型'',
         remark varchar(255) DEFAULT '''',
         state tinyint(4) DEFAULT ''0'' COMMENT ''状态'',
         created_at datetime DEFAULT CURRENT_TIMESTAMP COMMENT ''创建时间'',
         created_operator_id varchar(40) DEFAULT '''' COMMENT ''创建人'',
         created_operator_name varchar(40) DEFAULT '''' COMMENT ''创建人名称'',
         PRIMARY KEY (id),
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=''表''');       
	SET @sql=v_str;
	PREPARE stmt FROM @sql; 
	EXECUTE stmt;  
	DEALLOCATE PREPARE stmt;  
      
	SET v_str=CONCAT('insert into ',v_table_name,'
          select * from table_a where created_at>=''',v_stat_date,''' and created_at<date_add(''',v_stat_date,''',interval 1 day)');
	SET @sql=v_str;
	PREPARE stmt FROM @sql; 
	EXECUTE stmt;  
	DEALLOCATE PREPARE stmt;  
          
	DELETE FROM table_a
	WHERE created_at>=v_stat_date AND created_at<DATE_ADD(v_stat_date,INTERVAL 1 DAY);
	
END

解析

建表

• 也可以使用 create TABLE if not exists table_b like table_a;来进行复制表，只复制表结构不复制数据；
• if not exists 是必须要写的，不然报错的话就执行不下去了；
• 单引号 两边都需要加个单引号

PREPARE 预处理语句

PREPARE stmt FROM @sql; 
EXECUTE stmt;  
DEALLOCATE PREPARE stmt; 

上面这三句是固定式语句，第一句式 预定义好动态sql语句 stmt，第二句毫无疑问是执行语句，第三局是删除预定义语句stmt。

DEALLOCATE 英文翻译就是 释放; 解除分配; 释放游标; 删除一个准备好的查询; 归还;