>> 欢迎您 客人: 登录 | 注册 | 在线 | 搜索 | 风格 | 帮助 | 统计 | 文章 返回首页
>>与软件开发有关的知识：操作系统，数据库，网络通信等 书籍支持  卫琴直播  品书摘要  在线测试  资源下载  联系我们
Java技术网站 → 『 Java开发技术 』 → 『 操作系统、网络架构和数据库专题 』 → mysql 表分区、按时间函数分区、删除分区、自动添加表分区
发表一个新主题 开启一个新投票 回复文章 您是本文章第 19492 个阅读者 刷新本主题
 * 贴子主题:  mysql 表分区、按时间函数分区、删除分区、自动添加表分区 回复文章 点赞(0)  收藏  
作者:mary    发表时间：2020-03-23 17:39:49     消息  查看  搜索  好友  邮件  复制  引用
                                                                                                

mysql 表分区、按时间函数分区、删除分区、自动添加表分区

mysql 表分区的几种方式：

  1. RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
  2. LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  3. HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
  4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
常用的按时间分区可以按照年、月、日等条件用来分区，因为是按时间条件分区所以选用RANGE分区方式。

分区的切条件判断：

  1. LESS THAN：如果是数值就是小于等于，时间则是小于。
  2. LESS THAN MAX:不等于。
  3. IN:包含于某某区间。

按实际分区的时间函数选用：

  1. 按日分区切分时的条件可以用day()、to_days()时间函数，需要注意的是必须是返回值是整形的。
  2. 按月分区采用month()时间函数;
  3. 其他条件分区时间函数可到我关于时间函数的博文查看：https://blog.csdn.net/qq_36216193/article/details/90404527
其中按时间字段create_date按天分区，首先需要把date字段改成主键，后添加分区切分规则    

ALTER TABLE js_sys_log
DROP PRIMARY KEY,
ADD PRIMARY KEY (id, create_date);  --之前id是主键，所以这里第2主键为联合主键

ALTER TABLE  js_sys_log  ADD PRIMARY KEY (create_date);

为了便于管理分区名通常以“p时间”来命名。    

示例一：
    ALTER TABLE  js_sys_log  PARTITION BY RANGE (to_days(create_date)) (
        PARTITION  p20181101  VALUES LESS THAN (to_days('20181101')),
        PARTITION  p20181102  VALUES LESS THAN (to_days('20181102')),
        PARTITION  p20181103  VALUES LESS THAN (to_days('20181103')),
        PARTITION  p20181104  VALUES LESS THAN (to_days('20181104')),
        PARTITION  p20181105  VALUES LESS THAN (to_days('20181105')),
        PARTITION  p20181106  VALUES LESS THAN (to_days('20181106')),
        PARTITION  p20181107  VALUES LESS THAN (to_days('20181107')),
        PARTITION  p20181108  VALUES LESS THAN (to_days('20181108')),
        PARTITION  p20181109  VALUES LESS THAN (to_days('20181109')),
        PARTITION  p20181110  VALUES LESS THAN (to_days('20181110'))，
        PARTITION  p_other  VALUES LESS THAN MAXVALUE );
    );

后期添加分区：

示例二：
    ALTER TABLE  js_sys_log  ADD PARTITION (PARTITION  p20181111 VALUES LESS THAN (TO_DAYS ('2018-11-11')));

删除分区：

示例三：
    ALTER TABLE  js_sys_log  DROP PARTITION p20181101;

查询MySQL的系统字典库得知所有的分区详情信息

示例四：
    SELECT  *  FROM  information_schema. PARTITIONS  t  WHERE t.PARTITION_NAME IS NOT NULL

在分区创建后可以通过过程和事件控制自动增加表分区。

1、查看是否开启事件    

show variables like "event_scheduler";

点击在新窗口中浏览原图 CTRL+鼠标滚轮放大或缩小

2、开启事件（本次设置中生效）    

SET GLOBAL event_scheduler = on;    
或
SET GLOBAL event_scheduler = 1;

3、开启时间（长期生效）

编辑 mysql 配置文件，并在【mysqld】下添加该变量：

点击在新窗口中浏览原图 CTRL+鼠标滚轮放大或缩小

再重启 mysqld 服务， 登录 mysql ，查看配置，确认是否生效。

     4、设置分区脚本    

use test;

DELIMITER ||
-- 删除存储过程
drop procedure if exists 事件名称 ||
-- 注意：使用该存储过程必须保证相应数据库表中至少有一个手动分区
-- 创建存储过程[通过数据库名和对应表名]-建多少个分区，分区时间间隔为多少
-- databasename：创建分区的数据库
-- tablename：创建分区的表的名称
-- partition_number：一次创建多少个分区
-- partitiontype：分区类型[0按天分区，1按月分区，2按年分区]
-- gaps：分区间隔，如果分区类型为0则表示每个分区的间隔为 gaps天；
-- 如果分区类型为1则表示每个分区的间隔为 gaps月
-- 如果分区类型为2则表示每个分区的间隔为 gaps年
create procedure 事件名称 (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)
L_END:
begin    
    declare max_partition_description varchar(255) default '';
    declare p_name varchar(255) default 0;      
    declare p_description varchar(255) default 0;  
    declare isexist_partition varchar(255) default 0;
declare i int default 1;
  
-- 查看对应数据库对应表是否已经有手动分区[自动分区前提是必须有手动分区]
    select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename  and table_name = tablename limit 1;
    -- 如果不存在则打印错误并退出存储过程
    if isexist_partition <=> "" then
       select "partition table not is exist" as "ERROR";
       leave L_END;
    end if;

    -- 获取最大[降序获取]的分区描述[值]
    select partition_description into max_partition_description  from information_schema.partitions where table_schema = databasename  and table_name = tablename order by partition_description desc limit 1;
  
    -- 如果最大分区没有,说明没有手动分区,则无法创建自动分区
    if max_partition_description <=> "" then
       select "partition table is error" as "ERROR";
       leave L_END;
    end if;

    -- 替换前后的单引号[''两个引号表示一个单引号的转义]
    -- set max_partition_description = REPLACE(max_partition_description, '''', '');
    -- 或使用如下语句
    set max_partition_description = REPLACE(max_partition_description-1, '\'', '');

    -- 自动创建number个分区
    while (i <= partition_number) do
                 if (partitiontype = 0) then
                     -- 每个分区按天递增,递增gaps天
                     set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day);
                 elseif (partitiontype = 1) then
                     -- 每个分区按月递增,递增gaps月
                     set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month);
                 else
                     -- 每个分区按年递增,递增gaps年
                     set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);
                 end if;
                 -- 删除空格
                 set p_name = REPLACE(p_description, ' ', '');
                 -- 例如10.20的记录实际是less than 10.21
                 set p_description = DATE_ADD(p_description, interval 1 day);
                 -- 如果有横杆替换为空
          set p_name = REPLACE(p_name, '-', '');
                 -- 删除时间冒号
                 set p_name = REPLACE(p_name, ':', '');
                 -- alter table tablename add partition ( partition pname values less than ('2019-05-22 23:59:59') );
          set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
                 -- set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
                 -- 打印sql变量
          -- select @sql;
                 -- 准备sql语句
          PREPARE stmt from @sql;
                 -- 执行sql语句
          EXECUTE stmt;
                 -- 释放资源
          DEALLOCATE PREPARE stmt;
                 -- 递增变量
          set i = (i + 1) ;

    end while;          
end ||
-- 恢复语句中断符
DELIMITER ;

5、事件处理（每天执行一次）    

DELIMITER ||
drop event if exists 事件名称 ||
create event 事件名称
on schedule every 1 day
starts '2019-05-22 23:59:59'
do
BEGIN
    call 事件名称 ('数据库名', '分区表名', 1, 0, 1);
END ||
DELIMITER ;

6、删除事件    

DROP EVENT [IF EXISTS] 事件名称;

7、查看事件    

show events;

  8、表分区查看：    

explain partitions select * from 分区表名;

----------------------------
原文链接:https://blog.csdn.net/qq_36216193/article/details/90412637

程序猿的技术大观园：www.javathinker.net



[这个贴子最后由 flybird 在 2020-03-28 12:07:49 重新编辑]
Java精品书籍推荐
Java最新视频教程推荐
  Java面向对象编程-->多线程（上）
  JavaWeb开发-->自定义JSP标签(Ⅱ)
  JSP与Hibernate开发-->立即检索和延迟检索策略
  Java网络编程-->用Axis发布Web服务
  精通Spring-->绑定表单
  Vue3开发-->Vue Router路由管理器
本站其他相关文章
  如何成为写SQL高手
  神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程
  SaltStack 自动化运维实战
  MySQL 导入数据
  MySQL 复制表
  MySQL 索引 的创建和删除
  SQL Server 和 MySQL 中的 Date 函数
  SQL的创建表（CREATE TABLE） 语句的用法
  比较XML的子元素和属性
  MySQL:如何用 SELECT 语句的 WHERE 子句过滤返回的数据
  深入浅出一致性Hash原理
  mysql主从复制的步骤
  MySQL的实现定时执行任务的存储过程
  分布式架构的那点事
  为什么要分库分表？用过哪些分库分表中间件？
  更多...
 IPIP: 已设置保密
楼主      
共1页 0条记录 当前第1页
发表一个新主题 开启一个新投票 回复文章

中文版权所有： JavaThinker技术网站 Copyright 2016-2026 沪ICP备16029593号-2
荟萃Java程序员智慧的结晶，分享交流Java前沿技术。  联系我们
如有技术文章涉及侵权，请与本站管理员联系。