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