How to use UUIDs instead of integers in MySQL DB

Question

I would like to use python's uuid() function to assign my MySQL id's instead of just an integer and AUTOINCREMENT.

However, it would also be nice if that uuid() was generated when an object is created. I've not worked extensively with SQL before. So, the only way I can see to do this is when a new object is created in the python code, run uuid() and just assign it manually, but that seems unnecessary.

Is there a way to integrate this into the MySQL DB?

If so, what datatype do I assign to that column? varchar?

Why is this tagged Python? Do you want to do this in Python? If so, which module are you using? MySQLdb? — Ben, Jan 13 '14 at 22:08
It's tagged python because `uuid` is the python module I'd like to use in my code. And I'm using Flask-SQLAlchemy, is that a factor? — , Jan 13 '14 at 22:11
You can create a custom UUID sqlalchemy column type as suggested [here](http://stackoverflow.com/questions/183042/how-can-i-use-uuids-in-sqlalchemy). — alecxe, Jan 13 '14 at 22:25
Why do you want to use a UUID? If the ID is unique within the application what does it matter how long or globally unique it is? — , Jan 13 '14 at 23:10
Not completely, but it sounds like it could become a security concern later on if I'm passing them to and from a site/mobile app via API — , Jan 13 '14 at 23:13
If security is your concern, then you should know that rule one of security is to use a library. If you try and do it yourself you'll mess it up. — will, Jan 14 '14 at 00:04
Wouldn't using the uuid library suffice? Or do you mean something different? — , Jan 14 '14 at 19:09
Why use Python's uuid? Just add [MySQL's `UUID()`](http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid) to a `BEFORE INSERT` trigger if your primary key field is empty. — Wrikken, Jan 14 '14 at 20:54

score 3 · Accepted Answer · edited Sep 13 '22 at 20:18

MySQL does not have real UUID support – you can store UUIDs in CHAR(32) columns, although your indexes probably won't like it.

The SQLAlchemy docs provide the following recipe for using Python's uuid with any database:

from sqlalchemy.types import TypeDecorator, CHAR
from sqlalchemy.dialects.postgresql import UUID
import uuid

class GUID(TypeDecorator):
    """Platform-independent GUID type.

    Uses Postgresql's UUID type, otherwise uses
    CHAR(32), storing as stringified hex values.

    """
    impl = CHAR

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(32))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == 'postgresql':
            return str(value)
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value)
            else:
                # hexstring
                return "%.32x" % value

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(value)

By using this snippet, you will also keep the possibility open to switch to Postgres later on, a database which does have native UUID support.

As for initializing objects: you are right to assign a new uuid.uuid4() when you create a new object; the database (especially a database without UUID support) can't do that for you.

How to use UUIDs instead of integers in MySQL DB

1 Answers1