auto increment with latters?

[php4om]

I would love it to be an auto INCREMENT, i.e. ord5001, ord5002, ord5003, ord5004, ord5005. how to do this in mysql ? 😕
bye

[AstroTeg]

MySQL's auto increment field is numeric only. So your choices would then be to build your own field. But you'll take a performance hit because searching on a numeric value of 5001 will be faster than searching on a text value of 'ord5001' (indexing will definitely help). The other problem with this is ID collisions. If 2 people on 2 different computers add 1 addtional record at the very same time, how will you code around the duplicate ID that was generated?

Your other option is to make it transparent in the MySQL database and add the 'ord' in the queries or PHP code you use. So you would use MySQL's auto increment field and add the 'ord' as needed:

SELECT CONTACT('ord', myIDfield) AS newID FROM mytable

With other database engines, you can combine keys together to create a new key. This is ideally what you'd want. But I don't think MySQL can do it directly. There might be a trick or two you could do, but I'm not aware of it.

[Sxooter]

In PostgreSQL you'd do this:

smarlowe=> create table test (id text primary key, info text);
CREATE TABLE
smarlowe=> create sequence test_seq;
CREATE SEQUENCE
smarlowe=> alter table test alter column id set default 'ord'||nextval('test_seq')::text;
ALTER TABLE

smarlowe=> insert into test (info) values ('this is order number 1, I think');
INSERT 25368 1
smarlowe=> insert into test (info) values ('this should be order number 2');
INSERT 25369 1
smarlowe=> select * from test;
id | info
------+---------------------------------
ord1 | this is order number 1, I think
ord2 | this should be order number 2
(2 rows)

Simple, eh? But who needs all that power 🙂