MySQL | 主键需要重置

Question

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)

Answer 1

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;