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

The following content mainly brings you the details of mysql partition use commands, the knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing, hoping to bring help to the majority of readers.

You need to use the command to see if mysql supports partitions:

Show variables like partition%'

If you don't see the variable: the value of have_partition_engine is YES, but empty, then the current version of mysql does not support partitions. Mysql has supported partitioning since 5. 1. But if you use mysql higher than version 5.1, you will get the result of empty. Use instead:

Show plugins

Partition active proof of support for partitions appears.

MySQL partitioning policy

RANGE partitions assign multiple rows to partitions based on column values that belong to a given contiguous interval.

DROP TABLE IF EXISTS `p_ range`

CREATE TABLE `p_ range` (

`id`int (10) NOT NULL AUTO_INCREMENT

`name` char (20) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

/ * 50100 PARTITION BY RANGE (id)

(PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) * /

Maximum value

PARTITION BY RANGE (id)

(

PARTITION p0 VALUES LESS THAN (8)

PARTITION p1 VALUES LESS THAN MAXVALUE)

Applicable scenarios:

This means that all data records with id greater than 7 exist in the p1 partition.

RANGE partitions are particularly useful in the following situations:

When you need to delete "old" data. If you use the partitioning scheme given in the most recent example above, you simply use "ALTER TABLE employees DROP PARTITION p0;" to delete all rows corresponding to all employees who stopped working before 1991. For tables with a large number of rows, this is better than running a table such as "DELETE FROM employees WHERE YEAR (separated)"

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.