How to Search for Text in Every Field of Database in MySQL

Generally, database developers use WHERE clause with patterns or literal strings to search for a text in a specific column. If you need to search for text in multiple columns we use multiple WHERE conditions and combine them using OR condition. But what if you do not know the column where your text exists and want to search for text in every field of database in MySQL? In this article, we will learn a couple of ways to do this.

How to Search for Text in Every Field of Database in MySQL

Sadly MySQL does not provide any direct function to search a text across all tables of a database. But there are several ways to search for text in every field. We will look at some of the simpler ones.

One of the most common ways to search a text across a database is to take a backup of the database or table using MySQLdump and then use grep or some other command to search for the text. Here is the command for it.

# search text in a database
$ mysqldump -u <username> -p database > db_backup.sql
$ grep "search_text" db_backup.sql

#search text in a table
$ mysqldump -u <username> -p database table > table_backup.sql
$ grep "search_text" table_backup.sql

The first approach is useful if you don’t know the table where your text might be located. If you know the table where you text is located, or if your database is too big to be exported, then you can use the second approach.

If you have a really big database, then we suggest you install PHPMyAdmin on your system as it provides built-in functionality to search text across databases. Once you have installed PHPMyAdmin on your system and given it access to your database, you can use the following steps to search text in every field.

• Select your database. Please note, if you select a table instead of a database, you’ll get a completely different search dialog.
• Click ‘Search’ tab
• Choose the search text you want to search
• Choose the table(s) to search

The third approach is to get a list of table and column names in your database using information_schema database. It is a system built database created at the time of MySQL installation. It contains a list of all tables and columns in your database and is automatically updated as your databases change. Use tables SCHEMATA, TABLES and COLUMNS in this database to get a list of tables and columns and search them for your text. Here is a query to get list of all columns in your database.

select column_name, table_name from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position

In this article, we have learnt 3 different ways to search a text in every field of database. Out of them, we suggest using PHPMyAdmin since it is free, easy to use and provides a graphical interface to quickly search your text, without having to export it into a file.

Also read:

How to Remove Leading & Trailing Whitespace in MySQL
How to Remove Primary Key in MySQL
How to Find Most Frequent Column Value in MySQL
How to Truncate Foreign Key Constrained Table
How to Export MySQL Schema Without Data