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)

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.

4. Drop tbl_cases

   DROP table tbl_cases;
   

5. Rename tbl_cases_tmp to tbl_cases

   RENAME TABLE tbl_cases_tmp TO tbl_cases;
   

What about find lowest id and subtract its Value from all ids?

Then you'll be able to re-set the id to a lower number

Edit:

This suppose that there are unused ids and no recods related to them

In case your problem is with conveying the caseid e.g. from the customer via phone to the help desk you might consider leaving the actual case id as-is but change the alphabet/set of digits when showing it.
E.g. switch from decimal to hexadecimal and you've increased the range of values that can be displayed as four digits/characters from 9999 to 65535 (hex:ffff).
Now consider a different set of digits like 3479ACEFHJKLMNPRTUVWXY* and the range of numbers that can be displayed using only four digits/characters increases quite a lot.

<?php
echo getCode(234255), "
"; // up until "here": four digits
echo getCode(234256), "
"; // ok, now it's five
echo getCode(5100000), "
"; // but stays five until > 5 millions

function toBase(/* positiv integer*/ $n, array $alphabet) {
    $retval = '';
    do {
        $retval = $alphabet[ $n%count($alphabet) ] . $retval;
        $n = intval( $n / count($alphabet) );
    }
    while( ($n=intval($n)) > 0);
    return $retval;
}

function getCode(/*int*/ $caseid) {
    static $alphabet = ['3','4','7','9','A','C','E','F','H','J','K','L','M','N','P','R','T','U','V','W','X','Y'];
    return toBase($caseid, $alphabet);
}

prints

YYYY
43333
YTYAA

*) an alphabet containing only unambiguous characters.