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

The following mainly brings you how to partition the mysql reconstruction table and retain the data. I hope these words can bring you practical use. This is also the main purpose of this article that I edit the mysql reconstruction table how to partition and retain the data. All right, don't talk too much nonsense, let's just read the following.

How mysql rebuilds the table partition and preserves the data:

1. Create a new table with the same structure as the original table, a new partition.

two。 Copy the data from the original table to the new table.

3. Delete the original table.

4. Change the new table name to the original table name.

Example:

The original structure of the log table is as follows, partitioned by id.

CREATE DATABASE `test`; use `test` CREATE TABLE `log` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `content` text NOT NULL COMMENT 'content', `status` tinyint (3) unsigned NOT NULL COMMENT 'record status', `addtime` int (11) unsigned NOT NULL COMMENT 'add time', `lastmodify` int (11) unsigned NOT NULL COMMENT 'last modification time', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSETCHARSET 'utf8Band' 50100 PARTITION BY RANGE (id) (PARTITION p10w VALUES LESS THAN (100000) ENGINE=InnoDB, PARTITION p20w VALUES LESS THAN (200000) ENGINE=InnoDB PARTITION p50w VALUES LESS THAN (500000) ENGINE = InnoDB,PARTITION p100w VALUES LESS THAN (1000000) ENGINE = InnoDB,PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) * / Insert into `log` (content,status,addtime,lastmodify) values ('content1',1, unix_timestamp (' 2018-01-11 00 values'), unix_timestamp ('2018-01-11 00 values')), ('content2',1, unix_timestamp (' 2018-02-22 00 values'), unix_timestamp ('2018-02-22 00 values'), ('content3',1, unix_timestamp (' 2018-03-31 00 values') Unix_timestamp ('2018-03-31 00 rig 0000')

View data partition distribution

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 'log' +-+-+ | PARTITION_NAME | TABLE_ROWS | +-+-+ | p10w | 3 | | p20w | 0 | | p50w | 0 | | p100w | 0 | | Pmax | 0 | +-+-+

Log data needs to be searched by time, so the partition needs to be rebuilt by log time.

1. Create log2 by time partition (1 partition per month)

CREATE TABLE `log2` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `content` text NOT NULL COMMENT 'content', `status` tinyint (3) unsigned NOT NULL COMMENT 'record status', `addtime` int (11) unsigned NOT NULL COMMENT 'add time', `lastmodify` int (11) unsigned NOT NULL COMMENT 'last modification time', PRIMARY KEY (`id`, `addtime`), KEY `id` (`id`) KEY `addtime` (`addtime`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET`UTF8According to 50100 PARTITION BY RANGE (addtime) (PARTITION p201801 VALUES LESS THAN (unix_timestamp ('2018-02-0100: 00PARTITION BY RANGE 00')) ENGINE=InnoDB, PARTITION p201802 VALUES LESS THAN (unix_timestamp (' 2018-03-0100: 00PARTITION BY RANGE')) ENGINE=InnoDB, PARTITION p201803 VALUES LESS THAN (unix_timestamp ('2018-04-0100: 00addtime') ENGINE=InnoDB PARTITION p201804 VALUES LESS THAN (unix_timestamp ('2018-05-01 0015-0000 ENGINE = InnoDB,PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) * /

two。 Copy the data from log to log2

Insert into `log2` select * from `log`

3. Delete the log table

Drop table `log`

4. Rename the log2 table to log

Rename table `log2` to `log`

View data partition distribution after execution

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 'log' +-+ | PARTITION_NAME | TABLE_ROWS | +-+-+ | p201801 | 1 | | p201802 | 1 | | p201803 | 1 | | p201804 | 0 | | pmax | 0 | +-+-+

For the above about how the mysql rebuild table to achieve partitioning and retain data, you do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.