Mysql Partition Introduction (v)--list columns partition

Source: Internet
Author: User

Mysql 5.6 began to support the list columns partition, you can start to use multiple columns as the key of the partition, and the data type of the column in addition to the number type can be a partition column; You can also use string types, date and datetime

You have a customer business in 12 cities, for sales and marketing purposes, your organization divides every 3 cities into one region
For the list columns partition, you can create a customer data table based on the name of the city and declare 4 partitions when your customer has the corresponding region:

CREATE TABLE customers_1 (    first_name VARCHAR(25),    last_name VARCHAR(25),    street_1 VARCHAR(30),    street_2 VARCHAR(30),    city VARCHAR(15),    renewal DATE)PARTITION BY LIST COLUMNS(city) (    PARTITION pRegion_1 VALUES IN(‘Oskarshamn‘, ‘H?gsby‘, ‘M?nster?s‘),    PARTITION pRegion_2 VALUES IN(‘Vimmerby‘, ‘Hultsfred‘, ‘V?stervik‘),    PARTITION pRegion_3 VALUES IN(‘N?ssj?‘, ‘Eksj?‘, ‘Vetlanda‘),    PARTITION pRegion_4 VALUES IN(‘Uppvidinge‘, ‘Alvesta‘, ‘V?xjo‘));

使用日期分区

CREATE TABLE customers_2 (    first_name VARCHAR(25),    last_name VARCHAR(25),    street_1 VARCHAR(30),    street_2 VARCHAR(30),    city VARCHAR(15),    renewal DATE)PARTITION BY LIST COLUMNS(renewal) (    PARTITION pWeek_1 VALUES IN(‘2010-02-01‘, ‘2010-02-02‘, ‘2010-02-03‘,        ‘2010-02-04‘, ‘2010-02-05‘, ‘2010-02-06‘, ‘2010-02-07‘),    PARTITION pWeek_2 VALUES IN(‘2010-02-08‘, ‘2010-02-09‘, ‘2010-02-10‘,        ‘2010-02-11‘, ‘2010-02-12‘, ‘2010-02-13‘, ‘2010-02-14‘),    PARTITION pWeek_3 VALUES IN(‘2010-02-15‘, ‘2010-02-16‘, ‘2010-02-17‘,        ‘2010-02-18‘, ‘2010-02-19‘, ‘2010-02-20‘, ‘2010-02-21‘),    PARTITION pWeek_4 VALUES IN(‘2010-02-22‘, ‘2010-02-23‘, ‘2010-02-24‘,        ‘2010-02-25‘, ‘2010-02-26‘, ‘2010-02-27‘, ‘2010-02-28‘));

But this is complicated when the date grows to a very large extent, so it's better to use the range partitioning method.

Mysql Partition Introduction (v)--list columns partition

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.