mysql表分区 8192_mysql 表分区

mysql> 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_name varchar(20) )->PARTITION BY LIST(store_name)-> ( PARTITION pNorth VALUES IN ('a','b'),-> PARTITION pEast VALUES IN ('c','d'));

ERROR1697 (HY000): VALUES value for partition 'pNorth'must have type INT

mysql>create table customer-> (id int,-> province varchar(20))->partition by list COLUMNS(province)-> (partition pNorthwest values in ('xinjiang','xizang','qinhai','ningxia','shanxi'),-> partition pNorthest values in ('heilongjiang','jilin','liaoning'),-> partition pSouthwest values in ('sichuan','yunnan','guangxi')->);

Query OK,0 rows affected (0.05 sec)

mysql> CREATE TABLE rcx (a INT, b INT,

-> c CHAR(3), d INT)

-> PARTITION BY RANGE COLUMNS(a,b,c)

-> (PARTITION p0 VALUES LESS THAN (5,10,'ggg'),

-> PARTITION p1 VALUES LESS THAN (10,20,'mmm'),

-> PARTITION p2 VALUES LESS THAN (15,30,'sss'),

-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));

Query OK, 0 rows affected (0.12 sec)

mysql> insert into rcx values(4,5,'abc',1),(5,9,'abc',1),(4,11,'ggg',1),(5,11,'abc',1),(6,2, 'abc',1);

Query OK, 5 rows affected (0.03 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from rcx partition (p0);

+------+------+------+------+

| a | b | c | d |

+------+------+------+------+

| 4 | 5 | abc | 1 |

| 5 | 9 | abc | 1 |

| 4 | 11 | ggg | 1 |

+------+------+------+------+

3 rows in set (0.00 sec)

mysql> select * from rcx partition (p1);

+------+------+------+------+

| a | b | c | d |

+------+------+------+------+

| 5 | 11 | abc | 1 |

| 6 | 2 | abc | 1 |

+------+------+------+------+

2 rows in set (0.01 sec)

mysql> select (5,9)

+--------------+

| (5,9)

+--------------+

| 1 |

+--------------+

1 row in set (0.00 sec)

mysql> select (5,9,'abc')> (5,10,'ggg');

+---------------------------+

| (5,9,'abc')> (5,10,'ggg') |

+---------------------------+

| 0 |

+---------------------------+

1 row in set (0.00 sec)

mysql> mysql> select * from information_schema.partitions where table_name='rcx';

+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+----------------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |

+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+----------------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+

| def | A2 | rcx | p0 | NULL | 1 | NULL | RANGE COLUMNS | NULL | `a`,`b`,`c` | NULL | 5,10,'ggg' | 3 | 5461 | 16384 | 0 | 0 | 0 | 2019-04-01 06:56:11 | 2019-04-01 06:58:17 | NULL | NULL | | default | NULL |

| def | A2 | rcx | p1 | NULL | 2 | NULL | RANGE COLUMNS | NULL | `a`,`b`,`c` | NULL | 10,20,'mmm' | 2 | 8192 | 16384 | 0 | 0 | 0 | 2019-04-01 06:56:11 | 2019-04-01 06:58:17 | NULL | NULL | | default | NULL |

| def | A2 | rcx | p2 | NULL | 3 | NULL | RANGE COLUMNS | NULL | `a`,`b`,`c` | NULL | 15,30,'sss' | 0 | 0 | 16384 | 0 | 0 | 0 | 2019-04-01 06:56:11 | NULL | NULL | NULL | | default | NULL |

| def | A2 | rcx | p3 | NULL | 4 | NULL | RANGE COLUMNS | NULL | `a`,`b`,`c` | NULL | MAXVALUE,MAXVALUE,MAXVALUE | 0 | 0 | 16384 | 0 | 0 | 0 | 2019-04-01 06:56:11 | NULL | NULL | NULL | | default | NULL |

+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+----------------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+

4 rows in set (0.01 sec)

mysql> select partition_name,table_rows from information_schema.partitions where table_name='rcx';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0 | 3 |

| p1 | 2 |

| p2 | 0 |

| p3 | 0 |

+----------------+------------+

4 rows in set (0.00 sec)

mysql> CREATE TABLE rcf (a INT, b INT, c INT)

-> PARTITION BY RANGE COLUMNS(a,b,c) (

-> PARTITION p0 VALUES LESS THAN (0,25,50),

-> PARTITION p1 VALUES LESS THAN (20,20,100),

-> PARTITION p2 VALUES LESS THAN (10,30,50),

-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition