Shulou(Shulou.com)06/01 Report--

1. Create tables and table partitions

DROP TABLE zy.time_partition

CREATE TABLE zy.time_partition

(TIME DATETIME NOT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8

PARTITION BY RANGE (TO_DAYS (TIME))

(PARTITION p20171031 VALUES LESS THAN (TO_DAYS ('2017-11-01'))

PARTITION p20171101 VALUES LESS THAN (TO_DAYS ('2017-11-02'))

# DATA DIRECTORY'/ data/2010-07-16'

# INDEX DIRECTORY'/ data/2010-07-16'

);

two。 Create a stored procedure that adds daily table partitions

DROP PROCEDURE IF EXISTS zy.time_partition_procedure

DELIMITER $$

CREATE PROCEDURE zy.time_partition_procedure ()

BEGIN

Select replace into @ in_date from information_schema.PARTITIONS b where b.table_name = 'time_partition' order by b.partition_ordinal_position desc limit 1'

Set @ max_date= DATE_ADD (@ in_date,INTERVAL 1 DAY) + 0

Set @ date= DATE_ADD (@ in_date,INTERVAL 1 DAY) + 0

SET @ sql=CONCAT ('ALTER TABLE zy.time_partition add PARTITION (PARTITION pendant VALUES LESS THAN VALUES LESS THAN (TO_DAYS ('', to_days (@ max_date1),''))

SELECT @ sql

PREPARE strsql FROM @ sql; # pre-execute sql

EXECUTE strsql; # execute sql

DEALLOCATE PREPARE strsql; # release sql

COMMIT

END

3. Create events that are stored every day

Delimiter $$

Create event zy.time_partition_event

On schedule every 1 day start date_add (curent () + 1 dint interval 3 hour)

On completion preserve

Enable

Do

Begin

Call zy.time_partition_procedure ()

End

4.

# check whether table partitioning is supported

SHOW VARIABLES LIKE'% partition%'

# query all partitions of the table

SELECT * FROM information_schema.PARTITIONS a WHERE a.table_name IN ('time_partition') ORDER BY partition_ordinal_position DESC

# add table partition

ALTER TABLE zy.time_partition ADD PARTITION (PARTITION p20171102 VALUES LESS THAN (TO_DAYS ('2017-11-02'))

# Delete the partition of the table

ALTER TABLE zy.time_partition DROP PARTITION p20171101

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.