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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2137603/，如需转载，请注明出处，否则将追究法律责任。