Using binary UUIDs with Ruby (on Rails) and MySQL

01.md

Binary UUIDs are much more efficient in space and lookups compared to VARCHAR or CHAR UUIDs. To better exmplain how they work I have a few examples of raw sql and how that translates in a programming language like Ruby using the UUID tools module. All examples will reference the following database table and schema.

Table name: users

 id          :uuid(16)
 first_name  :string(255)
 last_name   :string(255)

02_insert_example.md

Generating UUIDs is the simple part. Converting them to binary and inserting them into MySQL is the hard part. Luckily there is a great Ruby gem called UUID Tools, that will help with UUID generation and insertion.

To generate a random UUID; not based on a namespace or timestamp of any kind. You can run the following:

uuid = UUIDTools::UUID.random_create

This will return a UUID object that looks like this:

#<UUID:0x3fccd2efd344 UUID:07390786-33be-4d86-9ae1-6f129ef514b5>

Now you can use your uuid object to print out the UUID string.

uuid.to_s #=> "07390786-33be-4d86-9ae1-6f129ef514b5"

Unfortunately you cannot insert this string as is into your database. MySQL will cut off the string after 16 characters and it won't be stored correctly for lookup later. To insert a binary UUID into the database you need to use another method to print out the binary version of the UUID. UUID Tools provides a quoted_id method which makes it easy to insert binary UUIDs into your database.

Calling the quoted_id method will produce the binary string needed for inserts and lookup.

uuid.quoted_id #=> "x'0739078633be4d869ae16f129ef514b5'"

Let's insert our user into the database using raw sql:

user = [
  "#{UUIDTools::UUID.random_create.quoted_id}",
  "John",
  "Smith"
]

sql = "INSERT INTO `users` (`id`, `first_name`, `last_name`) VALUES (#{user.join(', ')})"

ActiveRecord::Base.connection.execute(sql)