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普通表如何转换成分区表”这篇文章的所有内容，感谢各位的阅读！ （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
