— 创建表分区 HASH分区
    CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT ‘1970-01-01’,
    separated DATE NOT NULL DEFAULT ‘9999-12-31’,
    job_code INT,
    store_id INT
    )
    PARTITION BY LINEAR HASH(YEAR(hired))
    PARTITIONS 4;
    — range分区
    CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT ‘1970-01-01’,
    separated DATE NOT NULL DEFAULT ‘9999-12-31’,
    job_code INT NOT NULL,
    store_id INT NOT NULL
    )
    PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
    );
    — list分区
    CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT ‘1970-01-01’,
    separated DATE NOT NULL DEFAULT ‘9999-12-31’,
    job_code INT,
    store_id INT
    )
    PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
    );
    — 获取到p_list表的分区详细信息。
    SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME = ‘employees’;
    — 分区的创建信息
    SHOW CREATE TABLE employees;
    — 新增分区
    ALTER TABLE xinshen.xs_tb_price_order
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (ID, PI_DATE);

    ALTER TABLE XS_TB_PRICE_ORDER PARTITION BY RANGE (MONTH(PI_DATE))(
    PARTITION p1 VALUES LESS THAN (1),
    PARTITION p2 VALUES LESS THAN (2),
    PARTITION p3 VALUES LESS THAN (3),
    PARTITION p4 VALUES LESS THAN (4),
    PARTITION p5 VALUES LESS THAN (5),
    PARTITION p6 VALUES LESS THAN (6),
    PARTITION p7 VALUES LESS THAN (7),
    PARTITION p8 VALUES LESS THAN (8),
    PARTITION p9 VALUES LESS THAN (9),
    PARTITION p10 VALUES LESS THAN (10),
    PARTITION p11 VALUES LESS THAN (11),
    PARTITION p12 VALUES LESS THAN (12),
    PARTITION p13 VALUES LESS THAN MAXVALUE
    );

    ALTER TABLE sale_data ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
    — 删除分区
    ALTER TABLE sale_data DROP PARTITION p201010;
    — 合并分区
    ALTER TABLE sale_data
    REORGANIZE PARTITION p201001,p201002,p201003,
    p201004,p201005,p201006,
    p201007,p201008,p201009 INTO
    (
    PARTITION p2010Q1 VALUES LESS THAN (201004),
    PARTITION p2010Q2 VALUES LESS THAN (201007),
    PARTITION p2010Q3 VALUES LESS THAN (201010)
    );
    — 拆分分区

    ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (
    PARTITION s2009 VALUES LESS THAN (201001),
    PARTITION s2010 VALUES LESS THAN (201004)
    );

    — 按年月方式创建分区
    CREATE TABLE sale_data (
    sale_date DATETIME NOT NULL,
    sale_item VARCHAR(2) NOT NULL ,
    sale_money DECIMAL(10,2) NOT NULL
    )
    PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (
    PARTITION p201001 VALUES LESS THAN (201002),
    PARTITION p201002 VALUES LESS THAN (201003),
    PARTITION p201003 VALUES LESS THAN (201004),
    PARTITION p201004 VALUES LESS THAN (201005),
    PARTITION p201005 VALUES LESS THAN (201006),
    PARTITION p201006 VALUES LESS THAN (201007),
    PARTITION p201007 VALUES LESS THAN (201008),
    PARTITION p201008 VALUES LESS THAN (201009),
    PARTITION p201009 VALUES LESS THAN (201010),
    PARTITION pcatchall VLAUES LESS THAN MAXVALUE
    );