How to search and replace "http" to "https" in MySQL

Learn how to match any http URL on some column in your database and replace it with https using a simple query.

If you are willing to search for some content and replace it with something in MySQL with a query, you may want to use the string replace function (fromt_str, to_strt) that returns the string str with all occurrences of the string from_str replaced by the string to_str. It performs a case-sensitive match when searching for from_str.

Replacing all http URLs with https

The query will update from the given table, the selected column and will search on the providen column the first value and replace it with the new one:

Note

Remember to test your query first, otherwise things may go wrong. Besides, you may want to run this query on some test sandbox and not in production.

/* 
    The simple query that you need to run to
    replace all strings that contains http:// with https://
    is the following:
*/
UPDATE `tableName` SET `columnName` = REPLACE(
    `tableName`.`columnName`,
    "http://",
    "https://"
)

Being carefully

As probably, the content of the column that you are working with contains more URLs (maybe third party URLs) e.g given the table articles:

id	title	content
1	Hello	<h1>This image is very bad</h1> <img src="http://ourcodeworld.com/some-path/imaginary.png" /> and the image of other website is <img src="http://es.ourcodeworld.com/hola/burritos.png" />
2	World	<a href="http://awebsitewithoutssl-that-you-may-want-not-touch.com/url">Hello</a>
3	Nothing	<a href="http://ourcodeworld.com/support-deadpool.php">Deadpool for president</a>

So remember to provide with exactitude the pattern of the URL that needs to be changed. For example, if es.ourcodeworld doesn't support HTTPS, then our change will make the images of that domain innacessible from the browser, so to replace only the URLs of ourcodeworld.com and not the es.ourcodeworld.com, the query would change to:

/* 
    Change only the URLs of Our Code World
*/
UPDATE `articles` SET `content` = REPLACE(
    `articles`.`content`,
    "http://ourcodeworld.com",
    "https://ourcodeworld.com"
)

Which would update only 2 rows, namely 1 and 3 with:

id	title	content
1	Hello	<h1>This image is very bad</h1> <img src="https://ourcodeworld.com/some-path/imaginary.png" /> and the image of other website is <img src="http://es.ourcodeworld.com/hola/burritos.png" />
2	World	<a href="http://awebsitewithoutssl.com/url">Hello</a>
3	Nothing	<a href="https://ourcodeworld.com/support-deadpool.php">Deadpool for president</a>