MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE example1 (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  name VARCHAR(10)
);
INSERT INTO example1 (name) VALUES ('Max'), ('John')
SELECT *, CONCAT_WS('-', RIGHT(YEAR(created_at), 2), LPAD(id, 3, 0)) AS custom_id 
FROM example1


id
created_at
name
custom_id


1
2019-12-18 13:36:14
Max
19-001

2
2019-12-18 13:36:14
John
19-002


CREATE VIEW v_example1 AS
  SELECT *, CONCAT_WS('-', RIGHT(YEAR(created_at), 2), LPAD(id, 3, 0)) AS custom_id 
  FROM example1
SELECT * FROM v_example1


id
created_at
name
custom_id


1
2019-12-18 13:36:14
Max
19-001

2
2019-12-18 13:36:14
John
19-002


SELECT *, CONCAT_WS('-', RIGHT(YEAR(created_at), 2), LPAD(ROW_NUMBER() OVER (PARTITION BY YEAR(created_at) ORDER BY id), 3, 0)) AS custom_id 
FROM example1


id
created_at
name
custom_id


1
2019-12-18 13:36:14
Max
19-001

2
2019-12-18 13:36:14
John
19-002