0

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?

|improve this question
  • The BINARY and VARBINARY Types – Akina Mar 27 at 11:54
  • I used binary type but the insertion always fails, this is really mysterious. Thank you for the feedback – Begueradj Mar 27 at 12:15
  • 1
    I used binary type but the insertion always fails You must show both table's DDL and the code which you use for insertion. Edit your question. – Akina Mar 27 at 12:18
  • I have posted the related code on StackOverflow so that this does not look like cross posting: stackoverflow.com/questions/55375522/… – Begueradj Mar 27 at 12:25
  • No problems, wait until someone helps you on stackoverflow. – Akina Mar 27 at 12:28
1

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.

|improve this answer
  • If your binary data is over 255 bytes use some of BLOB datatypes. Select proper datatype according to max possible size of your data. See String Type Storage Requirements. It seems MEDIUMBLOB (16Mb max) is enough in your case... – Akina Mar 27 at 12:54
  • @Begueradj It seems the only variant for you is to create text type column (something like table.text("photo", "MEDIUMTEXT")), serialize your data during insertion on the client side, and de-serialise back during selection (for example, there exists atob() and btoa() functions). – Akina Mar 27 at 13:08
  • I tested what it comes to storing the data. I will investigate later more about the serialization and desirialization, I should have kissed you if you were here :) Thank you very much. I do not understand why someone downvoted your effort to help me – Begueradj Mar 27 at 13:22
  • And it is amazing what I found here: it says longtext is the same as longblob. I am really happy for this because I wanted to see if I can create a blob type in Knex.js using the specificType method. Thank you a lot again – Begueradj Mar 27 at 13:30
  • 1
    @Begueradj - Is this the "same" that you are referring to? "These correspond to the four BLOB types and have the same maximum lengths and storage requirements." – Rick James Apr 18 at 5:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.