用什么SQL查询做一个简单的查找和替换

保存URL的表是wp_options.您应该在使用您网站网址的列上进行更新:

 UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "siteurl"
UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "home"
 

我可能会丢失一些值,但是每当您再次执行此查找/替换过程时,您都会注意到应该更新的值和表并将它们添加到此脚本中.

WordPress Codex提供了有关如何更改网站URL的不错的指南,也许对您来说更方便:更改网站URL

The table where your URL is saved is wp_options. You should do an update on the columns that use the URL for your site:

UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "siteurl"
UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "home"

I might be missing some value, but whenever you do this find/replace process again, you can notice the values and tables that should be updated and add them to this script.

WordPress Codex has a nice guide on how to change a site URL, maybe that's even handier for you: Changing the Site URL

最适合做的选择,发布,发布内容和发布meta:

 UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');

UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
 

另请参见使用phpMyAdmin和MySQL搜索数据| Packt Publishing.并且搜索RegEx 是一个很好的WP插件,能够在所有帖子和页面中搜索并替换为Grep.

2015年6月16日更新::使用下句话中链接的工具要好得多,因为在数据库转储中使用上述的简单查找/替换操作会破坏序列化的数据./em>请参见interconnectit.com WordPress序列化的PHP搜索替换工具.这样,您就不会破坏序列化的数据,也不需要在帖子内容上运行RegEx,因为互连脚本会随处更改URL.我一直使用该工具将网站迁移到不同的域,或者只是从http到https进行全局更改,以强制不使用插件的SSL,并更改内容中的所有URL,以防止不安全的元素错误.

UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');

UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

Also see Searching Data using phpMyAdmin and MySQL | Packt Publishing. And Search RegEx is a good WP plugin to be able to search and replace with Grep through all posts and pages.

Update 6/16/2015: Using the tool linked in the next sentence is much better, as a simple find/replace as above in a database dump will break serialized data. See interconnectit.com WordPress Serialized PHP Search Replace Tool. This way, you don't break serialized data and won't need to run RegEx on post content, as the interconnect script changes URLs everywhere. I use that tool all the time to migrate sites to different domains, or to simply do a global change from http to https to force SSL without plugins and change all URLs in content to prevent insecure element errors.

这是我使用的出色的脚本,可与WP用于存储选项的序列化数组完美地结合使用.完成操作后,只需确保将其从远程服务器中删除即可,因为它存在巨大的安全风险.

https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

This is a great drop-in script that I use and it works beautifully with the serialized arrays that WP uses to store options. Just make sure to delete it from your remote server when you're done because it's a HUGE security risk.

https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

为此,我使用 WP-CLI ,因为我发现它最简单负责序列化数据.

wp search-replace 'http://example.dev' 'http://example.com' --skip-columns=guid

还有一个选项可以将您的更改写入SQL文件,而不是操作实际的数据库:

wp search-replace foo bar --export=database.sql

For this I use WP-CLI because I find it the easiest and it takes care of serialized data.

wp search-replace 'http://example.dev' 'http://example.com' --skip-columns=guid

There is also an option that writes your changes into an SQL file instead of manipulating the actual database:

wp search-replace foo bar --export=database.sql

您不必这样做,可以使用相对路径.

链接而不是subdomain.soemthing.com/image.jpg时-例如使用/image.jpg

像这样,您一开始就不会遇到问题.

否则,对于mysql更新语句,您可以使用

 update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);
 

you do not have to do this , you can use relative paths.

when you are linking something instead of subdomain.soemthing.com/image.jpg - use /image.jpg for example

like this you won't face the problem in the first place.

otherwise for a mysql update statement you can use

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

要更改我们经常需要的wordpress域,可能是使站点从localhost运行:这是更新查询的完整列表:

 UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');
UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_url = replace(link_url, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_image = replace(link_image, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_usermeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

/*UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl' OR option_name = 'widget_text' OR option_name = 'dashboard_widget_options';*/
UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com');
 

• 如果需要,我们还必须添加其他不是WP默认的表.

更新:搜索替换数据库版本3.1.0是面向开发人员的用户友好的前端工具,可让您执行数据库范围的搜索/替换操作,而不会损坏PHP序列化的字符串或对象.

To change the wordpress domain what we often need, may be to make the site live from localhost: This is a complete list of update queries:

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');
UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_url = replace(link_url, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_image = replace(link_image, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_usermeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

/*UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl' OR option_name = 'widget_text' OR option_name = 'dashboard_widget_options';*/
UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com');

• We have to also add other tables which are not default with WP if necessary.

UPDATE: Search Replace DB version 3.1.0 is a user-friendly, front-end tool for developers, that allows you to carry out database wide search/replace actions, that don't damage PHP serialized strings or objects.

实际上,您不必仅在主题中的wp_config和functions.php文件中进行一些调整即可使用SQL查询.在Wordpress Codex中查看此主题: https://codex.wordpress.org/Changing_The_Site_URL

Actually, you don't have to use a SQL query just some adjustments in wp_config and functions.php file in your theme. Check out this topic in Wordpress Codex: https://codex.wordpress.org/Changing_The_Site_URL