How to store binary data in MySQL

mysql binary data-storage
Maria Jahan picture Maria Jahan · Jun 11, 2012 · Viewed 18.3k times · Source

How do I store binary data in a MySQL database?

Answer

Eugen Rieck picture Eugen Rieck · Jun 11, 2012

This question is not so straight forward to answer, as it sounds: There are lots of different binary data usage patterns out there, each with their own caveats and pros and cons. Let me try to summarize:

  • Short pieces of binary data, such as password hashes, work very well by simply base64-encoding them and storing the resulting string as a VARCHAR
  • "Not-quite-binary" data, such as document snipplets with the occasional non-printable can be escaped and sored as a string
  • The BLOB datatype allows you to store arbitrary chunks of binary data, but I strongly recommend against using it: Store the data in a file, then store the path to the file in a String type. You gain nothing from storing binary data, that the DB doesn't "understand" in the DB.

Related questions

Inserting and selecting UUIDs as binary(16)

I don't understand why SELECT UUID(); Returns something like: 3f06af63-a93c-11e4-9797-00505690773f But if I insert it into a binary(16) field (the UUID() function) with for instance a BEFORE INSERT trigger and run a select, …

Read More
Varbinary vs Blob in MySQL

I have about 2k of raw binary data that I need to store in a table, but don't know whether to choose the Varbinary or Blob type. I have read through the descriptions in the MySQL docs but didn't find …

Read More
What effects does using a binary collation have?

While answering this question, I became uncertain about something that I didn't manage to find a sufficient answer to. What are the practical differences between using the binary utf8_bin and the case insensitive utf8_general_ci collations? I can …

Read More