mysql选择所有表,从所有表中选择-MySQL

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

I tried this:

SELECT * FROM * WHERE Product LIKE %XYZ%

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

解决方案

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

SELECT DISTINCT TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME IN ('Product')

AND TABLE_SCHEMA='YourDatabase';

Then you have to run a cursor on 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.