MySQL普通表如何转换成分区表

共计 4602 个字符，预计需要花费 12 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 MySQL 普通表如何转换成分区表，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

以 jxfp_data_bak 表为例:
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)

方法 1: 用 alter table table_name partition by 命令重建分区表

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

直接增加主键会报错:
mysql alter table jxfp_data_bak add PRIMARY KEY(SH);
ERROR 1068 (42000): Multiple primary key defined

需要删除原先的主键，并增加联合主键:
mysql ALTER TABLE `jxfp_data_bak` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`SH`);

查看新的表结构:
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 列为联合主键
| 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)

用 alter table table_name partition by 命令重建分区表:
建 hash 分区会报错, 因为 hash 分区字段只能是整型，不能是 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

但建 key 分区则可以成功:
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

测试分区功能:
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 | p0,p1,p2,p3,p4,p5,p6,p7 | ALL  | NULL          | NULL | NULL    | NULL | 43588 | NULL  |
+—-+————-+—————+————————-+——+—————+——+———+——+——-+——-+
1 row in set (0.00 sec)

以上我们可以看到全表扫描跨越了七个分区 (p0–p7), 说明分区表功能生效。

方法 2: 利用 create table 命令重建分区表

mysql CREATE TABLE `jxfp_data` (
    –   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    –   `SH` varchar(32) DEFAULT NULL COMMENT 税号 ,
    –   `KPJH` varchar(32) DEFAULT NULL COMMENT 开票机号 ,
    –   `ZFJH` varchar(10) DEFAULT NULL COMMENT 主分机号 ,
    –   `TYPE` char(3) DEFAULT NULL,
    –   `MONTH` char(10) DEFAULT NULL,
    –   `STATUS` varchar(255) DEFAULT NULL COMMENT 解析状态标识 ,
    –   `CREATE_TIME` datetime DEFAULT NULL COMMENT 插入时间 ,
    –   `UPDATE_TIME` datetime DEFAULT NULL COMMENT 更新时间 ,
    –   `FP_DATA` mediumtext COMMENT 发票数据 ,
    –   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)        – 创建分区表成功

注意: 如果原表里有主键字段，则 MYSQL 的分区字段必须包含在主键字段内，否则会创建失败，因此上面需要定义两个联合主键 (`id`,`SH`)

插回数据:
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

测试:
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 | p0,p1,p2,p3,p4,p5,p6,p7 | ALL  | NULL          | NULL | NULL    | NULL | 695986 | NULL  |
+—-+————-+———–+————————-+——+—————+——+———+——+——–+——-+
1 row in set (0.00 sec)

以上是“MySQL 普通表如何转换成分区表”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！