MySQL – Why REPLACE Command Fails on JSON Encoded Strings

jsonMySQLreplaceWordpress

Say in my wordpress db I've got a post_content column containing strings like this:

href=\"http:\/\/dev.example.com\/example\/wp-content\/uploads\/2019\/07\/my.pdf\"

The above is a portion of string (placed inside a link placed inside a text) that comes from data saved by a plugin (acf) which uses json as data format.

Then I run

UPDATE wp_posts SET post_content = REPLACE(post_content,'http:\/\/dev.example.com\/example\/', 'https:\/\/www.example.com\/');

Well, that won't work. Zero rows affected and no errors. Why? What am I doing wrong?

That kind of code comes from acf plugin gutenberg block in wordpress, and that's because it's json encoded. But as far as I know, shoudl be trated just as any other string.. What's the matter?

To be more precise, here below I'm attaching an image from sequelpro and from cli to prove the backslashes are there (see comments).

enter image description here

Best Answer

As a primer it looks like the data is being stored with the escape character \ backslash to allow the WordPress server to automatically use the data in the engine itself.

You would have to double the back-slashes to find and replace the string.

You can verify this via dbfiddle.uk.

If you run your Search String against a MySQL 8.0 instance like this:

 select 'http:\/\/dev.example.com\/example\/'

... then you will receive this:

http://dev.example.com/example/

Reference: db<>fiddle for Question 255169 (db<>fiddle)

This isn't what is stored in the database and thus will no be found.

Solution

The solution is to double the (MySQL) escape character \ into:

select 'http:\\/\\/dev.example.com\\/example\\/'

...which returns this:

http:\/\/dev.example.com\/example\/

Reference: db<>fiddle for Question 255169 (db<>fiddle)

Related Question