What is the RANGE partition in Mysql

Let's talk about what is the RANGE partition in Mysql. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on what is the RANGE partition in Mysql.

Partition by scope, giving a certain range for each partition. The range must be continuous and cannot be repeated. Use the VALUES LESS THAN operator.

Let's first create a range partitioned table

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 NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21))

Those with store_id less than 6 will be put into the first partition, and those less than 11 will be put into the second partition.

What if my store_id is greater than 21? So we have to change the way the partition is created

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 NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) PARTITION p4 VALUES LESS THAN (MAXVALUE))

Add a MAXVALUE, MAXVALUE means that all data greater than 21 will be put into this partition, of course, there is another way to avoid this problem, is to add the IGNORE keyword when INSERT.

Partition key type is time-to-timestamp

You can use UNIX-TIMESTAMP ()

CREATE TABLE quarterly_report_status (report_id INT NOT NULL, report_status VARCHAR (20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) PARTITION BY RANGE (UNIX_TIMESTAMP (report_updated)) (PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP ('2008-01-01 00 NOT NULL 0000')), PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP ('2008-04-01 00 NOT NULL 0000')) PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP ('2008-07-01 00 UNIX_TIMESTAMP), PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP (' 2008-10-01 00 UNIX_TIMESTAMP)), PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP ('2009-01-01 00 UNIX_TIMESTAMP')), PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP ('2009-04-01 00 purr 00')) PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP ('2009-07-01 00 UNIX_TIMESTAMP), PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP (' 2009-10-01 00 UNIX_TIMESTAMP)), PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP ('2010-01-01 00 VALUES LESS THAN (MAXVALUE)

With the exception of UNIX_TIMESTAMP, other expressions involving timestamps are not allowed

Partition based on time number

CREATE TABLE members (firstname VARCHAR (25) NOT NULL, lastname VARCHAR (25) NOT NULL, username VARCHAR (16) NOT NULL, email VARCHAR (35), joined DATE NOT NULL) PARTITION BY RANGE (YEAR (joined)) (PARTITION p0 VALUES LESS THAN (1960), PARTITION p1 VALUES LESS THAN (1970), PARTITION p2 VALUES LESS THAN (1980), PARTITION p3 VALUES LESS THAN (1990), PARTITION p4 VALUES LESS THAN MAXVALUE)

Is there anything you don't understand about the above RANGE partition in Mysql? Or if you want to know more about it, you can continue to follow our industry information section.