>> 欢迎您 客人: 登录 | 注册 | 在线 | 搜索 | 风格 | 帮助 | 统计 | 文章 返回首页
>>与软件开发有关的知识：操作系统，数据库，网络通信等 书籍支持  卫琴直播  品书摘要  在线测试  资源下载  联系我们
Java技术网站 → 『 Java开发技术 』 → 『 操作系统、网络架构和数据库专题 』 → mysql 表分区、按时间函数分区、删除分区、自动添加表分区
发表一个新主题 开启一个新投票 回复文章 您是本文章第 28605 个阅读者 刷新本主题
 * 贴子主题:  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面向对象编程-->Swing组件（下）
  JavaWeb开发-->Servlet技术详解（Ⅲ）
  JSP与Hibernate开发-->映射一对多关联关系
  Java网络编程-->Socket用法详解
  精通Spring-->
  Vue3开发-->组合（Composition）API
本站其他相关文章
  一文读懂非关系型数据库（NoSQL）
  Windows下安装MySQL8 的步骤
  解决mysql问题：The server quit without updating PID file
  DNS原理及其解析过程
  MySQL——mmm(Master-Master replication managerfor Mysql)...
  Mysql数据库的使用总结之ERROR 1146 (42S02)
  MySQL 运算符的用法
  MySQL 序列使用
  MySQL 删除数据库
  SQL ALTER TABLE 语句
  SQL DEFAULT 约束
  SQL FOREIGN KEY 约束
  SQL PRIMARY KEY 约束
  深入浅出一致性Hash原理
  MySQL的实现定时执行任务的存储过程
  更多...
 IPIP: 已设置保密
楼主      
共1页 0条记录 当前第1页
发表一个新主题 开启一个新投票 回复文章

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