mysql分区表添加含自增列的主键

mysql> CREATE TABLEp_hash(
->storeidmediumint(10) NOT NULL DEFAULT '0',
->storename` char(255) DEFAULT NULL
-> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY HASH (storeid)
-> PARTITIONS 4 */;
mysql> insert into p_hash values(1,’a’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into p_hash values(2,’b’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into p_hash values(3,’c’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into p_hash values(4,’d’);
Query OK, 1 row affected (0.00 sec)
添加一个id字段，位置在第一位
alter table p_hash add column id int not null first;
添加主键
alter table p_hash add primary key(id, storeid);
修改id为自增
alter table p_hash change id id int not null auto_increment;
mysql> select * from p_hash;
+—-+———+———–+
| id | storeid | storename |
+—-+———+———–+
| 1 | 4 | d |
| 2 | 1 | a |
| 3 | 2 | b |
| 4 | 3 | c |
+—-+———+———–+
4 rows in set (0.00 sec)
mysql> show create table p_hash;
———+
| Table | Create Table
| p_hash | CREATE TABLE p_hash (
id int(11) NOT NULL AUTO_INCREMENT,
storeid mediumint(10) NOT NULL DEFAULT ‘0’,
storename char(255) DEFAULT NULL,
PRIMARY KEY (id,storeid)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (storeid)
PARTITIONS 4 */ |
+——–+———————————————–

如果原表中的分区字段上有重复的值，那么在添加主键的时候会提示错误，需要先把重复的记录删除，添加主键后，在插入
mysql> alter table p_hash add primary key(id, storeid);
ERROR 1062 (23000): Duplicate entry ‘0-1’ for key ‘PRIMARY’
`

如果存在重复的数据，采用上面的方式，对于原表变动较大，线上业务不可能接受，使用下面的方式我认为更好些。
创建一个包含自增主键与原表结构相同的表。确保原表没有新的数据插入，将所有的数据插入到新表中，互换2个表的名字。这种对于insert操作不频繁的情况下是可以接受的，如果原表上的insert很多，那么重命名表的过程可能失败，需要考虑别的方案

mysql> CREATE TABLE `p_hash` (
    ->
    -> `storeid` mediumint(10) NOT NULL DEFAULT '0',
    -> `storename` char(255) DEFAULT NULL
    ->
    -> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
    -> /*!50100 PARTITION BY HASH (storeid)
    -> PARTITIONS 4 */;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into p_hash values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into p_hash values(1,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into p_hash values(2,'c');
Query OK, 1 row affected (0.00 sec)

mysql> select * from p_hash;
+---------+-----------+
| storeid | storename |
+---------+-----------+
|       1 | a         |
|       1 | b         |
|       2 | c         |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `p_hash2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `p_hash2` (
    -> `id` int(10) NOT NULL AUTO_INCREMENT,
    -> `storeid` mediumint(10) NOT NULL DEFAULT '0',
    -> `storename` char(255) DEFAULT NULL,
    -> PRIMARY KEY (`id`,`storeid`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
    -> /*!50100 PARTITION BY HASH (storeid)
    -> PARTITIONS 4 */;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into p_hash2 select storeid,storename from p_hash;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into p_hash2(storeid,storename) select storeid,storename from p_hash;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from p_hash2;
+----+---------+-----------+
| id | storeid | storename |
+----+---------+-----------+
| 11 |       1 | a         |
| 12 |       1 | b         |
| 13 |       2 | c         |
+----+---------+-----------+
3 rows in set (0.00 sec)

mysql> show create table p_hash2;
+---------+-----------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------+
| Table   | Create Table

            |
+---------+-----------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------+
| p_hash2 | CREATE TABLE `p_hash2` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `storeid` mediumint(10) NOT NULL DEFAULT '0',
  `storename` char(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`storeid`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (storeid)
PARTITIONS 4 */ |
+---------+-----------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------+
1 row in set (0.00 sec)

mysql> show create table p_hash;
+--------+------------------------------------------------------------------------------------
-----------------------------------------------------------------+
| Table  | Create Table
                                                                 |
+--------+------------------------------------------------------------------------------------
-----------------------------------------------------------------+
| p_hash | CREATE TABLE `p_hash` (
  `storeid` mediumint(10) NOT NULL DEFAULT '0',
  `storename` char(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (storeid)
PARTITIONS 4 */ |
+--------+------------------------------------------------------------------------------------
-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table p_hash rename p_hash_temp;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table p_hash2 rename p_hash;
Query OK, 0 rows affected (0.02 sec)