搜索和将数据库中的"http"替换为"https" [英] Search & replace 'http' to 'https' in database

问题描述

使用 phpmyadmin ，我想运行一个查询，该查询将在我的整个数据库中搜索:

Using phpmyadmin, I want to run a query that will search my entire database for:

http://example.com

并替换为:

https://example.com

我的SQL知识很有限，也许像这样:

My SQL knowledge is limited, maybe something like:

UPDATE ?? = REPLACE(??, 'http://example.com', 'https://example.com');

数据库超过1gb，所以我可以运行什么不会使服务器崩溃.

The database is over 1gb, so what can I run that will not crash the server.

更新:请注意，尽管在SO上发布了其他一些有关搜索和替换的答案，但它们似乎并没有覆盖整个数据库.

Update: Note that while there are other answers posted here on SO that deals with search and replace, they don't seem to cover the entire database.

推荐答案

使用REPLACE.如果该字段上有索引，则UPDATE可以使用它们

use REPLACE. and if there is a index on the field then the UPDATE can use them

UPDATE t
     set url = REPLACE(url, 'http:', 'https:')
     WHERE url LIKE '%http:%';

仅更改example.com

这只会找到带有' http://example.com '

UPDATE t
     set url = REPLACE(url, 'http:', 'https:')
     WHERE url LIKE '%http://example.com%';

否则它将查找所有带有http://的行，但仅更改此 http://example.com 到 https://example.com

or this will find all rows with http:// but only change only this http://example.com to https://example.com

UPDATE t
     set url = REPLACE(url, 'http://example.com', 'https://example.com')
     WHERE url LIKE '%http:%';

这篇关于搜索和将数据库中的"http"替换为"https"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！