PolarDB:Convert a non-partitioned table into a partitioned table

Create a non-partitioned table named test_users and insert data into the table.

CREATE TABLE test_users (
    id INT AUTO_INCREMENT,
    region_id INT NOT NULL, -- Partition key.
    email VARCHAR(100) NOT NULL,
    name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, region_id), -- The primary key must include the partition key.
    UNIQUE KEY (email, region_id) -- The unique key must include the partition key.
);

INSERT INTO test_users (region_id, email, name) VALUES
(1, 'alice@example.com', 'Alice'),
(2, 'bob@example.com', 'Bob'),
(3, 'charlie@example.com', 'Charlie');

Convert the test_users table into a partitioned table.

• Convert the test_users table into a list-partitioned table. For more information, see LIST.

  ALTER TABLE test_users 
      PARTITION BY LIST (region_id) (
          PARTITION p_east VALUES IN (1, 2), -- Rows whose region_id values are 1 or 2 are distributed into the p_east partition.
          PARTITION p_west VALUES IN (3) -- Rows whose region_id values are 3 are distributed into the p_west partition.
      );

• Convert the test_users table into a hash-partitioned table. For more information, see HASH.

  ALTER TABLE test_users 
      PARTITION BY HASH(region_id)
      PARTITIONS 4; -- Distribute the table data into four partitions.

• Convert the test_users table into a range-partitioned table. For more information, see RANGE.

  ALTER TABLE test_users 
      PARTITION BY RANGE (region_id) (
          PARTITION p0 VALUES LESS THAN (2),  -- region_id < 2
          PARTITION p1 VALUES LESS THAN (3), --Rows whose region_id values are between 2 (inclusive) and 3 (exclusive) are distributed to partition p1.
          PARTITION p2 VALUES LESS THAN MAXVALUE
      );

Create a non-partitioned table named orders and insert data into the table.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    region_id INT NOT NULL, -- Partition key field 1.
    order_date DATE NOT NULL, -- Partition key field 2.
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, region_id, order_date), -- The primary key must contain all columns of the partition key.
    UNIQUE KEY (customer_id, order_id, region_id, order_date) -- The unique key must contain all columns of the partition key.
);

INSERT INTO orders (region_id, order_date, customer_id, amount) VALUES
(1, '2022-12-31', 1001, 99.99),
(1, '2023-05-01', 1002, 199.99),
(2, '2023-06-01', 1003, 299.99);

Convert the orders table into a range partitioned table. For more information, see RANGE.

ALTER TABLE orders
    PARTITION BY RANGE COLUMNS(region_id, order_date) (
        PARTITION p0 VALUES LESS THAN (1, '2023-01-01'), -- Rows whose region_id values is 1 or less than 1 and whose order_date value is before January 1, 2023 are distributed to partition p0.
        PARTITION p1 VALUES LESS THAN (2, '2024-01-01'), -- Rows whose region_id values is 2 or less than 2 and whose order_date value is before January 1, 2024 are distributed to partition p1.
        PARTITION p2 VALUES LESS THAN (MAXVALUE, MAXVALUE)
    );