MySQL – Hexadecimal Data and Corresponding Field Type

MySQLnode.js

I used some Node.js code to stream a file image before inserting it to the database. I have a problem inserting the data into the related MySQL table field. So I run console.log(streamedFileContent) on the server to see what is this data about and I got this:

<Buffer 89 50 4e 47 0d 0a 1a 0a 00 00 00 0d 49 48 44 52 00 00 03 9e 00 00 03 52 08 06 00 00 00 0d a5 11 28 00 00 00 04 73 42 49 54 08 08 08 08 7\
c 08 64 88 00 ... 150808 more bytes>

What should be the appropriate MySQL field type for this data?

Best Answer

SQL is textual language. So you must convert your binary data to its textual representation (solid hexadecimal codes, for example, 'MySQL' -> '4D7953514C') and use it while building query text or attaching text parameter, and use UNHEX() function over this value in query text for to reconstruct binary value on the server side.

Avoid exceeding the max_packet_size value for your query text, increase it if needed in MySQL configuration file. Remember - textual representation is 2 times longer than binary one.

If the file which you want to insert into the table is local for your MySQL server you can use LOAD_FILE() function.

Related Solutions

Mysql – Data type for currency ‘type’ column, e.g. usd, cat, etc.

3 letter ISO currency codes are relatively small, so there is little overhead in using them as the key for the reference table. Having certain items such as dates, GL codes and other analysis codes and currency codes directly on the tables is quite convenient for people reporting off the transactions and balances. Many accounting packages - even big players like Oracle Financials - do it for this reason.

Mysql – How to speed up a loading data into MySQL from a zipped archive

Please keep in mind the behavior a mysqldump

It dumps all databases in alphabetical order
It dumps all tables in each database in alphabetical order
It loads each tables like this
- Drops the table
- Creates the Table
- Table is Loaded
  1. Disables Keys
  2. Locks the Table for Writes
  3. Multiple Exteneded INSERTs
  4. Unlock the Table
  5. Enable Keys
- When the keys of table are disabled, only PRIMARY KEYs and UNIQUE KEYs are reloaded. When keys are enabled, then all non-unique keys are built linearly.

I would recommend you add this to /etc/my.cnf setting bulk_insert_buffer_size as follows

[mysqld]
bulk_insert_buffer_size=256M
key_buffer_size=1G

because the default is 8M. You do not need to restart mysql. Just run this SQL command

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Data type for currency ‘type’ column, e.g. usd, cat, etc.

Mysql – How to speed up a loading data into MySQL from a zipped archive

Related Question