搜索&将数据库中的“http"替换为“https"

2022-01-05 00:00:00 sql mysql phpmyadmin

使用 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' 的行

this will only find row with '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:%';

相关文章