How to convert MySQL normal tables to partitioned tables
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!
