blocks|key|1521125|text|正如苏赫尔·梅曼在评论中所说：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1521126|SELECT+column1,+column2,+column3+FROM+table+1
UNION
SELECT+column1,+column2,+column3+FROM+table+2
...|code-block|syntax|javascript|1521127|会起作用的。|1521128|但是您的所有SELECTS必须由相同数量的列组成。因为您在一个结果表中显示它，所以它们应该包含相同的信息。|offset|length|style|CODE|1521129|您可能想要做的是，对Product+ID或类似的东西进行连接。这样，您将获得更多的列，这在大多数情况下更有意义。|1521130|entityMap^0|0|0|0|6|7|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|V|8|@$K|W|L|X|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

As Suhel Meman said in the comments:

<pre><code>SELECT column1, column2, column3 FROM table 1
UNION
SELECT column1, column2, column3 FROM table 2
...
</code></pre>

would work.

But all your <code>SELECTS</code> would 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.

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.

blocks|key|5334330|text|您可以使用以下语句获取包含列product的所有表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5334331|SELECT+DISTINCT+TABLE_NAME+
++++FROM+INFORMATION_SCHEMA.COLUMNS
++++WHERE+COLUMN_NAME+IN+('Product')
++++++++AND+TABLE_SCHEMA='YourDatabase';|code-block|syntax|javascript|5334332|然后，您必须在这些表上运行cursor，以便选择eachtime：|offset|length|5334333|Select+*+from+OneTable+where+product+like+'%25XYZ%25'|5334334|结果应该输入到第三个表或视图中，看看here。|5334335|注意:只有当所有表的结构相似时，这才能起作用，否则aou将不得不查看所有这些表的哪些列是联合的，并创建您的结果表/视图以仅包含这些列。|5334336|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/cursors.html|1|https://stackoverflow.com/questions/25969/sql-insert-into-values-select-from^0|0|0|D|6|0|0|0|I|4|1|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@$I|13|J|14|1|15]]|A|$]]|$1|K|3|L|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|17|8|@]|9|@$I|18|J|19|1|1A]]|A|$]]|$1|O|3|P|5|6|7|1B|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]|Y|$5|T|U|V|A|$W|Z]]]]

You get all tables containing the column product using this statment:

<pre><code>SELECT DISTINCT TABLE_NAME 
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME IN ('Product')
 AND TABLE_SCHEMA='YourDatabase';
</code></pre>

Then you have to run a <a href="http://dev.mysql.com/doc/refman/5.0/en/cursors.html" rel="noreferrer">cursor</a> on these tables so you select eachtime: 

<pre><code>Select * from OneTable where product like '%XYZ%'
</code></pre>

The results should be entered into a 3rd table or view, take a look <a href="https://stackoverflow.com/questions/25969/sql-insert-into-values-select-from">here</a>.

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.

blocks|key|1525648|text|您可以从information_Schema.columns获取列为"Product“的所有表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1525649|SELECT+DISTINCT+table_name+FROM+information_schema.columns+WHERE+column_name+="Product";|code-block|syntax|javascript|1525650|也不会创建一个过程|1525651|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();|1525652|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can get all tables that has column "Product" from information_Schema.columns 

<pre><code>SELECT DISTINCT table_name FROM information_schema.columns WHERE column_name ="Product";
</code></pre>

Nor create a procedure

<pre><code>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();
</code></pre>

blocks|key|1806587|text|为什么不直接转储mysql数据库，而使用扩展名在不使用--单个事务的情况下运行时，会中断与其他客户端的连接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1806588|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+|code-block|syntax|javascript|1806589|之后，读出文件并搜索您想要的内容...在字符串中.....|1806590|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>mysqldump --host=hostname.de --port=0000 --user=username --password=password --single-transaction --skip-add-locks --skip-lock-tables --default-character-set=utf8 datenbankname &gt; mysqlDBBackup.sql 
</code></pre>

after that read out the file and search for what you want.... in Strings.....

blocks|key|5334441|text|SELECT+product+FROM+Your_table_name+WHERE+Product+LIKE+'%25XYZ%25';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|5334442|上面的语句将显示单个表的结果。如果您想添加更多的表，那么只需使用UNION语句。|unstyled|5334443|SELECT+product+FROM+Table_name_1+
WHERE+Product+LIKE+'%25XYZ%25'++
UNION++
SELECT+product+FROM+Table_name_2+
WHERE+Product+LIKE+'%25XYZ%25'++
UNION++
SELECT+product+FROM+Table_name_3+
WHERE+Product+LIKE+'%25XYZ%25'+|5334444|..。诸若此类|5334445|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT product FROM Your_table_name WHERE Product LIKE '%XYZ%';
</code></pre>

The above statement will show result from a single table. If you want to add more tables then simply use the UNION statement.

<pre><code>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%' 
</code></pre>

... and so on

blocks|key|1806642|text|改进为@inno答案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1806643|delimiter+//

DROP+PROCEDURE+IF+EXISTS+get_product;++
CREATE+PROCEDURE+get_product()
BEGIN
+++DECLARE+i+VARCHAR(100);+
+++DECLARE+cur1+CURSOR+FOR+SELECT+DISTINCT+table_name+FROM+information_schema.columns+WHERE+COLUMN_NAME+IN+('Product');
+++OPEN+cur1;

+++read_loop:+LOOP
+++++++FETCH+cur1+INTO+i;
++++
+++++++SELECT+i;+--+printing+table+name
++++
+++++++SET+@s+=+CONCAT('select+*+from+',+i,+'+where+Product+like+%25XYZ%25');+
+++++++PREPARE+stmt1+FROM+@s;
+++++++EXECUTE+stmt1;
+++++++DEALLOCATE+PREPARE+stmt1;

+++END+LOOP+read_loop;

+++CLOSE+cur1;
END//

delimiter+;

call+get_product();|code-block|syntax|javascript|1806644|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Improve to @inno answer
<pre><code>delimiter //

DROP PROCEDURE IF EXISTS get_product; 
CREATE PROCEDURE get_product()
BEGIN
 DECLARE i VARCHAR(100); 
 DECLARE cur1 CURSOR FOR SELECT DISTINCT table_name FROM information_schema.columns WHERE COLUMN_NAME IN ('Product');
 OPEN cur1;

 read_loop: LOOP
 FETCH cur1 INTO i;
 
 SELECT i; -- printing table name
 
 SET @s = CONCAT('select * from ', i, ' where Product like %XYZ%'); 
 PREPARE stmt1 FROM @s;
 EXECUTE stmt1;
 DEALLOCATE PREPARE stmt1;

 END LOOP read_loop;

 CLOSE cur1;
END//

delimiter ;

call get_product();
</code></pre>

I have a mySQL database called <code>listDB</code> that contain several tables with column name <code>Product</code> etc. I want to <code>SELECT</code> from all tables where Product Like %XYZ%, and display the search result in a separate table.

I tried this:

<pre><code>SELECT * FROM * WHERE Product LIKE %XYZ%
</code></pre>

But it is not working. What is the right query for this purpose?

Select From all tables - MySQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个名为listDB的mySQL数据库，其中包含几个列名称为Product等的表。我想从所有Product Like %XYZ%的表中执行SELECT操作，并在一个单独的表中显示搜索结果。我试过这个：SELECT * FROM * WHERE Product LIKE %XYZ%但它不起作用。用于此目的的正确查询是...

问从所有表中选择- MySQL
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从所有表中选择- MySQLEN