1.Mysql merge合并表的要求
- 1.合并的分表必须是 MyISAM 引擎,MyISAN引擎是不支持事务的。
- 2.Merge表只保证合表后数据唯一性,合表前的数据可能会存在重复。
- 3.表的结构必须一致,包括索引、字段类型、引擎和字符集。
- 4.删除 tb_member1 分表正确的做法是
ALTER TABLE `tb_member_all` UNION (tb_member1);
DROP TABLE IF EXISTS `tb_member1`;
如果直接删除tb_member1,tb_member2表会报错。
- 5.不能自动分表,需要定期维护。
2.创建2个分表(Navcat中运行查询)
DROP TABLE IF EXISTS `tb_member1`;
CREATE TABLE `tb_member1` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) NOT NULL COMMENT '名称',
`code` varchar(100) NOT NULL COMMENT '标识',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '分表1';
DROP TABLE IF EXISTS `tb_member2`;
CREATE TABLE `tb_member2` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) NOT NULL COMMENT '名称',
`code` varchar(100) NOT NULL COMMENT '标识',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '分表2';
3.创建1个主表壳(Navcat中运行查询)
DROP TABLE IF EXISTS `tb_member_all`;
CREATE TABLE `tb_member_all` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) NOT NULL COMMENT '名称',
`code` varchar(100) NOT NULL COMMENT '标识',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '总表';
最后我们得到3张表:tb_member1(分表),tb_member2(分表),tb_member_all(总表)
4. 分别向 tb_member1(分表),tb_member2(分表) 插入数据
INSERT INTO `sysab`.`tb_member1`(`id`, `name`, `code`) VALUES (1, '1', '1');
INSERT INTO `sysab`.`tb_member1`(`id`, `name`, `code`) VALUES (2, '11', '11');
INSERT INTO `sysab`.`tb_member1`(`id`, `name`, `code`) VALUES (3, '111', '111');
INSERT INTO `sysab`.`tb_member2`(`id`, `name`, `code`) VALUES (1, '2', '2');
INSERT INTO `sysab`.`tb_member2`(`id`, `name`, `code`) VALUES (2, '22', '22');
INSERT INTO `sysab`.`tb_member2`(`id`, `name`, `code`) VALUES (3, '222', '222');
5. 查询 tb_member_all(总表) 的数据是这样子的
注意:我们并没有向 tb_member_all(总表) 表插入任何数据。
上面查询的结果中,id字段值(3,2,1,2,1,3) 有重复好像不太理想,可以在分表插入数据时使用雪花ID,这样总表查询出来的id就是唯一了。
6. 对这个 tb_member_all(总表) 进行模糊查询、分页查询
7. 自动分表,在总表 tb_member_all(总表) 中创建存储过程
未测试,自行研究
drop PROCEDURE if EXISTS createtb;
create PROCEDURE createtb()
BEGIN
DECLARE tname VARCHAR (255);
DECLARE i INT DEFAULT 1;
DECLARE uniontb VARCHAR (255) DEFAULT '';
SET tname = CONCAT('t', nextval('tb_member_all'));
set @sqlstr = CONCAT('create table ',tname,' like t1');
prepare stmt from @sqlstr;
EXECUTE stmt;
deallocate prepare stmt;
WHILE i <= currval ('tb_member_all') DO
SET uniontb = CONCAT(uniontb, CONCAT('t', i), ',');
SET i = i + 1;
END WHILE;
set uniontb = LEFT(uniontb,LENGTH(uniontb) - 1);
set @sqlstr = CONCAT('ALTER TABLE tb_member_all ENGINE = MERGE UNION = (',uniontb,') INSERT_METHOD = LAST');
prepare stmt from @sqlstr;
EXECUTE stmt;
deallocate prepare stmt;
end
-- 创建事件
CREATE EVENT test_event
ON SCHEDULE
EVERY 1 DAY
STARTS date_add(date(curdate() + 1),interval 3 hour)
DO call createtb;