mysql - How to Auto Increment Part of Varchar -

i trying create query auto increment varchar column. number values date value, highlighted, numbers i'm trying increment 7. there way auto increment number/date value within number column?

from

select * table_name number = 'abc**070516**a001' 

to

select * table_name number = 'abc**071216**a001' 

well, in queries not incrementing anything. making 2 selects. if wanted change value of varchar column auto increment it's not possible. @ least not out lot's , lot's of code.

the simplest solution create 3 separate columns , make third 1 auto increment.

col1     col2    col3 abc      070516  a001 abc      071216  a001 

then concatenate columns @ time of retrieval/display.