how to insert auto-increment varchar value in mysql

mysql insert a concat with convert (varchar to int)+1

problem:

In the mysql DB, there is a VARCHAR field with values something like this

AB123

AB124

AB999

AB1234

I strip off the "AB, convert to integer and add one.

I really want the max value if the resulting integer, and this works so far.

It returns the number 1235.

SELECT MAX( CONVERT( REPLACE( myfield, 'AB', '' ) , UNSIGNED ) ) +1 FROM mytable

Now, what I really want to do is to INSERT back into mytable the result with the "AB" at the start of the result.

For the above, I want to insert myfield= 'AB1235' into mytable.

Solution:

Sub-selects can only go in one place -- immediately after the list of fields. So, this would need to be written something like (untested):

• INSERT INTO mytable (myfield) SELECT concat('AB',MAX( CONVERT( REPLACE( myfield, 'AB', '' ) , UNSIGNED ) ) +1) FROM mytable

the sub-select can only go immediately after the list of fields. In particular, it can't appear within another expression. In other words, you can only have "INSERT INTO ... VALUES ..." or "INSERT INTO ... SELECT ...", but not some combination of the two. However, you can select a constant:

• INSERT INTO mytable (myfield1,myfield2) SELECT concat('AB',MAX( CONVERT( REPLACE(myfield1, 'AB', '' ),UNSIGNED))+1), 'sdfgr45t78ahlu' FROM mytable;