mysql创建分区

1.需要将日期和主键设置为主键

CREATE TABLE `stat_stock_all` (
  `date` date NOT NULL,
  `code` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `change` varchar(255) DEFAULT NULL,
  `open` varchar(255) DEFAULT NULL,
  `preclose` varchar(255) DEFAULT NULL,
  `close` varchar(255) DEFAULT NULL,
  `high` varchar(255) DEFAULT NULL,
  `low` varchar(255) DEFAULT NULL,
  `volume` varchar(255) DEFAULT NULL,
  `amount` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`date`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS(`date`))
(
  PARTITION p2017q1 VALUES LESS THAN (TO_DAYS('2017-04-01')),
  PARTITION p2017q2 VALUES LESS THAN (TO_DAYS('2017-07-01')),
  PARTITION p2017q3 VALUES LESS THAN (TO_DAYS('2017-10-01')),
  PARTITION p2017q4 VALUES LESS THAN (TO_DAYS('2018-01-01')),

  PARTITION p2018q1 VALUES LESS THAN (TO_DAYS('2018-04-01')),
  PARTITION p2018q2 VALUES LESS THAN (TO_DAYS('2018-07-01')),
  PARTITION p2018q3 VALUES LESS THAN (TO_DAYS('2018-10-01')),
  PARTITION p2018q4 VALUES LESS THAN (TO_DAYS('2019-01-01')),
);

 

//查看分区表的具体情况   
SELECT
    partition_name,
    partition_expression,
    partition_description,
    table_rows
FROM
    information_schema. PARTITIONS
WHERE
    table_schema = SCHEMA ()
AND table_name = 'gt_stk_index'

 

  //查看分区的数据
    EXPLAIN PARTITIONS SELECT * FROM gt_stk_index PARTITION (p2018,p2019);

  查询数据的时候按照日期筛选5万以上的数据是可以看到查询的效果的