1.使用merge存储存储引擎来实现分表
CREATE TABLE test1 LIKE test;
INSERT INTO csdn1 SELECT * FROM csdn LIMIT 50000;
INSERT INTO csdn2 SELECT * FROM csdn LIMIT 50001,50000;
INSERT INTO csdn3 SELECT * FROM csdn LIMIT 100002,50000;
INSERT INTO csdn4 SELECT * FROM csdn LIMIT 150003,50000;
INSERT INTO csdn5 SELECT * FROM csdn LIMIT 200004,50000;
INSERT INTO csdn6 SELECT * FROM csdn LIMIT 250005,50000;
复制主表数据到分表
给个汇总表:
CREATE TABLE csdnAll LIKE csdn;
ALTER TABLE csdnall ENGINE=MERGE UNION=(csdn1,csdn2,csdn3,csdn4,csdn5,csdn6) INSERT_METHOD=LAST;
利用循环来分表:
<?php
for($i=0;$i<100;$i++){
"insert into table{$i} select * from tables where mid%100={$i} <br>"
}
然后更新新表:
RENAME TABLE TB1 TO TABLES_BAK, TABLES_TMP TO MEMBERS;
使用上面那个for循环来导出数据,因为1000万的数据一次性导出是不对的,mid是主键,一个区间一个区间的导,基本是一次导出5万条吧,
最终实现分库分表