Shulou(Shulou.com)06/01 Report--

This article mainly introduces RANGE COLUMNS section handouts, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

RANGE COLUMNS and RANGE partitions are very similar, but there are many differences between the two.

RANGE COLUMNS cannot use expressions, but can only use column names RANGE COLUMNS to accept lists of one or more fields RANGE COLUMNS partitioned columns are not limited to numeric columns; string, DATE and DATETIME columns can also be used in partitioned columns

Basic definition:

CREATE TABLE table_namePARTITIONED BY RANGE COLUMNS (column_list) (PARTITION partition_name VALUES LESS THAN (value_list) [, PARTITION partition_name VALUES LESS THAN (value_list)] [,...]) column_list: column_name [, column_name] [,...] value_list: value [, value] [,...]

Column_list is one or more column names, and value_list is one or more values corresponding to column_list.

Mysql > CREATE TABLE rcx (- > an INT,-> b INT,-> c CHAR (3),-> d INT->)-> PARTITION BY RANGE COLUMNS (aPARTITION dpenc) (- > PARTITION p0 VALUES LESS THAN (5p10 VALUES LESS THAN),-> PARTITION p1 VALUES LESS THAN (10pm 20mm'),-> PARTITION p2 VALUES LESS THAN (15pl 30mins) -> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)->) Query OK, 0 rows affected (0.15 sec)

If we write three pieces of data to this table with a value of 5 for each column, all three pieces of data will be stored in the p1 partition, because the value of each column is not less than 5, so we can query INFORMATION_SCHEMA.PARTITIONS:

Mysql > INSERT INTO R1 VALUES (5LJ 10), (5L11), (5L12); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > SELECT PARTITION_NAME,TABLE_ROWS-> FROM INFORMATION_SCHEMA.PARTITIONS-> WHERE TABLE_NAME ='R1' +-+-+ | PARTITION_NAME | TABLE_ROWS | +-+-+ | p0 | 0 | | P1 | 3 | +-+- -+ 2 rows in set (0.00 sec)

Similarly, RANGE COLUMNS, like RANGE partitions, supports MAXVALUE.

CREATE TABLE rc1 (an INT, b INT) PARTITION BY RANGE COLUMNS (a, b) (PARTITION p0 VALUES LESS THAN (5,12), PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE))

But at this point, the distribution of written data will also change greatly.

Mysql > INSERT INTO rc1 VALUES (5 sec 10), (5 rows affected 11), (5 recorder 12); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > SELECT PARTITION_NAME,TABLE_ROWS-> FROM INFORMATION_SCHEMA.PARTITIONS-> WHERE TABLE_NAME = 'rc1' +-+ | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | +-+ | p | p0 | 2 | | p | p1 | 1 | +-+ 2 rows in set (0.00 sec)

Because we're comparing row data instead of scalar values.

Mysql > SELECT (5, 10)

< (5,12), (5,11) < (5,12), (5,12) < (5,12);+-----------------+-----------------+-----------------+| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |+-----------------+-----------------+-----------------+| 1 | 1 | 0 |+-----------------+-----------------+-----------------+1 row in set (0.00 sec) 如果是单个字段的RANGE COLUMNS分区, 那么和RANGE分区是一致的 CREATE TABLE rx ( a INT, b INT)PARTITION BY RANGE COLUMNS (a) ( PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (MAXVALUE)); 如果我们新增行(5,10), (5, 11)和(5,12)到表中, 我们可以看到他们存储的位置是一样的 mysql>

Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > SELECT PARTITION_NAME,TABLE_ROWS-> FROM INFORMATION_SCHEMA.PARTITIONS-> WHERE TABLE_NAME = 'rx' +-+ | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | +-+ | p | p0 | 0 | | p | p1 | 3 | +-+ 2 rows in set (0.00 sec)

After reading the above handouts on RANGE COLUMNS partition, I hope it can bring some help to you in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.