I need to store:
array(1,2,3,4,5);
into a mysql blob.
How can i convert this array into binary data?
3
-
1Your question shows little effort.Dan– Dan2015-11-04 16:23:27 +00:00Commented Nov 4, 2015 at 16:23
-
@dan08 If you check the revisions, you will see that his questions is not how to store a blob file, but rather why/how should a larger ammount of information be stored...Bonatti– Bonatti2015-11-04 16:44:32 +00:00Commented Nov 4, 2015 at 16:44
-
The answer is simple, you use pack(). Where it become more difficult is determining how to format each value. Being an 'id' each value will likely be unsigned since they likely start at 0 and count up. But what is the exact type that mysql is expecting? Are they uint32? uint64?. This is where this question is lacking. But it is a very good question, I'm surprised of the downvotes.JSON– JSON2022-11-06 16:27:44 +00:00Commented Nov 6, 2022 at 16:27
Add a comment
|
2 Answers
It depends mostly on how you are using those informations. IDs are usually used to identify a resource, and thus must be unique, not null and indexable.
By those standarts do not use as blob.
Mostly because search by content is slower than as native variable. Also, SQL databases sort the content of a table to ensure faster queries.
If what you need is just storing information and then using another ID to identify this resource (and they can be easily parsed to strings/numbers then do not use blob). A binary file will usually use 8 bytes per char. A number could contain the same information using less total memory. Example, 1902334123 (random keyboard smash) uses 10*8 = 80 bytes in Hard disk, while an 32-bit signed integer could hold it.
Finally, if what you need is just storing several data units, what is your problem with a sequential varchar to be read as string, as it could solve your problem
2 Comments
wooper clooper
You brought up some good points regarding memory that i was not considering. I am not particularly storing values as a json_encoding because it looks sloppy but it seems to be you are right when it comes to the memory consumption.
Bonatti
Not only memory consumption (since a badly used encode could use that or more when storing VARCHAR), but you loose the native mapping indexes and algorithm that allows massive queries with loads of possible units to sort and respond as little information as possible. Imagine a 10 thousand alphabetical sorted sentences... if you would like to "query * that starts with x", and at line 0 to 500 starts with v, you dont even need to look at them.. but if you had files, you would need to open an read each one of them
you can convert to JSON and store to db:
json_encode($array);
and when you return from db:
json_decode($array);
2 Comments
JSON
The question is specifically how to convert a php array to binary. There's a thousand "other answers" to storing information in a database, each belonging to other questions.
JSON
And truly converting an array of primitive types to JSON when they can be converted to binary is both lazy and costly.