如何在Wordpress中针对多个表运行查询

时间:2020-12-27 00:51:01

This query does what I need, return a list of data from a widget in several tables from a Wordpress Multi Site database.

此查询执行我需要的操作,从Wordpress多站点数据库的多个表中的窗口小部件返回数据列表。

There must be an easier way to do this. I have 30 tables I need to include, how can I get some type of loop to just return option value from all wp_n_option tables?

必须有一种更简单的方法来做到这一点。我需要包含30个表,如何从所有wp_n_option表返回选项值的某种类型的循环?

SELECT option_value
FROM `wp_options`
WHERE option_name = 'widget_thin_search'
UNION
SELECT option_value
FROM `wp_3_options`
WHERE option_name = 'widget_thin_search'
UNION
SELECT option_value
FROM `wp_4_options`
WHERE option_name = 'widget_thins_search'
INTO OUTFILE '/tmp/result.csv'

Edit: As Brandon pointed out, if it was a static 30 tables, I could build the query. However, the tables will increase as time goes on.

编辑:正如Brandon所指出的,如果它是一个静态的30个表,我可以构建查询。但是,随着时间的推移,表格会增加。

1 个解决方案

#1


2  

You could create a table with one column containing table names. Then create a T-SQL proc to loop through those table names and construct a query string resembling what you have in your example. Then run that query string with the exec command.

您可以创建一个包含表名的列的表。然后创建一个T-SQL proc来循环遍历这些表名,并构造一个类似于你的例子中的查询字符串。然后使用exec命令运行该查询字符串。

Just note that UNION removes duplicates whereas UNION ALL does not. That may not be an issue for you but I just wanted to point it out.

请注意,UNION会删除重复项,而UNION ALL则不会。这对你来说可能不是问题,但我只是想指出来。

#1


2  

You could create a table with one column containing table names. Then create a T-SQL proc to loop through those table names and construct a query string resembling what you have in your example. Then run that query string with the exec command.

您可以创建一个包含表名的列的表。然后创建一个T-SQL proc来循环遍历这些表名,并构造一个类似于你的例子中的查询字符串。然后使用exec命令运行该查询字符串。

Just note that UNION removes duplicates whereas UNION ALL does not. That may not be an issue for you but I just wanted to point it out.

请注意,UNION会删除重复项,而UNION ALL则不会。这对你来说可能不是问题,但我只是想指出来。