基于MRG_MyISAM引擎的Mysql分表
2024-10-11 11:41:21
正常情况下的分表,都是直接创建多个相同结构的表,比如table_1、table_2...最近碰到一个特殊需求,需要创建一个主表,所有分表的数据增删改查,全部自动实时更新到主表,这个时候可以使用MRG_MyISAM引擎了。
首先创建主表`mygame_message_log`。然后创建分表(必须使用MyISAM引擎):
create table `mygame_message_log_1` LIKE `mygame_message_log`;
create table `mygame_message_log_2` LIKE `mygame_message_log`;
create table `mygame_message_log_3` LIKE `mygame_message_log`;
create table `mygame_message_log_4` LIKE `mygame_message_log`;
create table `mygame_message_log_5` LIKE `mygame_message_log`;
create table `mygame_message_log_6` LIKE `mygame_message_log`;
create table `mygame_message_log_7` LIKE `mygame_message_log`;
create table `mygame_message_log_8` LIKE `mygame_message_log`;
create table `mygame_message_log_9` LIKE `mygame_message_log`;
create table `mygame_message_log_10` LIKE `mygame_message_log`;
create table `mygame_message_log_11` LIKE `mygame_message_log`;
create table `mygame_message_log_12` LIKE `mygame_message_log`;
create table `mygame_message_log_13` LIKE `mygame_message_log`;
create table `mygame_message_log_14` LIKE `mygame_message_log`;
create table `mygame_message_log_15` LIKE `mygame_message_log`;
create table `mygame_message_log_16` LIKE `mygame_message_log`;
create table `mygame_message_log_17` LIKE `mygame_message_log`;
create table `mygame_message_log_18` LIKE `mygame_message_log`;
create table `mygame_message_log_19` LIKE `mygame_message_log`;
create table `mygame_message_log_20` LIKE `mygame_message_log`;
然后将主表关联到分表:
ALTER TABLE mygame_message_log
ENGINE = MERGE UNION=
(mygame_message_log_1,
mygame_message_log_2,
mygame_message_log_3,
mygame_message_log_4,
mygame_message_log_5,
mygame_message_log_6,
mygame_message_log_7,
mygame_message_log_8,
mygame_message_log_9,
mygame_message_log_10,
mygame_message_log_11,
mygame_message_log_12,
mygame_message_log_13,
mygame_message_log_14,
mygame_message_log_15,
mygame_message_log_16,
mygame_message_log_17,
mygame_message_log_18,
mygame_message_log_19,
mygame_message_log_20)
INSERT_METHOD=no AUTO_INCREMENT=1;
需要注意的是,表的主键不能设为自增,所有表的结构全部要一致。
同时这种方法会有一个缺陷,就是一旦要修改表结构,就会很麻烦。千万不能直接修改!
最好开始就将表设计考虑周到一点,如果实在要修改,可按如下步骤:
1、删除主表;
2、针对所有分表修改相应的字段;
3、重新创建一个主表:
create table `mygame_message_log` LIKE `mygame_message_log_1`;
4、给主表做关联:
ALTER TABLE mygame_message_log
ENGINE = MERGE UNION=
(mygame_message_log_1,
mygame_message_log_2,
mygame_message_log_3,
mygame_message_log_4,
mygame_message_log_5,
mygame_message_log_6,
mygame_message_log_7,
mygame_message_log_8,
mygame_message_log_9,
mygame_message_log_10,
mygame_message_log_11,
mygame_message_log_12,
mygame_message_log_13,
mygame_message_log_14,
mygame_message_log_15,
mygame_message_log_16,
mygame_message_log_17,
mygame_message_log_18,
mygame_message_log_19,
mygame_message_log_20)
INSERT_METHOD=no AUTO_INCREMENT=1;
5、成功,原来主表的数据都恢复了
最新文章
- linux sudo 命令
- [Design Patterns] 1. Primary concept &; term - UML
- 转!数据库连接池概念、种类、配置(DBCP\C3P0\JndI与Tomact配置连接池)
- source insight 里编辑的时候,每次粘贴后,光标停留在粘贴内容的左面
- 怪物AI(复习)
- Eclipse配置CAS client
- MFC基础,MFC自绘控件学习总结.---转
- ECMAScript 6 proxies
- Java中常用加减密方式
- Webpack+vue2.0如何注册全局组件 (01)
- 程序员 面试题【前端,java,php】
- C#给checkboxList设置js选中事件
- jdbcTemplate的简单介绍
- is_null, empty, isset的区别
- Mysql优化_内置profiling性能分析工具
- Windows系统下在Eclipse中集成Python
- linux kernel swap daemon
- 20155319 《Java程序设计》实验五(网络编程与安全)实验报告
- Msql中的触发器
- 获取jqGrid中选择的行的数据