show year + auto increment as column register

Question

i have column register on table jadwal:

CREATE TABLE IF NOT EXISTS 'jadwal' ('register' int(5) zerofill NOT NULL AUTO_INCREMENT, PRIMARY KEY ('register')

i want to show register combine, this year + auto_increment like:

1800001
1800002
1800003

and reset become 1900001 when year is 2019

i try to add Y on mysql like CREATE TABLE IF NOT EXISTS jadwal ( register int(5) Y zerofill NOT NULL AUTO_INCREMENT, but not working

please help the source code and many thanks

Answer 1

Consider this:

• Use MEDIUMINT UNSIGNED
• Insert a bogus entry at midnight of Jan. 1 each year. Use the value 1900001 for the beginning of 2019.

(Or you could use 1900000 and delete it after another entry has been made.)

The alternative is a bit messier -- It would involve a Trigger that looks at YEAR(CURDATE()) to see if the 'next' id needs to be bumped up to the start of the next year.