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则不会。这对你来说可能不是问题,但我只是想指出来。