dongnaopa6200
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;
    评论
    解决 无用
    打赏 举报
查看更多回答(2条)

报告相同问题？

提交

相关推荐 更多相似问题