从所有表中选择 - MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14931001/
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 - MySQL
提问by Farid-ur-Rahman
I have a mySQL database called listDBthat contain several tables with column name Productetc. I want to SELECTfrom all tables where Product Like %XYZ%, and display the search result in a separate table.
我有一个名为 mySQL 的数据库listDB，其中包含几个带有列名Product等的表。我想SELECT从 Product Like %XYZ% 的所有表中提取，并在单独的表中显示搜索结果。
I tried this:
我试过这个：
SELECT * FROM * WHERE Product LIKE %XYZ%
But it is not working. What is the right query for this purpose?
但它不起作用。为此目的正确的查询是什么？
回答by CloudyMarble
You get all tables containing the column product using this statment:
您可以使用此语句获取包含列 product 的所有表：
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('Product')
AND TABLE_SCHEMA='YourDatabase';
Then you have to run a cursoron these tables so you select eachtime:
然后您必须在这些表上运行光标，以便您每次选择：
Select * from OneTable where product like '%XYZ%'
The results should be entered into a 3rd table or view, take a look here.
结果应该输入到第三个表或视图中，看看这里。
Notice: This can work only if the structure of all table is similar, otherwise aou will have to see which columns are united for all these tables and create your result table / View to contain only these columns.
注意：这只有在所有表的结构相似时才有效，否则 aou 将不得不查看所有这些表的哪些列是统一的，并创建您的结果表/视图以仅包含这些列。
回答by Amiruddin Ayaz
SELECT product FROM Your_table_name WHERE Product LIKE '%XYZ%';
The above statement will show result from a single table. If you want to add more tables then simply use the UNION statement.
上面的语句将显示来自单个表的结果。如果要添加更多表，只需使用 UNION 语句即可。
SELECT product FROM Table_name_1
WHERE Product LIKE '%XYZ%'
UNION
SELECT product FROM Table_name_2
WHERE Product LIKE '%XYZ%'
UNION
SELECT product FROM Table_name_3
WHERE Product LIKE '%XYZ%'
... and so on
... 等等
回答by iiro
You can get all tables that has column "Product" from information_Schema.columns
您可以从 information_Schema.columns 中获取所有包含“Product”列的表
SELECT DISTINCT table_name FROM information_schema.columns WHERE column_name ="Product";
Nor create a procedure
也不创建程序
delimiter //
CREATE PROCEDURE curdemo()
BEGIN
DECLARE a varchar(100);
DECLARE cur1 CURSOR FOR SELECT DISTINCT table_name FROM information_schema.columns WHERE column_name ="Product";
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO a;
SELECT * FROM a;
END LOOP;
CLOSE cur1;
END;
delimiter ;
call curdemo();
回答by user2192370
why you dont just dump the mysql database but with extension when you run without --single-transaction you will interrupt the connection to other clients:
为什么您不只是转储 mysql 数据库，而是在没有 --single-transaction 的情况下运行时使用扩展名，您将中断与其他客户端的连接：
mysqldump --host=hostname.de --port=0000 --user=username --password=password --single-transaction --skip-add-locks --skip-lock-tables --default-character-set=utf8 datenbankname > mysqlDBBackup.sql
after that read out the file and search for what you want.... in Strings.....
之后读出文件并搜索你想要的......在字符串中......
回答by Jens
As Suhel Meman said in the comments:
正如 Suhel Meman 在评论中所说：
SELECT column1, column2, column3 FROM table 1
UNION
SELECT column1, column2, column3 FROM table 2
...
would work.
会工作。
But all your SELECTSwould have to consist of the same amount of columns. And because you are displaying it in one resulting table they should contain the same information.
但是您的所有内容SELECTS都必须由相同数量的列组成。并且因为您在一个结果表中显示它，所以它们应该包含相同的信息。
What you might want to do, is a JOIN on Product ID or something like that. This way you would get more columns, which makes more sense most of the time.
您可能想要做的是在产品 ID 上加入 JOIN 或类似的东西。这样你会得到更多的列，这在大多数情况下更有意义。
