mysql 通配符(在搜索中忽略一个/两个字符) [英] mysql wildcard (ignore one/two characters in search)

问题描述

我正在尝试更新我的 wordpress mu 数据库中的表.我想更新所有 wp_options 表.这些表的名称如下:

I'm trying to update tables in my wordpress mu database. I want to update all wp_options tables. These tables are named like this:

• wp_1_options
• wp_2_options
• ...等等.

我如何使用 wp_any-character-here_options 影响所有表?我试图查询:

How do i affect all tables with wp_any-character-here_options? I tried to query for:

UPDATE wp_%_options 
   SET option_value = replace(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') 
 WHERE option_name = 'home' OR option_name = 'siteurl';

...但是 mysql 抱怨我有语法错误...

...but mysql complains i have error in syntax...

推荐答案

你不能通配 UPDATE 语句 - 你必须为每个表编写一个 UPDATE 语句.

You can't wildcard UPDATE statements - you have to write an UPDATE statement for each table.

未经测试:

CREATE PROCEDURE cleanup()
BEGIN

  DECLARE i INT DEFAULT 1;

  PREPARE stmt FROM "UPDATE ? 
                        SET option_value = REPLACE(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') 
                      WHERE option_name IN ('home', 'siteurl')"

  WHILE i <= 10
    EXECUTE stmt USING CONCAT('wp_', i, '_options');

    SET i = i + 1;
  END WHILE;

  DEALLOCATE PREPARE stmt;

END;

这篇关于mysql 通配符(在搜索中忽略一个/两个字符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！