How to add/create default partition for a table in mysql

Question

I searched a lot - but couldn't find. How to create default partition for a table in mysql 5.7. Thanks a lot!

Answer 1

First check if your server support partitioning by running this query:

mysql> SELECT
    ->     PLUGIN_NAME as Name,
    ->     PLUGIN_VERSION as Version,
    ->     PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE'

partionning state should be active

if not run this another query to see whether the plugin is installed:

mysql> SHOW PLUGINS;

In either case, if you do not see the partition plugin listed with the value ACTIVE for the Status column in the output (shown in bold text in each of the examples just given), then your version of MySQL was not built with partitioning support.

If partitioning is enabled then this is an example to create a table partition for innoDB.

CREATE TABLE t1 (
    id INT,
    year_col INT
);

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;

or

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005),
    ->         PARTITION p4 VALUES LESS THAN (2010),
    ->         PARTITION p5 VALUES LESS THAN (2015)
    ->     );
Query OK, 0 rows affected (0.28 sec)

reference :https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html ok this code will work

CREATE TABLE t (a INT, b TEXT)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,5,6,7),
 PARTITION p1 VALUES IN (3,4,9),
 PARTITION pDefault VALUES IN (DEFAULT));

When using a DEFAULT partition, it will no longer be possible to ADD a partition, but one must instead REORGANIZE the DEFAULT partition, just like MAXVALUE partition in RANGE partitioning.