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万以上的数据是可以看到查询的效果的
