Can I insert binary data into MySQL TEXT column?

Question

Is it possible to insert binary data into MySQL TEXT column? I have a table that I'd like to use for storing both UTF-8 text and binary data (very basic structure - id int(11), type char(1), data text), but when I try to insert some binary data (JPEG image) into the data column, the column is empty.

I use mysql_query and mysql_real_escape_string in PHP to execute the INSERT query, both of them should be binary safe. Example code:

mysql_query("INSERT INTO my_table VALUES(" . (int)$id . ", 'b', '" . mysql_real_escape_string($jpegImageData) . "')");

I don't want to change the data column type to BLOB - in some cases, I need to use to compare / collate strings in this column.

Maybe you have some mysql exceptions? echo mysql_error($handle); — Aurimas Ličkus
– Aurimas Ličkus, Commented Oct 8, 2013 at 14:22
Have you considered the possibility that your binary data might just as well be invalid UTF-8, which will lead to problems/errors when you try to have a string with broken encoding stored in the database as UTF-8? If you're curious for those errors, please do try and let me know the result! =] — Code4R7
– Code4R7, Commented Jul 3, 2018 at 7:47

Vlad Ogay · Accepted Answer · 2013-10-08 13:45:23Z

1

Why not? Just encode in base64 and put it into that field.

<?php
$str = 'Some UTF-8 string or even a binary array';
echo base64_encode($str);
?>

answered Oct 8, 2013 at 13:45

Vlad Ogay

13311 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin M. Over a year ago

I mean, can I insert the binary data to text column without base64 encoding? Should it work or is this the reason why the column is empty?

Vlad Ogay Over a year ago

To be honest, never did that but I believe you can, because their core difference is just how mysql treats them, not how they are stored (which depends purely on storage engine).

Collectives™ on Stack Overflow

Can I insert binary data into MySQL TEXT column?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related