以下是代码:
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 jk20190422 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-22')),
PARTITION jk20190423 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-23')),
PARTITION jk20190424 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-24')),
PARTITION jk20190425 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-25')),
PARTITION jk20190426 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-26')),
PARTITION jk20190427 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-27'))
)
INSERT INTO sales VALUES(1066,'2019-04-19 15:20:59');
INSERT INTO sales VALUES(1088,'2019-04-20 18:20:59');
INSERT INTO sales VALUES(1066,'2019-04-21 17:20:59');
INSERT INTO sales VALUES(1099,'2019-04-22 20:21:59');
INSERT INTO sales VALUES(1088,'2019-04-23 18:20:59');
INSERT INTO sales VALUES(1066,'2019-04-24 12:30:59');
INSERT INTO sales VALUES(1066,'2019-04-25 21:20:59');
INSERT INTO sales VALUES(1066,'2019-04-26 22: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 TABLE_SCHEMA='test'
执行图片如下:
![](https://imgsa.baidu.com/forum/w%3D580/sign=7ea2c9335f0fd9f9a0175561152cd42b/876ab13533fa828b33447bacf31f4134960a5aea.jpg)
![](https://imgsa.baidu.com/forum/w%3D580/sign=5d6f7bacf31f4134e0370576151e95c1/bc55a18b87d6277f655ed47726381f30e824fc82.jpg)
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 jk20190422 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-22')),
PARTITION jk20190423 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-23')),
PARTITION jk20190424 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-24')),
PARTITION jk20190425 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-25')),
PARTITION jk20190426 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-26')),
PARTITION jk20190427 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-27'))
)
INSERT INTO sales VALUES(1066,'2019-04-19 15:20:59');
INSERT INTO sales VALUES(1088,'2019-04-20 18:20:59');
INSERT INTO sales VALUES(1066,'2019-04-21 17:20:59');
INSERT INTO sales VALUES(1099,'2019-04-22 20:21:59');
INSERT INTO sales VALUES(1088,'2019-04-23 18:20:59');
INSERT INTO sales VALUES(1066,'2019-04-24 12:30:59');
INSERT INTO sales VALUES(1066,'2019-04-25 21:20:59');
INSERT INTO sales VALUES(1066,'2019-04-26 22: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 TABLE_SCHEMA='test'
执行图片如下:
![](https://imgsa.baidu.com/forum/w%3D580/sign=7ea2c9335f0fd9f9a0175561152cd42b/876ab13533fa828b33447bacf31f4134960a5aea.jpg)
![](https://imgsa.baidu.com/forum/w%3D580/sign=5d6f7bacf31f4134e0370576151e95c1/bc55a18b87d6277f655ed47726381f30e824fc82.jpg)