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

小编给大家分享一下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普通表如何转换成分区表"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！