MySQL 从所有表中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3571894/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select from all tables
提问by dlaurent86
I have a lot of tables in my data base all with same structure. I want to select from all tables without having to list them all like so:
我的数据库中有很多表都具有相同的结构。我想从所有表中进行选择，而不必像这样列出它们：
SELECT name FROM table1,table2,table3,table4
And I tried but this doesn't work:
我试过了，但这不起作用：
SELECT name FROM *
Is there a way to select all tables in a database without listing each table in the query?
有没有办法在不列出查询中的每个表的情况下选择数据库中的所有表？
回答by Konstantin Likhter
i am working on a online file browser, each directory has its own table
我正在使用在线文件浏览器，每个目录都有自己的表
It is very unuseful due to one reason: when you have about 200 files (this situation is real, yeah?) you have about 200 tables. And if there are about thousand files in each directory.. etc. In some time you will either have slow processing while selecting from your database either have to buy more server resources.
由于一个原因，它非常无用：当您有大约 200 个文件（这种情况是真实的，是吗？）时，您有大约 200 个表。如果每个目录中有大约一千个文件……等等。在某些时候，您要么在从数据库中选择时处理缓慢，要么必须购买更多服务器资源。
I think you should change your database structure: just begin from adding parent_folder_idcolumn to your table, after this you can put all your rows (files and directories -- because directory is a file too -- here you can add typecolumn to determine this) into the one table.
我认为您应该更改数据库结构：从向parent_folder_id表中添加列开始，然后您可以将所有行（文件和目录 - 因为目录也是一个文件 - 在这里您可以添加type列来确定这一点）到一张桌子。
回答by VoodooChild
As far as I know there are no such wildcards to select from *all tables. I would recommend writing a viewand then call that viewinstead (it will save you writing out the names every time) – VoodooChild
据我所知，没有这样的通配符可以从 *all 表中选择。我建议写一个view然后调用它view（这样可以节省你每次写出的名字）——VoodooChild
回答by dlaurent86
I found a solution, but I would still like to know if there is a simpler way or a better solution.
我找到了一个解决方案，但我仍然想知道是否有更简单的方法或更好的解决方案。
But here's what I came up with:
但这是我想出的：
$tables = mysql_query("show tables");
$string = '';
while ($table_data = mysql_fetch_row($tables)){
$string.=$table_data[0].',';
}
$ALL_TABLES = substr($string,0,strlen($string)-1);
$sql="SELECT name FROM $ALL_TABLES ";
回答by Your Common Sense
That means you should not have a lot of tables with same structure at all.
But just one table with a field to distinguish different kinds of data, whatever it is.
这意味着您根本不应该有很多具有相同结构的表。
但是只有一个带有字段的表来区分不同类型的数据，不管它是什么。
Then select all would be no problem.
然后全选就没有问题了。
回答by sasfrog
Sounds like you want to UNION together each table, so you get the results as if they were one big table. You'll need to write out the query in full like
听起来您想将每个表联合在一起，因此您得到的结果就好像它们是一张大表一样。您需要完整地写出查询，例如
SELECT * FROM table1 UNION SELECT * FROM table2 UNION ... SELECT * FROM tableN
Copy & paste may be your friend here.
在这里复制和粘贴可能是您的朋友。
I'm curious as to why you have lots of different tables with the same structure?
我很好奇为什么你有很多具有相同结构的不同表？
回答by Amirhossein
You can generate SELECT by cursor like this code and find all result step by step in sql server:
您可以像此代码一样通过游标生成 SELECT 并在 sql server 中逐步查找所有结果：
--Author: Ah.Ghasemi
Declare @Select sysname;
DECLARE A CURSOR
FOR Select 'select ' + '*' + ' from ' + name
from sys.tables
--Where name like 'tbl%'
Order by name
OPEN A
FETCH NEXT FROM A INTO @Select
While (@@FETCH_STATUS <>-1)
Begin
exec sp_executesql @Select
FETCH NEXT FROM A INTO @Select;
End
close A
Deallocate A
Please let us know if the problem is not resolved.
如果问题没有解决，请告诉我们。
I hope you for the best
我希望你最好
