PHP: json_encode vs serialize for storing in a MySQL database?

JSON has one main advantage :

• compatibility with other languages than PHP.

PHP's serialize has one 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 storing data that's not really structured, both formats should work pretty well.

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

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

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

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

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

Another important point of consideration is the ability to perform changes on those JSON strings. Suppose you have a url stored in encoded strings all over your database. Wordpress users who've ever tried to migrate an existing database to a new domain name may sympathize here. If it's serialized, it's going to break things. If it's JSON you can simply run a query using REPLACE() and everything will be 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 )

json_encode() converts non-ASCII characters and symbols (e.g., “Schrödinger” becomes “Schr\u00f6dinger”) but 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 untouched, you can use the option JSON_UNESCAPED_UNICODE as of PHP 5.4.

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

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

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

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