mysql search string in multiple tables

How to Search String in Multiple Tables in Database

Generally, database developers search string in a single database in MySQL. But sometimes you may need to search string in multiple tables in database. In this article, we will learn how to do this in MySQL.


How to Search String in Multiple Tables in Database

Let us say your database has tables table1, table2, table3. Let us say you want to search for string ‘abc’ in each of them and get matching rows in each of the tables. You can do this using full text search feature in MySQL.

First of all, add fulltext indexes to all each of the string column in all those tables where you want to do the matching.

Here is an example to add fulltext index to col1, col2, col3 in table table1.

CREATE TABLE table1 (
         ...<column definitions>...
         FULLTEXT (col1, col2, col3)
         ) ENGINE=InnoDB;

After that, you can just union the results as shown below. Here is the SQL query to get rows where col1 in table1 matches ‘abc’, col2 in table2 matches ‘abc’, and col3 in table3 matches ‘abc’

select * from table1 where match(col1) against ('abc')
union all
select * from table2 where match(col2) against ('abc')
union all
select * from table3 where match(col3) against ('abc')

You can also match multiple columns from each table in a single query.

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

In this short article, we have learnt how to search string in multiple tables in MySQL.

Also read:

How to Split Field into Two in MySQL
How to Fix Can’t Connect to Local MySQL Socket Error
How to Make Case Sensitive String Comparison in MySQL
How to Fix Error MySQL Shutdown Unexpectedly
How to Fix ‘2006, MySQL Server Has Gone Away’ Error

Leave a Reply

Your email address will not be published. Required fields are marked *