mysql merge分表实际操作

时间：2022-09-18 19:55:40

分表即把数据量大的表拆分成小表，根据项目的需求这里设为每个月定时创建merge分表，合并主表。
建立分表

DROP TABLE IF EXISTS `device_log_201611`;
CREATE TABLE `device_log_201611`( `id` VARCHAR (36) NOT NULL COMMENT 'ID主键', `deviceId` VARCHAR(36), `access_time` DATETIME, remark VARCHAR(20), PRIMARY KEY (`id`) )CHARSET=utf8 ENGINE=MYISAM;

建立主表

DROP TABLE IF EXISTS `device_log`;
CREATE TABLE `device_log`( `id` VARCHAR (36) NOT NULL COMMENT 'ID主键', `deviceId` VARCHAR(36), `access_time` DATETIME, remark VARCHAR(20), PRIMARY KEY (`id`) )CHARSET=utf8 ENGINE=MERGE UNION(device_log_201611) INSERT_METHOD=LAST;

—创建存储过程

DELIMITER //
CREATE PROCEDURE createT_pro() BEGIN DECLARE oldnames TEXT;
DECLARE newname VARCHAR(100);
DECLARE createtable VARCHAR(200);
DECLARE altertable TEXT;
SELECT GROUP_CONCAT(TABLE_NAME) INTO oldnames FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'device_log_%';
SET newname = CONCAT('device_log_',DATE_FORMAT(NOW(),'%Y%m%d%H%i'));
SET oldnames = CONCAT(oldnames,',',newname);
SET createtable = CONCAT('CREATE TABLE ',newname,' LIKE device_log_201611;');
SET altertable = CONCAT('ALTER TABLE device_log UNION=(',oldnames,')');
SET @sql = createtable;
PREPARE stmt FROM @sql;
EXECUTE stmt;
SET @sql = altertable;
PREPARE stmt FROM @sql;
EXECUTE stmt;
END
//
DELIMITER ;

定时器

SHOW VARIABLES LIKE '%sche%'; -- 查看定时器状态
SET GLOBAL event_scheduler = 1;  -- 启动定时器
-- SET GLOBAL event_scheduler = 0; -- 停止定时器

调用存储过程
每个月的一号凌晨1 点执行

DROP EVENT IF EXISTS log_event;
CREATE EVENT IF NOT EXISTS log_event ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR) ON COMPLETION PRESERVE DO CALL createLog_pro;

附录：mysql定时器的设置http://blog.sina.com.cn/s/blog_6d39ac7e01017sd6.html

1、 INSERT_METHOD = LAST表示插入到UNION表的最后一个表，
2、 .merge数据存储结构mysql中MyISAM引擎下每一张表都对应三个文件: .MYD数据文件，.MYI索引文件，.frm表结构文件.但是Merge引擎下每一张表只有一个.MRG文件.MRG里面存放着分表的关系，以及插入数据的方式。它就像是一个外壳，或者是连接池,
3、数据存放在分表里面.
4、.CRUD对于增删改查，直接操作总表t就可以了。
5、。MERGE表只对建表之后的操作负责
6、不能直接删除一个分表，这样会破坏merge表。正确的方法是: alter table t ENGINE = MRG_MyISAM UNION = (t1) INSERT_METHOD = LAST;
drop table t1
7.误删merge总表，是不会造成数据丢失的，只需重新创建总表
Unable to open underlying table which is differently defined or of non-MyISAM type or doesn’t exist这个错误不知道为什么经常发生，我做的结果就是每次把所有建立的表都删完，重新执行上面的指令。当然表结构一定要一直。存储过程和事件要写对。

标签：

相关文章