首页 / MYSQL / mysql动态新建以及删除分区表_MySQL

mysql动态新建以及删除分区表_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql动态新建以及删除分区表_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9002字，纯文字阅读大概需要13分钟。

内容图文

bitsCN.com

mysql动态新建以及删除分区表

因为项目需要，最近研究了一下在mysql数据库下如何动态新建以及删除分区表。如果全部借助存储过程的话，新建以及删除分区表在逻辑上比较死板、不灵活，而且还容易出错。因此，我新建了一个数据表table_fen_qu，借助这个表可以很(相对)灵活的对分区表进行管理。以下是操作过程，不足之处请各位看官指正。

第一步：建立存储过程。

建立新建分区表的存储过程代码如下：

001

drop procedure if exists general_procedure;

002

-- general_procedure的作用：新建分区表及在table_fen_qu表中存储新建分区表时的相关参数

003

-- general_procedure的参数：表名，分区表之间的时间间隔(单位为小时)，要新增的分区表个数

004

create procedure general_procedure(in tablenamein varchar(50),in intervalHour int,in newIntervalNum int)

005

general_pro:begin

006

-- 参数：最大时间

007

declare maxMonitTime datetime default SYSDATE();

008

-- 参数：最大时间对应的字符串

009

declare maxMonitTimeStr varchar(50);

010

-- 参数：最小时间

011

declare minMonitTime datetime default SYSDATE();

012

-- 参数：最大时间对应的字符串

013

declare minMonitTimeStr varchar(50);

014

-- 参数：数据库记录数

015

declare recoidNum int default 0;

016

-- 判断传入的表名是否为空

017

if tablenamein is null then

018

leave general_pro;

019

end if;

020

-- 判断传入的时间间隔

021

if intervalHour <= 0 then

022

set intervalHour = 6;

023

end if;

024

-- 判断新增分区表个数

025

if newIntervalNum <= 0 then

026

set newIntervalNum = 1;

027

end if;

028

029

-- 在该表中，查询符合条件的记录数，backupflag=0说明是未备份

030

select count(*) into recoidNum from table_fen_qu where tablename=tablenamein and backupflag=0;

031

if recoidNum > 0 then

032

-- 查询该表在table_fen_qu表中的最大监测时间

033

select monittime into maxMonitTime from table_fen_qu where tablename=tablenamein and backupflag=0 order by monittime desc limit 1;

034

-- 判断监测时间是否为null

035

if maxMonitTime is null then

036

set maxMonitTime = SYSDATE();

037

end if;

038

-- 比较最大时间减去72个小时之后的时间与系统时间的早晚

039

set recoidNum = timestampdiff(hour,SYSDATE(),DATE_SUB(maxMonitTime,INTERVAL 3 DAY));

040

-- 如果recoidNum大于0，说明最大监测时间减去72小时之后的时间仍然在系统时间之后,

041

-- 说明不用建立新的分区表，反之，则建立最大监测时间之后newIntervalNum个以每intervalHour小时为间隔的分区表

042

if recoidNum <= 0 then

043

set recoidNum = 1;

044

while recoidNum <= newIntervalNum do

045

set maxMonitTime = ADDDATE(maxMonitTime,INTERVAL intervalHour HOUR);

046

set maxMonitTimeStr = CONCAT('p',DATE_FORMAT(maxMonitTime,"%Y%m%d%H%i%s"));

047

-- 开始添加分区表

048

/*拼接分区表代码段*/

049

set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' ADD PARTITION (PARTITION ',maxMonitTimeStr,' VALUES LESS THAN (/'',maxMonitTime,'/') ENGINE = InnoDB )');

050

/*定义预处理语句*/

051

prepare stmt from @v_add_s;

052

/*执行预处理语句*/

053

execute stmt;

054

/*释放预处理语句*/

055

deallocate prepare stmt;

056

-- 开始在table_fen_qu中添加记录

057

insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(maxMonitTimeStr,tablenamein,maxMonitTime,0);

058

-- 记录数加1

059

set recoidNum = recoidNum + 1;

060

end while;

061

end if;

062

else

063

set recoidNum = 1;

064

-- 计算最小时间

065

set minMonitTimeStr = CONCAT(DATE_FORMAT(DATE_SUB(maxMonitTime,INTERVAL 60 DAY),'%Y-%m-%d'),' 00:00:00');

066

set minMonitTime = STR_TO_DATE(minMonitTimeStr,'%Y-%m-%d %H:%i:%s');

067

-- 计算最大时间

068

set maxMonitTimeStr = CONCAT(DATE_FORMAT(ADDDATE(maxMonitTime,INTERVAL 4 DAY),'%Y-%m-%d'),' 00:00:00');

069

set maxMonitTime = STR_TO_DATE(maxMonitTimeStr,'%Y-%m-%d %H:%i:%s');

070

-- 计算新建表分区个数

071

set newIntervalNum = floor(timestampdiff(hour,minMonitTime,maxMonitTime) / intervalHour) + 1;

072

if newIntervalNum < 10 then

073

set newIntervalNum = 10;

074

end if;

075

-- 删除所有表分区

076

set @v_del_s = CONCAT('ALTER TABLE ',tablenamein,' remove partitioning');

077

/*定义预处理语句*/

078

prepare stmt from @v_del_s;

079

/*执行预处理语句*/

080

execute stmt;

081

/*释放预处理语句*/

082

deallocate prepare stmt;

083

-- 删除所有数据

084

delete from table_fen_qu where tablename= tablenamein;

085

-- 新建分区

086

while recoidNum <= newIntervalNum do

087

set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));

088

-- 开始添加分区表

089

/*拼接分区表代码段*/

090

if recoidNum = 1 then

091

set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' PARTITION BY RANGE COLUMNS(moint_time) (PARTITION ',minMonitTimeStr,' VALUES LESS THAN (/'',minMonitTime,'/') ENGINE = InnoDB )');

092

else

093

set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' ADD PARTITION (PARTITION ',minMonitTimeStr,' VALUES LESS THAN (/'',minMonitTime,'/') ENGINE = InnoDB )');

094

end if;

095

/*定义预处理语句*/

096

prepare stmt from @v_add_s;

097

/*执行预处理语句*/

098

execute stmt;

099

/*释放预处理语句*/

100

deallocate prepare stmt;

101

-- 开始在table_fen_qu中添加记录

102

insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);

103

-- 记录数加1

104

set recoidNum = recoidNum + 1;

105

set minMonitTime = ADDDATE(minMonitTime,INTERVAL intervalHour HOUR);

106

end while;

107

end if;

108

end general_pro;

删除分区表的存储过程如下：

drop procedure if exists del_fenqu;

-- 删除'temp_data','no_energy_five_minute_data','energy_five_minute_data'及'energy_five_minute_data_summarize'共4个表中已备份的分区表

create procedure del_fenqu()

del_fq:begin

-- 参数：记录id

declare myrecid int;

-- 参数：分区表名称

declare myfenquname varchar(50);

-- 参数：表分区所在表的表名称

declare mytablename varchar(50);

-- 参数：数据库记录数

declare recoidNum int default 0;

-- 在该表中，查询符合条件的记录数，backupflag=1说明是已备份

select count(*) into recoidNum from table_fen_qu where tablename in ('temp_data','no_energy_five_minute_data','energy_five_minute_data','energy_five_minute_data_summarize') and backupflag=1;

-- 存在符合条件的记录则全部删除

while recoidNum > 0 do

-- 查询出一条

select recid,fenquname,tablename into myrecid,myfenquname,mytablename from table_fen_qu where tablename in ('temp_data','no_energy_five_minute_data','energy_five_minute_data','energy_five_minute_data_summarize') and backupflag=1 order by monittime desc limit 1;

-- 删除数据记录

delete from table_fen_qu WHERE recid = myrecid;

-- 删除表分区

/*拼接分区表代码段*/

set @v_drop_d=CONCAT('ALTER TABLE ',mytablename,' DROP PARTITION ',myfenquname);

/*定义预处理语句*/

prepare stmt from @v_drop_d;

/*执行预处理语句*/

execute stmt;

/*释放预处理语句*/

deallocate prepare stmt;

-- 在该表中，查询符合条件的记录数，backupflag=1说明是已备份

select count(*) into recoidNum from table_fen_qu where tablename in ('temp_data','no_energy_five_minute_data','energy_five_minute_data','energy_five_minute_data_summarize') and backupflag=1;

end while;

end del_fq;

第二步：建立事件计划，定时执行事件。

事件如下：

-- 打开事件计划

SET GLOBAL event_scheduler = ON;

/*创建从开始时间每隔1天定时执行*/

drop event if exists eachDayEvent;

DELIMITER ||

create event eachDayEvent

on schedule every 1 day starts '2013-05-01 00:00:00'

on completion preserve enable

begin

-- general_procedure的参数：表名，分区表之间的时间间隔(单位为小时)，要新增的分区表个数

-- 非能耗5分钟表-间隔6小时-6h/分区表

call general_procedure('no_energy_five_minute_data',6,8);

-- 原始数据表-间隔6小时-6h/分区表

call general_procedure('temp_data',6,8);

-- 能耗五分钟-间隔天-24h/分区表

call general_procedure('energy_five_minute_data',24,4);

-- 能耗五分钟汇总-间隔天-24h/分区表

call general_procedure('energy_five_minute_data_summarize',24,4);

-- 能耗小时表调用-间隔周-7*24h/分区表

call general_procedure('energy_hour_data_summarize',168,4);

-- 能耗分类分项5分钟表-间隔周7*24h/分区表

call general_procedure('energy_item_five_minute_data',168,4);

-- 能耗分类分项小时表-间隔季度-90*24h/分区表

call general_procedure('energy_item_hour_data',2160,4);

-- 能耗天汇总表-间隔半年-4380h/分区表

call general_procedure('energy_day_data_summarize',4380,4);

-- 删除已备份的分区表

call del_fenqu();

end ||

DELIMITER ;

bitsCN.com

内容总结

以上是互联网集市为您收集整理的mysql动态新建以及删除分区表_MySQL全部内容，希望文章能够帮你解决mysql动态新建以及删除分区表_MySQL所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/574375.html

来源：【匿名】

【上一篇】mysql5.6onlineddl测试_MySQL 【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【mysql动态新建以及删除分区表_MySQL】教程文章相关的互联网学习教程文章

MySQL分区表例子——List分区

列表分区(List分区)这里假设表中有一个sale_item_type 字段，数据类型为INT 型当sale_item_type 为1，3，5的时候，作为一个分区当sale_item_type 为2，4，6的时候，作为另一个分区当sale_item_type 为7，8的时候，作为最后一个分区初期分区定义mysql> CREATE TABLE sale_data2 ( -> sale_date DATETIME NOT NULL, -> sale_item_type INT NOT NULL , -> sale_money DECIMAL(10,2) NOT NULL -> ) -> PART...

MySQL5.7传输表空间——迁移分区表

Mysql 传输表空间--将InnoDB分区表复制到另一个实例（二）实验环境：（都是mysql5.7）源库：192.168.2.200 mysql5.7.16 zhangdb下的emp_2分区表的目标库：192.168.2.100 mysql5.7.18 test下（将zhangdb的emp表，导入到目标库的test schema下）--：在源数据库中创建测试分区表emp_2，然后导入数据MySQL [zhangdb]> CREATE TABLE emp_2(id BIGINT unsigned NOT NULL AUTO_INCREMENT,x VARCHAR(500) NOT NULL,y ...

Mysql5.6—分区表及独享表空间

1、先停止Mysql [root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL.[确定] 2、修改 /etc/my.cnf配置文件 [root@localhost ~]# vi /etc/my.cnf [mysqld] innodb_file_per_table = 1 3、启动Mysql [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL..[确定] 4、连接到Mysql数据库 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end wit...

详解MySQL分区表【代码】【图】

当数据库数据量涨到一定数量时，性能就成为我们不能不关注的问题，如何优化呢？常用的方式不外乎那么几种：　　1、分表，即把一个很大的表达数据分到几个表中，这样每个表数据都不多。　　　　优点：提高并发量，减小锁的粒度　　　　缺点：代码维护成本高，相关sql都需要改动　　2、分区，所有的数据还在一个表中，但物理存储数据根据一定的规则存放在不同的文件中，文件也可以放到另外磁盘上　　　　优点：代码维护量小，基本不...

Mysql临时表和分区表【代码】【图】

临时表与内存表内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表临时表，可以使用各种引擎类型。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎。临时表特性建表语法是create te...

MySQL 分区表各个分区的行数

分区的信息是记录在information_schema.partitions 这个表里的。它不能直接定位行所在的分区，但它可查到每个分区中有多少行。例子： select partition_name as part,partition_expression as expr,partition_description as descr,table_rows from information_schema.partitions where table_schema= schema() and table_name=‘t‘;MySQL 分区表各个分区的行数标签：本文系统来源：http://www.cnblogs.com/JiangLe/p/4491462.h...

Mysql5.6—分区表及独享表空间

[root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL.[确定] 2、修改 /etc/my.cnf配置文件 [root@localhost ~]# vi /etc/my.cnf [mysqld] innodb_file_per_table = 1 3、启动Mysql [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL..[确定] 4、连接到Mysql数据库 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your...

MySQL之高级特性---分区表【代码】

CREATE TABLE sales (order_date DATETIME NOT NULL,-- Other columns omitted ) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) ( PARTITION p_2010 VALUES LESS THAN (2010), PARTITION p_2011 VALUES LESS THAN (2011), PARTITION p_2012 VALUES LESS THAN (2012), PARTITION p_catchall VALUES LESS THAN MAXVALUE );PARTITION 分区子句可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个...

MySQL 分区表原理及使用详解【代码】

分区查询 1）查询某张表一共有多少个分区 mysql> select -> partition_name,-> partition_expression,-> partition_description,-> table_rows-> from -> INFORMATION_SCHEMA.partitions-> where-> table_schema=‘test‘-> and table_name = ‘emp‘; +----------------+----------------------+-----------------------+------------+ | partition_name | partition_expression | partition_description | table_r...

MySQL分区表【代码】【图】

CREATE TABLE sales (id INT AUTO_INCREMENT,amount DOUBLE NOT NULL,order_day DATETIME NOT NULL,PRIMARY KEY(id, order_day) ) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (PARTITION p_2010 VALUES LESS THAN (2010),PARTITION p_2011 VALUES LESS THAN (2011),PARTITION p_2012 VALUES LESS THAN (2012),PARTITION p_catchall VALUES LESS THAN MAXVALUE);这段语句表示将表内数据按照order_dy的年份范围进行分区,201...

MySQL分区表管理【代码】

但是如果e2里面的数据不能够满足分区p0的要求的话,切分区就会失败.1737 - Found a row that does not match the partition只有再指定不验证的时候才不会报错ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;WITHOUT VALIDATION 指定的时候效率会更高,因为不再做逐行验证了. 子分区和没分区的表进行切换 1:假设创建一个分区表,带有子分区> CREATE TABLE es (-> id INT NOT NULL,-> fname VARCHAR...

MySQL5.5的分区表

MySQL 分区表原理及数据备份转移实战

1、分区表含义分区表定义指根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。 2、分区表优点...

MySQL 分区表探究细节《整理自官档》

1. Date Time分区When partitioning by KEY or LINEAR KEY, youcan use a DATE, TIME, or DATETIME column as thepartitioning column without performing any modification of the column value. Forexample, this table creation statement is perfectly valid in MySQL:如果考虑不转换列值，可采取key进行分区。CREATE TABLE members(firstname VARCHAR(25)NOT NULL,lastname VARCHAR(25)NOT NULL,username VARCHAR(16)NOT NU...

Mysql --- 分区表【代码】【图】

TABLE IF EXISTS p_user; CREATE TABLE p_user(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(225),login_date DATETIME )ENGINE=INNODB PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30), PARTITION p3 VALUES LESS THAN MAXVALUE ) 分区表p_user，小于10的id存储在p0分区中，10到20之间的id存储在p1中，以此类推，大于30的id存储在p3...

首页 / MYSQL / mysql动态新建以及删除分区表_MySQL

mysql动态新建以及删除分区表_MySQL

内容导读

内容图文

内容总结

内容备注

内容手机端

【mysql动态新建以及删除分区表_MySQL】教程文章相关的互联网学习教程文章

MySQL分区表例子——List分区

MySQL5.7传输表空间——迁移分区表

Mysql5.6—分区表及独享表空间

详解MySQL分区表【代码】【图】

Mysql临时表和分区表【代码】【图】

MySQL 分区表各个分区的行数

Mysql5.6—分区表及独享表空间

MySQL之高级特性---分区表【代码】

MySQL 分区表原理及使用详解【代码】

MySQL分区表【代码】【图】

MySQL分区表管理【代码】

MySQL5.5的分区表

MySQL 分区表原理及数据备份转移实战

MySQL 分区表探究细节《整理自官档》

Mysql --- 分区表【代码】【图】

MYSQL - 相关标签

分区表 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程