mysql 更换主键，添加新主键字段及主键初始化赋值 sql

顺序执行以下sql语句

1.删除表主键约束

ALTER TABLE mobilems_m_weex DROP PRIMARY KEY;

2.添加新主键字段id

ALTER TABLE mobilems_m_weex ADD id BIGINT(20) NOT NULL COMMENT ‘主键’ FIRST;

3.id赋值

#显示行号的sql
SELECT
@r := @r + 1 AS row_num,a.*
FROM mobilems_m_weex a, (SELECT @r := 0) b;

把已经存在的行号当作主键赋值给主键

UPDATE mobilems_m_weex mmw1 INNER JOIN (SELECT
                                          @r := @r + 1 AS row_num,
                                          mmw2.bundle_id,
                                          mmw2.lowest_support_version
                                        FROM mobilems_m_weex mmw2, (SELECT @r := 0) b) tb
    ON mmw1.bundle_id = tb.bundle_id AND mmw1.lowest_support_version = tb.lowest_support_version SET mmw1.id = tb.row_num;

5.设置主键

ALTER TABLE mobilems_m_weex
  ADD PRIMARY KEY (`id`);