在这些图的帮助下，我们对插入一条记录到多列分区表的步骤有了更深的了解，这些都是理论上的，为了帮助你更好地掌握新功能，我们再来看一个更高级一点的例子，对于比较务实的读者更有意义，下面是表的定义脚本：
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) DEFAULT NULL,
hire_date date NOT NULL )
ENGINE=MyISAM PARTITION BY RANGE COLUMNS(gender,hire_date)
(PARTITION p01 VALUES LESS THAN ('F','1990-01-01') ,
PARTITION p02 VALUES LESS THAN ('F','2000-01-01') ,
PARTITION p03 VALUES LESS THAN ('F',MAXVALUE) ,
PARTITION p04 VALUES LESS THAN ('M','1990-01-01') ,
PARTITION p05 VALUES LESS THAN ('M','2000-01-01') ,
PARTITION p06 VALUES LESS THAN ('M',MAXVALUE) ,
PARTITION p07 VALUES LESS THAN (MAXVALUE,MAXVALUE)
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) DEFAULT NULL,
hire_date date NOT NULL )
ENGINE=MyISAM PARTITION BY RANGE COLUMNS(gender,hire_date)
(PARTITION p01 VALUES LESS THAN ('F','1990-01-01') ,
PARTITION p02 VALUES LESS THAN ('F','2000-01-01') ,
PARTITION p03 VALUES LESS THAN ('F',MAXVALUE) ,
PARTITION p04 VALUES LESS THAN ('M','1990-01-01') ,
PARTITION p05 VALUES LESS THAN ('M','2000-01-01') ,
PARTITION p06 VALUES LESS THAN ('M',MAXVALUE) ,
PARTITION p07 VALUES LESS THAN (MAXVALUE,MAXVALUE)
和上面的例子不同，这个例子更好理解，第一个分区用来存储雇佣于1990年以前的女职员，第二个分区存储股用于1990-2000年之间的女职员，第三个分区存储所有剩下的女职员。对于分区p04到p06，我们策略是一样的，只不过存储的是男职员。最后一个分区是控制情况。
看完后你可能要问，我怎么知道某一行存储在那个分区中的?有两个办法，第一个办法是使用与分区定义相同的条件作为查询条件进行查询。
SELECT CASE
WHEN gender = 'F' AND hire_date < '1990-01-01'
THEN 'p1'
WHEN gender = 'F' AND hire_date < '2000-01-01'
THEN 'p2'
WHEN gender = 'F' AND hire_date < '2999-01-01'
THEN 'p3'
WHEN gender = 'M' AND hire_date < '1990-01-01'
THEN 'p4'
WHEN gender = 'M' AND hire_date < '2000-01-01'
THEN 'p5'
WHEN gender = 'M' AND hire_date < '2999-01-01'
THEN 'p6' ELSE
'p7' END as p,
COUNT(*) AS rows FROM employees
GROUP BY p;
+------+-------+
| p | rows |
+------+-------+
| p1 | 66212 |
| p2 | 53832 |
| p3 | 7 |
| p4 | 98585 |
| p5 | 81382 |
| p6 | 6 |
+------+-------+
WHEN gender = 'F' AND hire_date < '1990-01-01'
THEN 'p1'
WHEN gender = 'F' AND hire_date < '2000-01-01'
THEN 'p2'
WHEN gender = 'F' AND hire_date < '2999-01-01'
THEN 'p3'
WHEN gender = 'M' AND hire_date < '1990-01-01'
THEN 'p4'
WHEN gender = 'M' AND hire_date < '2000-01-01'
THEN 'p5'
WHEN gender = 'M' AND hire_date < '2999-01-01'
THEN 'p6' ELSE
'p7' END as p,
COUNT(*) AS rows FROM employees
GROUP BY p;
+------+-------+
| p | rows |
+------+-------+
| p1 | 66212 |
| p2 | 53832 |
| p3 | 7 |
| p4 | 98585 |
| p5 | 81382 |
| p6 | 6 |
+------+-------+
如果表是MyISAM或ARCHIVE，你可以信任由INFORMATION_SCHEMA提供的统计信息。
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME='employees';
+------+------------------+-------------------+------------+
| part | expr | descr | table_rows |
+------+------------------+-------------------+------------+
| p01 | gender,hire_date | 'F','1990-01-01' | 66212 |
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME='employees';
+------+------------------+-------------------+------------+
| part | expr | descr | table_rows |
+------+------------------+-------------------+------------+
| p01 | gender,hire_date | 'F','1990-01-01' | 66212 |
| p02 | gender,hire_date | 'F','2000-01-01' |53832 |
| p03 | gender,hire_date | 'F',MAXVALUE | 7 |
| p04 | gender,hire_date | 'M','1990-01-01' | 98585 |
| p05 | gender,hire_date | 'M','2000-01-01' |81382 |
| p06 | gender,hire_date | 'M',MAXVALUE | 6 |
| p07 | gender,hire_date | MAXVALUE,MAXVALUE | 0 |
+------+------------------+-------------------+------------+
+------+------------------+-------------------+------------+
