Toby Joiner
Toby Joiner

Reputation: 4376

generate mysql ids as a string instead of a integer

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

Upvotes: 0

Views: 646

Answers (2)

Marc B
Marc B

Reputation: 360742

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.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332641

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.

Upvotes: 3

Related Questions