mysql分区

最新推荐文章于 2024-10-17 18:58:27 发布

chenglu1855

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量91

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/u/3350266/blog/3035942

版权

#查询mysql分区

SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'intable_passengerflow';

建立表分区

CREATE TABLE `intable_passengerflow_new` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `storeId` varchar(255) DEFAULT NULL COMMENT '案场Id',
  `storeName` varchar(255) DEFAULT NULL COMMENT '案场名称',
  `physicalZoneId` bigint(9) DEFAULT NULL COMMENT '区域Id',
  `physicalZone` varchar(255) DEFAULT NULL COMMENT '区域名称',
  `physicalZonetype` varchar(255) DEFAULT NULL COMMENT '区域类型',
  `countEnter` int(10) DEFAULT NULL COMMENT '进客流',
  `countExit` int(10) DEFAULT NULL COMMENT '出客流',
  `dataDate` date NOT NULL DEFAULT '2014-01-31' COMMENT '日期',
  `dataHour` int(10) DEFAULT NULL COMMENT '小时',
  `dataMin` int(10) DEFAULT NULL COMMENT '分钟',
  `datetime` datetime DEFAULT NULL COMMENT '客流时间',
  `updatetime` datetime DEFAULT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `storeid` (`storeId`,`physicalZone`,`physicalZonetype`,`datetime`),
  KEY `storeid_2` (`storeId`,`physicalZonetype`,`dataDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS('dataDate))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2014-01-31')) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (TO_DAYS('2014-06-30')) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (TO_DAYS('2014-12-31')) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (TO_DAYS('2015-01-31')) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (TO_DAYS('2015-06-30')) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (TO_DAYS('2015-12-31')) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (TO_DAYS('2016-01-31')) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (TO_DAYS('2016-06-30')) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (TO_DAYS('2016-12-31')) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (TO_DAYS('2017-01-31')) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (TO_DAYS('2017-06-30')) ENGINE = InnoDB
)

转载于:https://my.oschina.net/u/3350266/blog/3035942