[ACCEPTED]-PHP: json_encode vs serialize for storing in a MySQL database?-serialization

JSON has one main advantage :

• compatibility with other languages than PHP.

PHP's serialize has one 7 main advantage :

• it's specifically designed to store PHP-based data -- most notably, it can store serialized objects, instance of classes, that will be re-instanciated to the right class-type when the string is unserialized.

(Yes, those advantages are the exact opposite of each other)

In your case, as you are 6 storing data that's not really structured, both 5 formats should work pretty well.

And the 4 encoding problem you have should not be 3 related to serialize by itself : as long 2 as everything (DB, connection to the DB, PHP files, ...) is in UTF-8, serialization 1 should work too.

The folks at FriendFeed opted for a similar 2 solution using JSON. You should check out 1 their blog post about it.

json_encode() converts non-ASCII characters and symbols 4 (e.g., “Schrödinger” becomes “Schr\u00f6dinger”) but 3 serialize() does not.

Source: https://www.toptal.com/php/10-most-common-mistakes-php-programmers-make#common-mistake-6--ignoring-unicodeutf-8-issues

To leave UTF-8 characters 2 untouched, you can use the option JSON_UNESCAPED_UNICODE as of 1 PHP 5.4.

Source: https://stackoverflow.com/a/804089/1438029

I think unless you absolutely need to preserve 19 php specific types that json_encode() is the way to go 18 for storing structured data in a single 17 field in MySQL. Here's why:

https://dev.mysql.com/doc/refman/5.7/en/json.html

As of MySQL 16 5.7.8, MySQL supports a native JSON data 15 type defined by RFC 7159 that enables efficient 14 access to data in JSON (JavaScript Object 13 Notation) documents

If you are using a version 12 of MySQL that supports the new JSON data 11 type you can benefit from that feature.

Another 10 important point of consideration is the 9 ability to perform changes on those JSON 8 strings. Suppose you have a url stored 7 in encoded strings all over your database. Wordpress 6 users who've ever tried to migrate an existing 5 database to a new domain name may sympathize 4 here. If it's serialized, it's going to 3 break things. If it's JSON you can simply 2 run a query using REPLACE() and everything will be 1 fine. Example:

$arr = ['url' => 'http://example.com'];
$ser = serialize($arr);
$jsn = json_encode($arr);

$ser = str_replace('http://','https://',$ser);
$jsn = str_replace('http://','https://',$jsn);

print_r(unserialize($ser));

PHP Notice:  unserialize(): Error at offset 39 of 43 bytes in /root/sandbox/encoding.php on line 10

print_r(json_decode($jsn,true));

Array ( [url] => https://example.com )

If the problem is (and I believe it is) in 8 UTF-8 encoding, there is not difference 7 between json_encode and serialize. Both will leave characters 6 encoding unchanged.

You should make sure 5 your database/connection is properly set 4 up for handle all UTF-8 characters or encode 3 whole record into supported encoding before 2 inserting to the DB.

Also please specify 1 what "I get an error" means.

Found this in the PHP docs...

function mb_unserialize($serial_str) { 
    $out = preg_replace('!s:(\d+):"(.*?)";!se', "'s:'.strlen('$2').':\"$2\";'", $serial_str ); 
    return unserialize($out); 
} 

I don't quite 5 understand it, but it worked to unserialize 4 the data that I couldn't unserialize before. Moved 3 to JSON now, i'll report in a couple of 2 weeks whether this solved the problem of 1 randomly getting some records "corrupted"

As I'm going through this I'll give my opinion, both 6 serialize and json_encode are good for storing 5 data in DB, but for those looking for performance, I've 4 tested and I get these results, json_encode 3 are a little microsegunds faster tham serialize, i 2 used this script to calculate a the difference 1 time.

$bounced =array();
for($i=count($bounced); $i<9999; ++$i)$bounced[$i]=$i;


$timeStart = microtime(true);
var_dump(serialize ($bounced));
unserialize(serialize ($bounced));
print timer_diff($timeStart) . " sec.\n";
$timeStart = microtime(true);
var_dump(json_encode ($bounced));
json_decode(json_encode ($bounced));
print timer_diff($timeStart) . " sec.\n";

function timer_diff($timeStart)
{
    return number_format(microtime(true) - $timeStart, 3);
}

As a design decision, I'd opt for storing 8 JSON because it can only represent a data 7 structure, whereas serialization is bound 6 to a PHP data object signature.

The advantages 5 I see are: * you are forced to separate 4 the data storage from any logic layer on 3 top. * you are independent from changes 2 to the data object class (say, for example, that 1 you want to add a field).