mysql带分区表备忘

 

建表 
CREATE TABLE `sp_football_team_schedule.copy` (

  `com_id` int(10) unsigned DEFAULT '0' COMMENT '联赛id',

  `com_name` varchar(16) DEFAULT '0' COMMENT '联赛简称',

  `season_id` int(10) unsigned DEFAULT '0' COMMENT '赛季id',

  `round` varchar(16) DEFAULT '' COMMENT '轮次',

  `game_time` varchar(32) DEFAULT '' COMMENT '比赛时间',

  `home_id` varchar(10) DEFAULT '' COMMENT '主队id',

  `home_name` varchar(32) DEFAULT '' COMMENT '主队名称',

  `home_score` varchar(5) DEFAULT '' COMMENT '主队得分',

  `home_score_h` varchar(5) DEFAULT '' COMMENT '主队半场得分',

  `guest_id` varchar(10) DEFAULT '' COMMENT '客队id',

  `guest_name` varchar(32) DEFAULT '' COMMENT '客队名称',

  `guest_score` varchar(5) DEFAULT '' COMMENT '客队得分',

  `guest_score_h` varchar(5) DEFAULT '' COMMENT '半场得分',

  `name_id` int(10) DEFAULT NULL COMMENT 'id',

  `name` varchar(32) DEFAULT NULL COMMENT '名称',

  KEY `index_name` (`name_id`),
  KEY `index_name1` (`com_name`,`season_id`,`round`),
  KEY `index_name2`

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='资料库-赛程赛果'

  PARTITION by range (season_id)(

  PARTITION p0 VALUES LESS THAN (1500),

  PARTITION p1 VALUES LESS THAN (3000),

  PARTITION p2 VALUES LESS THAN (4500),

  PARTITION p3 VALUES LESS THAN (6000),

  PARTITION p4 VALUES LESS THAN (7500),

  PARTITION p5 VALUES LESS THAN MAXVALUE

);

查询表的分区及对应信息

SELECT  *  FROM

INFORMATION_SCHEMA.partitions

WHERE

TABLE_SCHEMA = schema()

AND TABLE_NAME='sp_football_team_schedule'; 

查询单个分区数据

select * from `sp_football_team_schedule` PARTITION(p0); 

添加表索引
 ALTER TABLE `table_name` ADD INDEX index_name ( `index_name` )

删除单表数据
 CREATE TABLE `football_data_team.copy` SELECT

t.com_id,t.com_name,t.com_name_full,t.season,t.team_id,t.team_name,t.team_name_en

FROM

`football_data_team` t

GROUP BY

t.com_id,t.com_name,t.com_name_full,t.season,t.team_id,t.team_name,t.team_name_en;

DROP TABLE `football_data_team`;

ALTER TABLE `football_data_team.copy` RENAME TO `football_data_team`;

 A,B表数据同步

INSERT INTO table_name(index1,index2,index3)

SELECT x.index1,x.index2,x.index3

FROM (SELECT index1,index2,index3

FROM table_name_1

WHERE (index1,index2)

not in (SELECT index2,index2 FROM table_name)) x;

A,B表数据修改

update table_name a inner join(

select index1,index2,index3,index4

) c on

a.index1=c.index1 and a.index2=c.index2 

set a.index3=c.index3,a.index4=c.index4;