2015-11-27 11:27
浏览 255

MySQL | 主键需要重置

I have a table which stores the ID of support cases using the primary key (column name = caseid).

I have now got to about 100,000 caseid and the number is just too big. I wish to somehow start from a lower number such as 1000.

How do I achieve something like this by not having to delete/archive existing records and not having to change the unique caseid's to another column (keep it as the primary key column)

图片转代码服务由CSDN问答提供 功能建议

我有一个表,它使用主键(列名= caseid)存储支持案例的ID。

我现在有大约100,000个caseid,这个数字太大了。 我希望以某种方式从较低的数字开始,例如1000.

如何通过不必删除/存档现有记录而不必将唯一的caseid更改为另一个来实现这样的目标 column(将其保留为主键列)

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

3条回答 默认 最新

  • dongse5528 2015-11-27 11:58

    To reset Primary Key, you can follow below steps:

    1. Create temporary table with structure same as main table. Let's say table name is tbl_cases

      CREATE TABLE tbl_cases_tmp LIKE tbl_cases;
      ALTER TABLE tbl_cases_tmp ADD old_caseid int NOT NULL DEFAULT '0';
    2. DUMP all data from tbl_cases to tbl_cases_tmp. caseid will be stored in old_caseid column.

      INSERT INTO tbl_cases_tmp (name, summary, old_caseid)
      SELECT name, summary, caseid FROM tbl_cases;
    3. For any other tables having references to tbl_cases. Let's say tbl_reference

      UPDATE tbl_reference tr 
      JOIN tbl_cases_tmp tc
      ON tr.caseid = tc.old_caseid
      SET tr.caseid = tc.caseid;

    Before using Steps 4 and 5, ensure your tables tbl_cases_tmp and all references are properly updated.

    1. Drop tbl_cases

      DROP table tbl_cases;
    2. Rename tbl_cases_tmp to tbl_cases

      RENAME TABLE tbl_cases_tmp TO tbl_cases;
    解决 无用
    打赏 举报

相关推荐 更多相似问题