Storing Base64 PNG in MySQL

Question

I am using Sencha Touch to capture data from a user on an iPad. This includes a standard form (name, email, etc.) as well as the customer's signature (see the plugin here).

Essentially, the plugin takes the coordinates from the user's signature and gives me back Base64 PNG data.

Once I have the signature data, I want to store it. My two questions are:

1. Should I store the Base64 data in my (MySQL) database along with the rest of the user's information, or should I create a static file and link as necessary?

2. If storing in the database is the way to go, what data type should I use?

Answer 1

There's no need to base64 encode the image. MySQL's perfectly capable of storing binary data. Just make sure you use a 'blob' field type, and not 'text'. text fields are subject to character set translation, which could trash your .png data. blob fields are not translated.

As well, base64 encoding increases the size of text by around 35%, so you'd be wasting a large chunk of space for no benefit whatsoever.

However, it's generally a bad idea to store images in the database. You do have the advantage of the image being "right there" always, but makes for absolutely huge dumps at backup time and all kinds of fun trying to get the image out and displayed in your app/web page.

it's invariably better to store it externally in a file named after the record's primary key for ease of access/verfication.

Answer 2

Just save files in BLOB field. Such PNG file shouldn't be larger than 1KB if you turn some optimizations (grayscale or B/W).

Storing files outside DB seems easy but there are things to consider:

• backup,
• additional replication if multi-server
• security - access rights to files dir, but also to files,
• no transactions - e.g. DB insert ok but file write fails,
• need to distribute files within multiple directories to avoid large dir listings (depends on filesystem capabilities)