generate mysql ids as a string instead of a integer

Question

I am sure this is called something that I don't know the name of.

I want to generate ids like:

59AA307E-94C8-47D1-AA50-AAA7500F5B54

instead of the standard auto incremented number.

It doesn't have to be exactly like that, but would like a long unique string value for it.

Is there an easy way to do this?

I want to do it to reference attachments so they are not easily used, like attachment=1

I know there are ways around that, but I figure the string based id would be better if possible, and im sure I am just not searching for the right thing.

Thank you

score 3 · Answer 1 · answered Dec 01 '10 at 04:43

3

Last time I checked, you can't specify UUID() as the default constraint for a column in MySQL. That means using a trigger:

CREATE TRIGGER
newid
BEFORE INSERT ON your_table_name
FOR EACH ROW
SET NEW.id = UUID()

I know there are ways around that, but I figure the string based id would be better

I understand you're after the security by obscurity, but be aware that CHAR/VARCHAR columns larger than 4 characters take more space than INT does (1 byte). This will impact performance in retrieval and JOINs.

answered Dec 01 '10 at 04:43

OMG Ponies

325,700
82
523
502

Not that I said this in the question, but I am doing this in rails, and there is a plugin for rails for it, thank you for your answer. If I could select two answers I would select yours too. jcomeau_ictx beat you with the answer by 1 minute. – Toby Joiner Dec 01 '10 at 06:35

score 0 · Answer 2 · answered Dec 01 '10 at 05:16

You could always just pass the regular auto_increment values through SHA1() or MD5() whenever it comes time to send it out to the "public". With a decent salting string before/after the ID value, it'd be pretty much impossible to guess what the original number was. You wouldn't get a fancy looking string like a UUID, but you'd still have a regular integer ID value to deal with internally.

If you're worried about the extra cpu time involved in repeatedly hashing the column, you can always stored the hash value in a seperate field when the record's created.

generate mysql ids as a string instead of a integer

2 Answers2