Help of auto_increment

[hjyn]

Hi, how can I add char to auto_increment? For example, the
auto_increment is for integer, I want to add a character at the front
of that number. Ex:

create table abs (UserID INT(3) NOT NULL AUTO_INCREMENT, name varchar(15),
primary key(UserID));insert into abs(name) VALUES ('Elton'), ('Andy'), ('Peter');
select * from abs;

The output is:
+ -------+--------+
| UserID | name |
+--------+--------+
| 1 | Elton |
| 2 | Andy |
| 3 | Peter |
+ -------+--------+

The output I want is:
+ -------+--------+
| UserID | name |
+--------+--------+
| U1 | Elton |
| U2 | Andy |
| U3 | Peter |
+ -------+--------+
Notice that I added 'U' at the front of the UserID, is it possible to
do that?
What is the command for that? Thanks
Best Regards,
Hau Jyn

[Bill Karwin]

hjyn wrote:

Hi, how can I add char to auto_increment?

As far as I can tell from the documentation, auto_increment can be
applied only to integer type columns.

If you want a 'U' at the start of the identifier, you can do it as an
expression in the select:

SELECT CONCAT('U', abs) AS abs, ...
FROM abs;

Other options might be to avoid the use of auto_increment, and provide a
unique primary key value manually. Or else define an integer field as
the auto_increment field, and then another field as the identifier with
the character prefix. Then, every time you insert a value to your
table, follow that operation with an update forcing the second
identifier column to have the value you describe:
UPDATE abs SET abs2 = CONCAT('U', abs);

Regards,
Bill K.

[hjyn]

Thanks Bill, your solution help me a lot.

Regards,
Hau Jyn