Shulou(Shulou.com)05/31 Report--

Editor to share with you how to convert MySQL ordinary tables into partition tables, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Take jxfp_data_bak table as an example:

Mysql > desc jxfp_data_bak

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | bigint (20) | NO | PRI | NULL | auto_increment |

| | SH | varchar (32) | YES | | NULL |

| | KPJH | varchar (32) | YES | | NULL |

| | ZFJH | varchar (10) | YES | | NULL |

| | TYPE | char (3) | YES | | NULL |

| | MONTH | char (10) | YES | | NULL |

| | STATUS | varchar (255) | YES | MUL | NULL |

| | CREATE_TIME | datetime | YES | | NULL |

| | UPDATE_TIME | datetime | YES | | NULL |

| | FP_DATA | mediumtext | YES | | NULL |

+-+ +

10 rows in set (0.00 sec)

Method 1: rebuild the partition table with the alter table table_name partition by command

Mysql > alter table jxfp_data_bak PARTITION BY KEY (SH) PARTITIONS 8

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Directly adding the primary key will report an error:

Mysql > alter table jxfp_data_bak add PRIMARY KEY (SH)

ERROR 1068 (42000): Multiple primary key defined

You need to delete the previous primary key and add the federated primary key:

Mysql > ALTER TABLE `jxfp_data_ bak` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `SH`)

View the new table structure:

Mysql > desc jxfp_data_bak

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | bigint (20) | NO | PRI | NULL | auto_increment |

| | SH | varchar (32) | NO | PRI |-- SH is listed as the federated primary key |

| | KPJH | varchar (32) | YES | | NULL |

| | ZFJH | varchar (10) | YES | | NULL |

| | TYPE | char (3) | YES | | NULL |

| | MONTH | char (10) | YES | | NULL |

| | STATUS | varchar (255) | YES | | NULL |

| | CREATE_TIME | datetime | YES | | NULL |

| | UPDATE_TIME | datetime | YES | | NULL |

| | FP_DATA | mediumtext | YES | | NULL |

+-+ +

10 rows in set (0.00 sec)

Rebuild the partition table with the alter table table_name partition by command:

An error will be reported when creating a hash partition, because the hash partition field can only be an integer, not a varchar:

Mysql > alter table jxfp_data_bak PARTITION BY HASH (SH) PARTITIONS 8

ERROR 1659 (HY000): Field 'SH' is of a not allowed type for this type of partitioning

However, the key partition can be built successfully:

Mysql > alter table jxfp_data_bak PARTITION BY KEY (SH) PARTITIONS 8

Query OK, 78317 rows affected (3.42 sec)

Records: 78317 Duplicates: 0 Warnings: 0

Test the partition function:

Mysql > explain partitions select * from jxfp_data_bak

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | jxfp_data_bak | p0Magol p1, p2, p3, p4, p5, p6, ALL | ALL | NULL | NULL | NULL | NULL | 43588 | NULL |

+-- +

1 row in set (0.00 sec)

Above we can see that the full table scan spans seven partitions (p0--p7), indicating that the partition table function is in effect.

Method 2: reconstruct the partition table using the create table command

Mysql > CREATE TABLE `jxfp_ data` (

-> `id` bigint (20) NOT NULL AUTO_INCREMENT

-> `SH`varchar (32) DEFAULT NULL COMMENT 'tax number'

-> `KPJH` varchar (32) DEFAULT NULL COMMENT 'invoice machine number'

-> `ZFJH` varchar (10) DEFAULT NULL COMMENT 'main extension'

-> `TYPE` char (3) DEFAULT NULL

-> `MONTH` char (10) DEFAULT NULL

-> `STATUS` varchar (255) DEFAULT NULL COMMENT 'parsing status ID'

-> `TIME`datetime DEFAULT NULL COMMENT 'CREATE_ time'

-> `TIME`datetime DEFAULT NULL COMMENT 'update time'

-> `DATA` mediumtext COMMENT 'invoice data'

-> PRIMARY KEY (`id`, `SH`)

->) ENGINE=InnoDB AUTO_INCREMENT=81060 DEFAULT CHARSET=utf8 PARTITION BY LINEAR KEY (SH) PARTITIONS 8

Query OK, 0 rows affected (0.08 sec)-partitioned table created successfully

Note: if there is a primary key field in the original table, the partition field of MYSQL must be included in the primary key field, otherwise the creation will fail. Therefore, two federated primary keys (`id`, `SH`) need to be defined above.

Insert data back in:

Mysql > insert into jxfp_data select * from ixinnuo_sjcj.jxfp_data

Query OK, 745540 rows affected, 1 warning (28.21 sec)

Records: 745540 Duplicates: 0 Warnings: 1

Test:

Mysql > explain partitions select * from jxfp_data

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | jxfp_data | p0Magol p1, p2, p3, p4, p5, p6, ALL | ALL | NULL | NULL | NULL | NULL | 695986 | NULL |

+-- +

1 row in set (0.00 sec)

These are all the contents of the article "how to convert MySQL ordinary tables into partition tables". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.