maysql 按月建立分表

最新推荐文章于 2024-05-07 14:54:09 发布

ly_zszcyx

最新推荐文章于 2024-05-07 14:54:09 发布

阅读量855

点赞数

分类专栏： sql学习

本文链接：https://blog.csdn.net/qq_41722795/article/details/116268880

版权

mysql

sql学习专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Mysql 建立水平分表

项目需求，按月建立水平分表

前言

主表数据量过大，需要建立分表，提交数据查询速度

提示：以下是本篇文章正文内容，下面案例可供参考

一、使用工具

Navicat Premium

二、创建分表过程

1. 创建需要的主表

CREATE TABLE `biao_ming` (
   `id` varchar(36) NOT NULL,
   `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
   `create_time` datetime DEFAULT NULL COMMENT '创建日期',
   `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
   `update_time` datetime DEFAULT NULL COMMENT '更新日期',
   `sys_org_code` varchar(64) DEFAULT NULL COMMENT '所属部门',
   `pack_code` varchar(32) DEFAULT NULL COMMENT '停车场编号',
   `plate_no` varchar(32) DEFAULT NULL COMMENT '车牌号',
   `card_no` varchar(32) DEFAULT NULL COMMENT '卡号',
   `card_type` int DEFAULT NULL COMMENT '卡片类型',
   `cap_time` datetime DEFAULT NULL COMMENT '进出时间',
   `cap_place` varchar(32) DEFAULT NULL COMMENT '进出地点',
   `cap_flag` int DEFAULT NULL COMMENT '进出标志',
   `img_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图片',
   `cap_remark` varchar(32) DEFAULT NULL COMMENT '备注',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='进出明细';

如果已经有主表则不需要该步骤。

2.建立创建分表函数

分表表名：主表表名+日期，例如：biao_ming_202104

建立分表方式一：建议使用该方式创建

SUBSTR() 函数：截取字符串

concat(str1, str2) 拼接字符串；

Prepare ：

Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。

Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击。

Prepare 语法：

PREPARE statement_name FROM sql_text /*定义*/

EXECUTE statement_name [USING variable [,variable...]] /*执行预处理语句*/

DEALLOCATE PREPARE statement_name /*删除定义*/

delimiter //
DROP PROCEDURE IF EXISTS create_month_table //
create procedure create_month_table()
begin
    DECLARE table_prefix varchar(20);
    DECLARE table_suffix varchar(20);
    DECLARE tableName varchar(50); 

set @table_prefix = 'biao_ming_';
set @table_suffix =  SUBSTR(replace(replace(now(), '-', ''),' ', ''), 1, 6);
set @tableName =  CONCAT(@table_prefix, @table_suffix);
set @sqlstr = concat("CREATE TABLE if not exists ", @tableName," like biao_ming");
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

end //
delimiter;

建立分表方式二：

使用此方式建立分表，建议不要建立主键；

delimiter //
DROP PROCEDURE IF EXISTS create_hour_table //
create procedure create_hour_table()
begin
    DECLARE table_prefix varchar(20);
    DECLARE table_suffix varchar(20);
    DECLARE tableName varchar(50); 

set @table_prefix = 'biao_ming_';
set @table_suffix =  SUBSTR(replace(replace(now(), '-', ''),' ', ''), 1, 10);
set @tableName =  CONCAT(@table_prefix, @table_suffix);
set @sqlstr = concat("CREATE TABLE if not exists ", @tableName," (
  `id` varchar(36) NOT NULL,
   `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
   `create_time` datetime DEFAULT NULL COMMENT '创建日期',
   `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
   `update_time` datetime DEFAULT NULL COMMENT '更新日期',
   `sys_org_code` varchar(64) DEFAULT NULL COMMENT '所属部门',
   `pack_code` varchar(32) DEFAULT NULL COMMENT '停车场编号',
   `plate_no` varchar(32) DEFAULT NULL COMMENT '车牌号',
   `card_no` varchar(32) DEFAULT NULL COMMENT '卡号',
   `card_type` int DEFAULT NULL COMMENT '卡片类型',
   `cap_time` datetime DEFAULT NULL COMMENT '进出时间',
   `cap_place` varchar(32) DEFAULT NULL COMMENT '进出地点',
   `cap_flag` int DEFAULT NULL COMMENT '进出标志',
   `img_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图片',
   `cap_remark` varchar(32) DEFAULT NULL COMMENT '备注',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='进出明细';");
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

end //
delimiter;

创建后可以函数中查看：

3.创建建立分表事件（定时器）

创建成功后可在事件中查看：

delimiter //
CREATE EVENT e_create_month_table
ON SCHEDULE EVERY 1 MONTH STARTS TIMESTAMP '2021-04-26 02:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
CALL create_month_table();
END//
delimiter;

CREATE EVENT 语句来创建事件，其语法格式如下：

CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

ON SCHEDULE schedule：必选，用于定义执行的时间和时间间隔，schedule 表示触发点。

ON COMPLETION [NOT] PRESERVE：可选；用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE。

ENABLE | DISABLE | DISABLE ON SLAVE：可选，用于指定事件的一种属性。其中，关键字 ENABLE 表示该事件是活动的，即调度器检查事件是否必须调用；关键字 DISABLE 表示该事件是关闭的，即事件的声明存储到目录中，但是调度器不会检查它是否应该调用；关键字 DISABLE ON SLAVE 表示事件在从机中是关闭的。

COMMENT 'comment'：可选，用于定义事件的注释。

DO event_body：必选，用于指定事件启动时所要执行的代码，可以是任何有效的 SQL 语句、存储过程或者一个计划执行的事件。如果包含多条语句，则可以使用 BEGIN..END 复合结构。

ON SCHEDULE 子句中，参数 schedule 的值为一个 AT 子句，用于指定事件在某个时刻发生，其语法格式如下：

AT timestamp [+ INTERVAL interval]...
    | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp[+ INTERVAL interval]...]

timestamp：一般用于只执行一次，表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。
EVERY 子句：用于事件在指定时间区间内每隔多长时间发生一次，其中 STARTS 子句用于指定开始时间；ENDS 子句用于指定结束时间。
interval：一般用于周期性执行，表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示 4 周，使用“'1:10'HOUR_MINUTE”表示 1 小时 10 分钟。间隔的长短用 DATE_ADD() 函数支配。

interval 参数可以是以下值：

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
    WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
    DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

4.建立存储过程

将主表的数据转存到分表中，并删除

delimiter //
create procedure p_dr_stats_month()
begin 
    declare v_day int;
    declare v_yesterday date;
    declare table_name VARCHAR(50);
    DECLARE table_prefix varchar(20);
    DECLARE table_suffix varchar(20);

    
set @table_prefix = 'biao_ming_';
set @table_suffix =  SUBSTR(replace(replace(now(), '-', ''),' ', ''), 1, 6);
set @table_name =  CONCAT(@table_prefix, @table_suffix);
set @table_suffix_l = DATE_FORMAT(date_add(CURDATE(), interval -1 month),'%Y%m');
set @table_name_l = CONCAT(@table_prefix,@table_suffix_l);


set v_day = DAY(curdate());
set @v_yesterday = date_sub(CURDATE(), interval 1 day);
set @last_month = date_add(curdate()-day(curdate())+1,interval -1 month);
set @curr_day = date_add(curdate(), interval - day(curdate()) + 1 day);


if v_day = 28 THEN

   set @conditions = concat( " t.create_time >= '", @last_month,"' and t.create_time < '" , CURDATE() , "';");
   set @sqlsebstr = concat(@table_name_l, " select * from biao_ming t where ", @conditions);
   set @sqlstr = 	 concat("insert into ", @sqlsebstr);
   PREPARE stmt FROM @sqlstr;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   set @delsqlstr = concat("DELETE FROM biao_ming t WHERE ", @conditions);
   PREPARE stmt1 FROM @delsqlstr;
   EXECUTE stmt1;
   DEALLOCATE PREPARE stmt1;
else
   set @conditions = concat( " t.create_time >= '", @v_yesterday,"' and t.create_time < '" , CURDATE() , "';");      
   set @sqlsebstr = concat(@table_name, " select * from biao_ming t where ", @conditions);
   set @sqlstr = 	 concat("insert into ", @sqlsebstr);
   PREPARE stmt FROM @sqlstr;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
end if;
end //
delimiter;

5. 常见错误：

表示该函数已经存在，可在函数或事件中删除：

ly_zszcyx

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
maysql 按月建立分表

Mysql 建立水平分表项目需求，按月建立水平分表前言主表数据量过大，需要建立分表，提交数据查询速度提示：以下是本篇文章正文内容，下面案例可供参考一、使用工具Navicat Premium二、创建分表过程1. 创建需要的主表CREATE TABLE `biao_ming` ( `id` varchar(36) NOT NULL, `create_by` varchar(50) DEFAULT NULL COMMENT '创建人', `crea.
复制链接

扫一扫

专栏目录