How to add a series of string in incrementing id in any table?

Question

I have MySQL Table with an Order table in a DB this table has an auto-incremental id. Up-till now we have general numeric Order-ID likewise 1,2,3,4,5... From now onwards I have to append the series A20 to my id like A20103, A20104, A20105 and so on and when the last three digits reaches 999 the series appended should get changed to A21001, A21002, A21003 and so on, the same series has to be added in the previously added orders..

How can i achieve this task? please guide

Answer 1

something along the lines of:

"AR2" . str_pad((int) $ordernumber, 4, "0", STR_PAD_LEFT);

jim

[edit] - i'm assuming this is for display purposes as stated elsewhere, the ID field on the DB is integer!!

Answer 2

You can't have an auto-increment which is not a numeric field. You will better keep the current auto-incrementing column, and add a new one which you will compute manually according to your rules.

You'll probably want to use the MAX() function to get the latest order and generate the next value: remember to do it within a transaction.

You could create a function or a trigger, to do the insert cleanly for you.

Answer 3

You can't add prefixes directly to the database. However, when selecting it you can prepend it.

SELECT concat('A', id) as id FROM table

To get the effect of starting from 20000 you can set the auto increment starting value.

Answer 4

Altering an existing auto_increment column does not sound like a good idea - do you really have to do this? Instead, why not just modify your select query to return a suitably formatted id? By doing so, you maintain referential integrity, and you are also free to change the order id format at any time in the future, without having to update your database.

SELECT id, CONCAT('A2', LPAD(id, 4, '0')) AS order_id FROM <table>;

Example output:

+------+----------+
| id   | order_id |
+------+----------+
|    1 | A20001   |
|    2 | A20002   
...
|  999 | A20999   |
| 1000 | A21000   |
| 1001 | A21001   |
+------+----------+