测试代码:
alter database test CHARACTER SET utf8;
create table FjJk_Partition_Manager
(
TABLE_NAME VARCHAR(255),
TABLE_SCHEMA VARCHAR(50),
PARTITION_NAME VARCHAR(50),
BEGIN_TIME datetime DEFAULT NULL,
EXPR VARCHAR(50),
TABLE_ROWS VARCHAR(50),
Interval_days int
)
CREATE TABLE sales (
money int(11) NOT NULL,
date TIMESTAMP
)
drop table fjjk_partition_manager
drop table sales
ALTER TABLE sales PARTITION BY RANGE (UNIX_TIMESTAMP(date))(
PARTITION jk20190421 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-21')),
PARTITION jk20190424 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-24')),
PARTITION jk20190427 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-27')),
PARTITION jk20190430 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-30')),
PARTITION jk20190503 VALUES LESS THAN (UNIX_TIMESTAMP('2019-05-03')),
PARTITION jk20190506 VALUES LESS THAN (UNIX_TIMESTAMP('2019-05-06')),
PARTITION jk20190509 VALUES LESS THAN (UNIX_TIMESTAMP('2019-05-11'))
)
INSERT INTO sales VALUES(1066,'2019-04-19 15:20:59');
INSERT INTO sales VALUES(1088,'2019-04-25 18:20:59');
INSERT INTO sales VALUES(1066,'2019-04-27 17:20:59');
INSERT INTO sales VALUES(1099,'2019-04-30 20:21:59');
INSERT INTO sales VALUES(1088,'2019-05-02 18:20:59');
INSERT INTO sales VALUES(1066,'2019-05-05 12:30:59');
INSERT INTO sales VALUES(1066,'2019-05-08 21:20:59');
INSERT INTO sales VALUES(1066,'2019-05-10 09:30:59');
insert into FjJk_Partition_Manager(TABLE_NAME,TABLE_SCHEMA,PARTITION_NAME,BEGIN_TIME,EXPR,TABLE_ROWS,Interval_days) select TABLE_NAME,TABLE_SCHEMA,PARTITION_NAME,date,PARTITION_DESCRIPTION,TABLE_ROWS,TimeStampDiff(DAY,date,now()) from information_schema.partitions,sales where PARTITION_NAME is not null
执行结果:
请教各位大神,共建了7个分区不应该只有7个结果吗?为什么插入了42条,还有为什么table_rows 好多是0