Autoincrement of table id using string combination in database automatically

Question

We know that id of any table is of integer type and auto-increment in that table is fine. But my problem is that i want to increment a combination of string and integer. For example String : ABC is the starting code for the unique id.

First Id of my table should be primary key and

ie ABC10001, ABC10002,..... and so on.

Without using another key as auto-increment. Please do not use two type of unique key.

Answer 1

You cannot do it in one column, unless you specify a BEFORE INSERT trigger which would prefill it, leaving it wholly up to you to avoid any kind of race conditions the auto_increment handles on it's own. auto_increment can only be used on integer or floating-point columns.

In my opinion, 'requiring' the PK the have a specific format, and 'just having it auto increment' are mutually exclusive.

That doesn't mean you cannot 'fake' it on selects:

SELECT CONCAT('ABC1',LPAD(id,5,'0')) FROM tablename;