我正在尝试使用Wildcards在MySQL中进行搜索和替换

I am in the process of migrating content from another CMS to Wordpress. My challenge is to fix all the internal broken URLs contained in the field in question is 'post_content'.

我正在将内容从另一个CMS迁移到Wordpress。我的挑战是修复相关字段中包含的所有内部损坏的URL是'post_content'。

In a nutshell i need to search integers in the broken URLs that were from the old CMS, and replace them with new values mapping to the correct Wordpress content IDs.

简而言之,我需要搜索来自旧CMS的损坏URL中的整数,并用映射到正确Wordpress内容ID的新值替换它们。

I have the old and new values mapped as integers in a table 'wp_postmeta' where 'post_id" are the new values and 'meta_value' are the old values:

我在表'wp_postmeta'中将旧值和新值映射为整数,其中'post_id'是新值,'meta_value'是旧值:

post_id | meta_key   | meta_value
13533   | content_id | 30
13534   | content_id | 3094 
13535   | content_id | 4066

This is the SQL Query i have written so far, and I feel it's really close - but i need to do a search for 'meta_value' using a wildcard otherwise it won't work. I'm not sure how to do it, and hoping for some tips.

这是我到目前为止编写的SQL查询,我觉得它非常接近 - 但我需要使用通配符搜索'meta_value',否则它将无效。我不知道该怎么做,并希望得到一些提示。

UPDATE wp_posts
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
SET post_content = REPLACE(post_content, wp_postmeta.meta_value , wp_postmeta.post_id)
WHERE meta_key='content_id'

Thanks in advance - i'm new to SQL and it's taken me a day to get this far! :-)

在此先感谢 - 我是SQL的新手,我花了一天时间才能做到这一点! :-)

1 个解决方案

#1

can you have a sample data of your meta_key ? I hope this helps

你有meta_key的示例数据吗?我希望这有帮助

I assume your meta_keys starts with something like this 3094

我假设你的meta_keys以类似于3094的东西开头

UPDATE wp_posts
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
SET post_content = REPLACE(post_content, wp_postmeta.meta_value , wp_postmeta.post_id)
WHERE meta_key LIKE '30%'

For more info refer to this link WILDCARDS

有关更多信息,请参阅此链接WILDCARDS

#1