An implementation example of Mysql partition table and ordinary table reversal

background

Mysql partition table & ordinary table transfer

1. To partition table

1. The ordinary table is as follows

CREATE TABLE `fg_pm_nbiot_cel_h_cel_t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `int_id` decimal(20,0) DEFAULT NULL COMMENT 'Net element identification int_id',
  `batch` varchar(255) NOT NULL COMMENT 'batch',
  `create_time` datetime DEFAULT NULL COMMENT 'Create time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='NB-IoT cell performance data'

2. To partition table

If batch is used as the partition field and the value of batch is a date string in the format 20210530000,

##First delete the primary key as id, and then add the primary key combination of id and batch (the partition table requires that the partition field needs to be included in the primary key)ALTER TABLE `fg_pm_nbiot_cel_h_cel` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`batch`);

##Add partition informationalter table fg_pm_nbiot_cel_h_cel partition by RANGE COLUMNS(batch) (
    PARTITION p202106020000 VALUES LESS THAN ( '202106020100' )
);

After the above two steps, the ordinary table is converted into a partition table

3. How to convert partitioned table back to normal table

##Only use id as the primary key to restore the primary key, delete it first, and build it backALTER TABLE `fg_pm_nbiot_cel_h_cel` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`);

##Remove partition informationALTER TABLE fg_pm_nbiot_cel_h_cel remove partitioning;

This is the article about the implementation example of Mysql partition table and ordinary table reversal. For more related contents of Mysql partition table and ordinary table reversal, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!