mysql(无营养,请略过)

无营养,请略过
mysql:分表,分区;
…收起>>
分表:

CREATE TABLE IF NOT EXISTS `user1` (   
  `id` int(11) NOT NULL AUTO_INCREMENT,   
  `name` varchar(50) DEFAULT NULL,   
  `sex` int(1) NOT NULL DEFAULT '0',   
  PRIMARY KEY (`id`)   
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;   
  
 CREATE TABLE IF NOT EXISTS `user2` (   
  `id` int(11) NOT NULL AUTO_INCREMENT,   
  `name` varchar(50) DEFAULT NULL,   
  `sex` int(1) NOT NULL DEFAULT '0',   
  PRIMARY KEY (`id`)   
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;   

 CREATE TABLE IF NOT EXISTS `alluser` (   
  `id` int(11) NOT NULL AUTO_INCREMENT,   
  `name` varchar(50) DEFAULT NULL,   
  `sex` int(1) NOT NULL DEFAULT '0',   
  INDEX(id)   
 ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;
#还需要一个user_id表,插入前先生成userid

分区(略写):

PARTITION BY HASH(bid) PARTITIONS 10;//HASH bid 分区
SHOW TABLE STATUS //显示表
//查看txt表分区情况
select * from information_schema.PARTITIONS where table_schema = '404XS_www' and table_name = '404XS_txt';
alter table xxxx ENGINE=InnoDB

ALTER TABLE `wx_article` drop PARTITION p27; 
ALTER TABLE wx_article ADD PARTITION (PARTITION p27 VALUES LESS THAN (1460649600) COMMENT = '2016-4-15' ENGINE = InnoDB);
ALTER TABLE wx_article ADD PARTITION (PARTITION p28 VALUES LESS THAN (1462032000) COMMENT = '2016-5-1' ENGINE = InnoDB);
ALTER TABLE wx_article ADD PARTITION (PARTITION p29 VALUES LESS THAN (1463241600) COMMENT = '2016-5-15' ENGINE = InnoDB);
ALTER TABLE wx_article ADD PARTITION (PARTITION p30 VALUES LESS THAN (1464710400) COMMENT = '2016-6-1' ENGINE = InnoDB);

ALTER TABLE wx_article_data REORGANIZE PARTITION p14 INTO (PARTITION p14 VALUES LESS THAN (6000000)  ENGINE = INNODB,PARTITION p15 VALUES LESS THAN MAXVALUE  ENGINE = INNODB);

Leave a Comment

电子邮件地址不会被公开。 必填项已用*标注