重置MySQL中行的递增值

Question

I have a table with 10 rows. Each row has an id, name and a year. Each id has a value that is incremented by 3 from the previous value. For example, the first row has an id of 1, then the next has an id of 4 etc.

After deleting the first 9 rows, the next row I added instead of having an id of 4, it continued from 31.

How can I reset the value of the next added row to start from 4 (or the last value incremented by 3)?

Answer 1

Example of a FUNCTION that calculates the new AUTO_INCREMENT value for id field of a test table by a user defined step.

CREATE FUNCTION `test`(`step` INT) RETURNS int(11)
    NO SQL
BEGIN
IF `step`<1 THEN
SET `step` = 1;
END IF;
RETURN `step`+(SELECT MAX(`id`) FROM `test`);
END

You can use a function like that to calculate the new value for the id (AI) field when inserting new rows to the table. For example.

INSERT INTO TABLE `test` (`id`,`value`) VALUES ((SELECT `test`(4)),'yourvalue');