MySQL-Update inserted auto increment primary key to custom value

Question

When I insert a new row, an auto increment ID will be saved like 1,2,3.. I need to custom it using a Trigger so it will be saved like 20171,20172,20173..

Seems odd, but ok. What do you want to 10th value in that series to be, 201710? — Alden W.
– Alden W., Commented May 30, 2017 at 23:45
Ok, gotcha. And now you may have a reason for needing that. I was just hoping to point out that the order of the index won't be grouped by years unless you use characters. Which you could do. — Alden W.
– Alden W., Commented May 30, 2017 at 23:49
Maybe I didn't get it, but I believe that can be done using a Trigger, right? — Mohammed Aladham
– Mohammed Aladham, Commented May 30, 2017 at 23:53

Alden W. · Accepted Answer · 2017-05-31 01:28:49Z

1

This trigger would do this.

CREATE DEFINER=`root`@`%` TRIGGER `test_before_insert` BEFORE INSERT ON `test` FOR EACH ROW BEGIN

SET NEW.id = (
    SELECT CONCAT(YEAR(CURDATE()),IFNULL(MAX(CAST(ids.id AS UNSIGNED))+1,1))
    FROM (
        SELECT RIGHT(t.id,LENGTH(t.id)-4) AS id
        FROM test t
        WHERE LEFT(t.id,4) = YEAR(CURDATE())
    ) ids
);

END

But there are many reason you would not want to. This will get exponentially more expensive as rows are inserted, and provides no viable sorting etc.

answered May 31, 2017 at 1:28

Alden W.

1,4491 gold badge13 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mohammed Aladham Over a year ago

Thanks very much, I will test it as soon as possible when I reach office

Collectives™ on Stack Overflow

MySQL-Update inserted auto increment primary key to custom value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related