MySQL: Why aren't url's matching when using REPLACE?

Question

My Situation:

I have url's in a field containing blog posts. The url's are being stored in my database with escape characters. My task at the moment is to replace some already inserted 'http' url's with 'https' url's, but REPLACE will match neither the original url nor the escaped url. I can't just replace every instance of 'http:', because I only want to affect certain links in each post, not every link.

I am very familiar with SQL, as well as REPLACE, so I'm not just asking how REPLACE works and how to use it. Another user here has tested my queries in his environment and they work. So, there must be something in my configuration that is preventing the queries from functioning as expected.

I have searched this site and google extensively for several hours and have found nothing specifically addressing my issue. Everything I have tried is included below and if there is something else I should try, I don't know what that is and I haven't found any suggestions/posts/comments that suggest doing anything differently.

Example URL:

http://test01.mysite.com

As Stored in DB:

http:\/\/test01.mysite.com

Code to Re-Create Situation:

DROP TABLE IF EXISTS test_posts;
CREATE TABLE IF NOT EXISTS test_posts (
    id int NOT NULL AUTO_INCREMENT,
    post_content longtext NOT NULL,
    PRIMARY KEY (id)
)

INSERT INTO 
    test_posts
        (post_content)
    VALUES 
        ('content content content <a href="http:\\/\\/test01.mysite.com">Link I want to change</a> content content content <a href="http:\\/\\/someothersite.com">Link I don\'t want to change</a> content content content <a href="http:\\/\\/test01.mysite.com">Link I want to change</a> content content content <a href="http:\\/\\/someothersite.com">Link I don\'t want to change</a>');

If I run

UPDATE
    test_posts
SET
    post_content = REPLACE(post_content, 'http://test01.mysite.com', 'https://test01.mysite.com');

or

UPDATE
    test_posts
SET
    post_content = REPLACE(post_content, 'http:\/\/test01.mysite.com', 'https://test01.mysite.com');

zero records are affected.

For testing purposes, I ran the following query which returns 0 rows.

SELECT
    *
FROM
    test_posts
WHERE
    post_content LIKE '%http://test01.mysite.com%'
    OR
    post_content LIKE '%http:\/\/test01.mysite.com%'
    OR
    post_content LIKE '%http:\\/\\/test01.mysite.com%'
    OR
    post_content LIKE 'http:%/%/test01.mysite.com%';

If I run:

SELECT 
    *
FROM
    test_posts 
WHERE 
    post_content LIKE '%http:_/_/test01.mysite.com%'

It does return matches, but that doesn't solve the real problem of how to match when using UPDATE/REPLACE.

I have tried on two different servers and I get the same results on both.

I have tried the following Engine/Collation combinations and all return the same 0 records results:

MyISAM/latin1_swedish_ci

MyISAM/utf8mb4_unicode_ci

InnoDB/latin1_swedish_ci

InnoDB/utf8mb4_unicode_ci

Anybody know how I can write these queries so that REPLACE will find matches to those url's or what settings in my database or PhpMyAdmin may be causing the queries to return/affect 0 rows?

Answer 1

I'm still baffled as to why the queries with LIKE won't work, but, sadly, using those to narrow down the problem clouded my judgement and I didn't try all the same combinations in the REPLACE functions.

The following works:

UPDATE
    test_posts
SET 
    post_content = REPLACE(post_content, 'http:\\/\\/test01.mysite.com', 'https://test01.mysite.com');

If anyone can explain to me why these combinations work with REPLACE, but not with LIKE, I'd really love to know. Thanks!