MySQL: How to search multiple tables for a string existing in any column

Question

How can I search for in table_a table_b table_c, which have a random number of columns for a string?

I know this is not proper sql but it would be something like:

SELECT * FROM users, accounts, something_else WHERE ->ANY COLUMN CONTAINS 'this_string'<-

Ty in advance for SO community

Refer this: winashwin.wordpress.com/2012/08/28/mysql-search

user1573308
– user1573308

2012-08-30 09:26:19 +00:00
Commented Aug 30, 2012 at 9:26 — user1573308
– user1573308, Commented Aug 30, 2012 at 9:26

ʞɔıu · Accepted Answer · 2008-12-26 17:56:20Z

30

Add fulltext indexes to all of the string columns in all of those tables, then union the results

select * from table1 where match(col1, col2, col3) against ('some string')
union all
select * from table2 where match(col1, col2) against ('some string')
union all
select * from table3 where match(col1, col2, col3, col4) against ('some string')
...

answered Dec 26, 2008 at 17:56

ʞɔıu

48.7k36 gold badges109 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

G-J Over a year ago

@kiu Very cool - Been looking for this answer for a while now. Just need to know how to ORDER the results by the number of times the string occurs. In my case, I am doing a boolean search in multiple fields from multiple tables. Just need to know how to get the results ordered by relevancy.

Collectives™ on Stack Overflow

MySQL: How to search multiple tables for a string existing in any column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related