mysql表分區(qū)例子

1.表結構：

CREATE?TABLE?`students`?(
??`stu_id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`stu_name`?int(11)?DEFAULT?NULL,
??`stu_sex`?int(11)?DEFAULT?NULL,
??`cla_id`?int(11)?DEFAULT?NULL,
??`stu_phone`?char(11)?DEFAULT?NULL,
??`create_time`?datetime?NOT?NULL?DEFAULT?'0000-00-00?00:00:00',
??PRIMARY?KEY?(`stu_id`,`create_time`)
)PARTITION?BY?RANGE?(TO_DAYS(create_time))
(PARTITION?p20150819?VALUES?LESS?THAN?(736195))

2.存儲過程，每執(zhí)行一次，會創(chuàng)建一周內的分區(qū)（每天一個分區(qū)）

CREATE?PROCEDURE?`createPart`()
BEGIN
??DECLARE?retrows?int;
	DECLARE?pname?varchar(64);
	SET?@a?=?1;
	WHILE?@a?<=?7?DO
		set?pname=REPLACE(concat('p',date(DATE_ADD(NOW(),INTERVAL?@a?DAY))),'-','');
		select?count(1)?into?retrows?from?`information_schema`.`PARTITIONS`?where?partition_name=pname?and?table_name='students';??
????if?retrows=0?then
			SET?@SQL=CONCAT('ALTER?TABLE?students?ADD?PARTITION(','PARTITION?',REPLACE(concat('p',date(DATE_ADD(NOW(),INTERVAL?@a?DAY))),'-',''),'?VALUES?LESS?THAN?(',to_days(date_add(now(),interval?@a+1?day)),'))');
			PREPARE?STMT?FROM?@SQL;
			EXECUTE?STMT;
			DEALLOCATE?PREPARE?STMT;
		end?if;
		SET?@a?=?@a+1;
??END?WHILE;
END

3.存儲過程，每執(zhí)行一次，會刪除前2天~前7天的分區(qū)

CREATE?PROCEDURE?`dropPart`()
BEGIN
	declare?pname?varchar(32);
??declare?retrows?int;
	SET?@a?=?-2;
	WHILE?@a?>=?-7?DO
		set?pname=REPLACE(concat('p',date(DATE_ADD(NOW(),INTERVAL?@a?DAY))),'-','');
		select?count(1)?into?retrows?from?`information_schema`.`PARTITIONS`?where?partition_name=pname?and?table_name='students';??
		if?retrows?>0?then
				SET?@SQL=CONCAT('ALTER?TABLE?students?drop?PARTITION?',REPLACE(concat('p',date(DATE_ADD(NOW(),INTERVAL?@a?DAY))),'-',''));
				PREPARE?STMT?FROM?@SQL;
				EXECUTE?STMT;
				DEALLOCATE?PREPARE?STMT;
		end?if;
		SET?@a?=?@a-1;
??END?WHILE;
end