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

mysql performance optimization
improve mysql performance large database
mysql performance tuning script
mysql performance tuning tutorial
mysql query optimization techniques
mysql views performance slow
mysql optimization
mysql tuning tools

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?

I think the backslash must be escaped in MySQL

field_name LIKE 'http:\\/\\/test01.mysite.com%'

Of course one could go for sure and use the single char wildcard __

field_name LIKE 'http:_/_/test01.mysite.com%'

or for your both cases: an optional backslash:

field_name LIKE 'http:%/%/test01.mysite.com%'

MySQL Tutorial: How to Improve Performance, MySQL stores data in tables on disk. Of course, there are exceptions, such as in-​memory temporary tables. And different storage engines  MySQL stored procedures, functions and triggers, are tempting constructs for application developers. However, as I discovered, there can be an impact on database performance when using MySQL stored routines.

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!

MySQL 8.0 Reference Manual :: 9.6 Comment Syntax, Nested comments are not supported, are deprecated, and will be removed in a future MySQL release. (Under some conditions, nested comments might be  -rw----- 1 root root 4000 Jun 3 03:38 mysql.preupgrade.apsc.17.5.3-17.8.11.20180603-033857.dump.gz Answer These files can be used to restore corrupted Plesk or Applications Catalog data in case of emergency, like a crash or failed server upgrade.

There is no reason, your query won't work if you have run properly, there is something else, you may be missing here.

UPDATE test1 SET name_1 = REPLACE(name_1, 'http:\/\/test01.mysite.com', 'https://test01.mysite.com')

works well and does the job of repalcing the \/ with /. See screen-shot attached,

You may have some other problem, please check and update the question, if so.

Edit after comments

If you have more data points in URL, change query like below.

UPDATE test1 SET name_1 = REPLACE(name_1, '\/', '/')

Above will replace all the occurrence of \/ with /.

MySQL Installation Guide :: 5.5 Troubleshooting a , Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist. These messages often occur when the MySQL base or data directories are  The MySQL for Excel plugin is automatically added to Microsoft Excel's data menu when it is installed. Look for the MySQL for Excel icon, by default it will be listed on the right side of the main menu.

As \\ did not work to represent/escape a backslash, use regular expression functions:

REGEXP_LIKE('.*http:\\/\\/test01\.mysite.com.*')
REGEXP_REPLACE(field, 'http:\\/\\/', 'http://')

Here \\ should work.

An Introduction to Queries in MySQL, In Structured Query Language (SQL), queries are almost always made MySQL is an open-source relational database management system. To connect to the MySQL server, you need to know the name of the computer on which the database is located and your MySQL account’s user ID and password. For most queries, you also need to know the name of the database with which you want to interact.

SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational , Databases are logically modelled clusters of information, or data. A database management system (DBMS), on the other hand, is a computer  Can you be certain that the same style query will respond with the same decisions about which tables are optional and which ones aren't (because of the mix of LEFT and RIGHT joins in the same query)? The only way to avoid this kind of "order-of-operations" dilemma is to use parentheses to group (nest) your joins so that they evaluate in the

4. Indexes - High Performance MySQL [Book], Multicolumn indexes. Like many relational database engines, MySQL allows you to create indexes that are composed of multiple columns: ALTER TABLE  Invisible Columns - This feature, which is available on MariaDB, while not on MySQL, allows creating columns which aren’t listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement when their name isn’t mentioned in the statement.

Top 10 MySQL Mistakes Made By PHP Developers, MyISAM doesn't support foreign key constraints or transactions, which are essential for data integrity. In addition, the whole table is locked  MySQL doesn’t have the concept of ORACLE_SID so what we have been doing is to use the PORT number to distinguish the multiple instances. For each instance you can check its defaults-file to get the data file path, and create a symbolic link according to your standard.

Comments
  • Fundamental question here should be: what for you store escaped data in your database?!
  • The fundamental question is as I asked it. I don't have any control over the program that is doing the inserts. My job is to deal with the data once it gets there.
  • I spoke too soon in my first comment, so I deleted it. Your second option does match when using SELECT/LIKE. However, it does not work when using UPDATE/REPLACE. Neither your first, nor third option works in either case. Thanks, though!
  • Thanks, but you haven't answered my question. My query is exactly the same as yours, other than you've used test1 and name_1 instead of table and field. So, if you have some suggestion as to why your version of that query is working and mine isn't, I'd appreciate more info.
  • Do run following query and see , how many records get selected. I feel you may not full long URL in database. ` select * from test1 where name_1,='http:\/\/test01.mysite.com'` , I feel it will return 0. I assumed you are running query on terminal.
  • I'm running the queries exactly as written above (with the actual field and table names) in PhpMyAdmin and the results are always zero. I cannot use "where name_1 = 'http:\/\/test01.mysite.com' because the field contains complete blog posts, not just the urls.
  • Perfect, now, you hit the actual problem, so you have to change your query, I'll update the answer, based on this comment, I believe it will work for you.
  • That, too, affects zero rows. I tested it on one record, but, even if it had worked, it would be very risky for me to run that query against all the records. There could be legit instance of '\/' in the data. If you're able to affect results, I guess that at least means it's not the queries themselves and must be something in the environment or configuration. ... Sorry if I sounded rude in my first response to you. I've got a migraine and didn't articulate well. I appreciate your efforts!
  • Thanks, but neither worked for me. I get a function does not exist error using REGEXP_LIKE. The REGEXP_REPLACE causes no errors, but affects 0 rows. But, I'd read that neither function worked in standard MySQL, only MariaDB, so, at least now I know that REGEXP_REPLACE does work. I did figure out my answer, but I appreciate your input. Thanks!