使用多个表的结果查询数据库? [英] Query a database with results from multiple tables?

问题描述

周围也有一些类似的问题，但是它们并不是我要找的东西，因此，如果您认为这在其他地方得到了解答，请原谅我.

我基本上是在寻找一种简单的方法来做事情，因为我有4000多个表可以从中获取数据.从我之前的帖子中可以看到以下内容: mysql搜索表名的段

一般情况是，我有一个充满表的数据库，而我只希望其中的四分之一大约是4000个表.多亏了我的上一篇文章，我得到了各个表名的列表，但是我想要与它们一起提供的数据. 我知道对于一个人来说，我可以执行SELECT table1.*，table2.*;或类似的东西，但我不想经历全部4000左右. 它们都以相同的事物结尾，例如staff_name，manager_name，customer_name，这样我就可以使用

SHOW TABLES LIKE '%_name'

以查看我要在数据库中使用的表名.有人建议使用动态mysql，但我什至不知道从哪里开始.有什么建议吗?

解决方案

一般示例(在PHP中):

构造动态SQL或借助编程语言构建SQL查询的过程看起来像这样(例如，在PHP中):

$pdos = $pdo->query("SHOW TABLES LIKE '%_name'");
$tables = $pdos->fetchAll();

$query = 'SELECT * FROM '.implode(' UNION SELECT * FROM ');
$pdo->query($query);

fetchAll方法将返回一个数组，其中包含选定的每个表的名称.

implode($glue, $array)函数使用一个数组，并使用$glue参数连接该数组中的每个值-通常，您使用一个值数组，并使用$glue = ','内插它们以创建一个逗号分隔的值列表.

在我们的示例中，implode具有部分查询，如$glue，以便创建一个大的UNION JOIN查询.

最终的$query构建完成后，其外观应类似于:

SELECT * FROM table_1_name
    UNION
SELECT * FROM table_2_name
    UNION
SELECT * FROM table_3_name
    ....
    ....
    UNION
SELECT * FROM table_4000_name

结果应包含所有4000个表中的所有DISTINCT行.

特定示例(仅SQL格式):

SELECT    GROUP_CONCAT(
              CONCAT('select * from ', table_name)
              SEPARATOR ' union '
          )
    INTO  @my_variable
    FROM  information_schema.tables
    WHERE table_schema = 'dbname'
    AND   table_name LIKE '%_name';

PREPARE   my_statement FROM @my_variable;
EXECUTE   my_statement;

• 第一条语句将从information_schema数据库中获取所有表名；
• CONCAT函数在每个表名的前面加上一个'SELECT * FROM '字符串；
• GROUP_CONCAT可以完成implode用PHP完成的工作；

• INTO子句确保将值保存在名为my_variable;

的变量中

• PREPARE语句采用一个字符串值(例如您保存在my_variable中的字符串值)，并检查该值是否为SQL查询；

• EXECUTE语句采用准备好的语句"，然后执行....

@my_variable是一个临时变量，但只能是标量类型(varchar，int，date，datetime，binary，float，double等)，它不是一个数组.

GROUP_CONCAT函数是一个聚合函数"，这意味着它需要一个聚合值(与数组类似的概念-在我们的情况下是查询的结果集)并输出简单的字符串结果.

There are some similar questions around but they aren't quite what I'm looking for, so forgive me if you think this is answered elsewhere.

I am basically looking for an easy way to do things as I have over 4000 tables to get data from. This kind of follows on from my previous post: mysql search for segment of table name

The general situation is that I have a database filled with tables and I only want about a quarter of this which comes to around 4000 tables. I have a list of the individual table names thanks to my previous post, but I want the data that goes with them. I know that for an individual one I can do SELECT table1.*, table2.*; or something similar but I don't want to go through all 4000 or so. They all end with the same thing, e.g. staff_name, manager_name, customer_name so I can use

SHOW TABLES LIKE '%_name'

to see the table names that I want in the database. Someone suggested using dynamic mysql, but I don't even know where to start with that. Any suggestions?

解决方案

Generic example (in PHP):

Constructing dynamic SQL or building your SQL queries with the aid of a programming language would look like this (in PHP for ex.):

$pdos = $pdo->query("SHOW TABLES LIKE '%_name'");
$tables = $pdos->fetchAll();

$query = 'SELECT * FROM '.implode(' UNION SELECT * FROM ');
$pdo->query($query);

The fetchAll method will return an array containing the names of each table selected.

The implode($glue, $array) function takes an array and concatenates every value in the array using the $glue parameter - usually you take an array of values and implode them using $glue = ',' to create a coma separated list of values.

In our case the implode has a partial query as $glue in order to create one big UNION JOIN query.

Once the final $query is build it should look something like:

SELECT * FROM table_1_name
    UNION
SELECT * FROM table_2_name
    UNION
SELECT * FROM table_3_name
    ....
    ....
    UNION
SELECT * FROM table_4000_name

The result should contain all of the DISTINCT rows from all 4000 tables.

Specific example (in SQL-only format):

SELECT    GROUP_CONCAT(
              CONCAT('select * from ', table_name)
              SEPARATOR ' union '
          )
    INTO  @my_variable
    FROM  information_schema.tables
    WHERE table_schema = 'dbname'
    AND   table_name LIKE '%_name';

PREPARE   my_statement FROM @my_variable;
EXECUTE   my_statement;

• The first statement will get all of the table names from the information_schema database;
• The CONCAT function prefixes every table name with a a 'SELECT * FROM ' string;
• The GROUP_CONCAT does the job that implode would have done in PHP;

• The INTO clause makes sure the values are saved inside a variable named my_variable;

• The PREPARE statement takes a string value (such as the one you saved in my_variable) and checks if the value is an SQL query;

• The EXECUTE statement takes a "prepared statement" and well... executes it.

@my_variable is a temporary variable but it can only be of a scalar type (varchar, int, date, datetime, binary, float, double etc.) it is not an array.

The GROUP_CONCAT function is an "aggregate function" which means it takes an aggregate value (similar concept to an array - in our case the result set of our query) and outputs a simple string result.

这篇关于使用多个表的结果查询数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！