reset auto-increment each year with new prefix

Question

I have an auto incremented field in my table. It should reset every new year 01/01 and then the prefix part should increment. For example in 2016 my auto incremented field was A1,A2,A3..... etc but on 12:00 AM 2017 it should reset to B1 and go from there `(B1,B2,B3... etc), until Z when prefix should stop incrementing.

I created two tables... one for integer and one for varchar

CREATE TABLE t1
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE t2
(
  id VARCHAR(1) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
);

and am creating a trigger

DELIMITER $$
CREATE TRIGGER tg1
BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
  INSERT INTO t2 VALUES (NULL);
  SET NEW.id = CONCAT('A', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

but am not sure how to involve date.

My db is mysql