MySQL多表搜索

Question

table1
 ID
 SUBJECT
 CONTENT
table2
 ID
 SUBJECT
 CONTENT
table3
 ID
 SUBJECT
 CONTENT
... 5 more

我想在所有表

上搜索SUBJECT

我该怎么做?

Answer 1

CREATE VIEW multitable AS
SELECT * FROM table1
UNION SELECT * from table2
UNION SELECT * from table3;
SELECT subject FROM multitable ...

Answer 2

select * from table1 where subject like '%match%' union
select * from table2 where subject like '%match%' union
select * from table3 where subject like '%match%' union
select * from table4 where subject like '%match%' union
select * from table5 where subject like '%match%' union
select * from table6 where subject like '%match%' union
select * from table7 where subject like '%match%' union
select * from table8 where subject like '%match%'

Answer 3

因为所有的表都有相同的语法，所以您可以使用UNION操作符。

SELECT * FROM Table1
UNION Table2
UNION Table3
UNION Table4
UNION Table5
UNION Table6
UNION Table7
UNION Table8
WHERE SUBJECT="Subject"

为了简单起见，8个表并不太多。如果您有更多，我建议您使用动态查询。