Search code examples
mysqldata-modelingauto-increment

help creating a custom auto increment function in mysql

I would like to create an auto increment function that allows me to maintain uniqueness in designated columns across multiple tables.

I understand that this is not supported under mysql's standard functions, but it appears that this can be done by hand. Could someone point me in the right direction?

Thanks

Solution

  • You have a few choices. One, you could make the id a UUID (a 36+ varchar field), and use MySQL's UUID() function. Another option is to use a table as kind of a sequence generating table, allowing you to use the auto_increment features, and just use whatever is generated when instering into this table as a key elsewhere.

    Personally, I'd opt for a UUID as that's a pretty common way to have globally unique identifiers. A word of warning: you may be better off generating these UID's in your client's language instead of using MySQL's implementation.