MySQL分区实验

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_30572613

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量53

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/hwt987/p/3677237.html

环境 CentOS5.5,内存256MB,MySQL5.5.16

一、创建测试表

CREATE TABLE part_tab(
    c1 INT DEFAULT NULL,
    c2 VARCHAR(30) DEFAULT NULL,
    c3 DATE DEFAULT NULL
) ENGINE=myisam CHARSET=utf8
partition by RANGE(YEAR(c3))(
partition p0 values less than(1995),
partition p1 values less than(1996),
partition p2 values less than(1997),
partition p3 values less than(1998),
partition p4 values less than(1999),
partition p5 values less than(2000),
partition p6 values less than(2001),
partition p7 values less than(2002),
partition p8 values less than(2003),
partition p9 values less than(2004),
partition p10 values less than(2010),
partition p11 values less than MAXVALUE)

CREATE TABLE `no_part_tab` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(30) DEFAULT NULL,
  `c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

二、创建测试数据

CREATE PROCEDURE `p1`()
begin
    declare v int default 0;
    while v<8000000 do
    insert into part_tab values (v,'testing partition',adddate('1995-01-01',(rand(v)*36520) mod 3652));
    set v=v+1;
    end while;
end