Search for all occurrences of a string in a mysql database [duplicate]

In phpMyAdmin a 'Search' feature is available:

1. Select particular database not table.
2. Click 'Search' tab
3. Enter the search term you want
4. Select the tables you want to search in

phpMyAdmin screen shot:

The 'Search' feature is also available in MySQL Workbench:

1. Database Menu > Search Table Data
2. Select database and tables you want to search (it will search in selected tables only)
3. In search you can use wildChars.

MySQL Workbench screen shot:

Old post I know, but for others that find this via Google like I did, if you have phpmyadmin installed, it has a global search feature.

Using the MySQL Workbench, you can search for a string from the "Database" -> "Search Table Data" menu option.

Specify LIKE %URL_TO_SEARCH% and on the left side select all the tables you want to search through. You can use "Cntrl + A" to select the whole tree on the left, and then deselect the objects you don't care about.

You can do this with HeidiSQL without generating Db dumps

Steps:

1. Select the database you need to search in from the left panel of GUI.

2. Export > Export Database as SQL

3. In Table Tools window, select "FIND TEXT" tab.

4. Provide your string to search and click "FIND".

5. It will list all the tables that has our string.

6. Select the row with higher relevance %.

7. Click "SEE RESULTS"

I was looking for this myself when we changed domain on our Wordpress website. It can't be done without some programming so this is what I did.

<?php header("Content-Type: text/plain"); $host = "localhost"; $username = "root"; $password = ""; $database = "mydatabase"; $string_to_replace = 'old.example.com'; $new_string = 'new.example.com'; // Connect to database server mysql_connect($host, $username, $password); // Select database mysql_select_db($database); // List all tables in database $sql = "SHOW TABLES FROM ".$database; $tables_result = mysql_query($sql); if (!$tables_result) { echo "Database error, could not list tables\nMySQL error: " . mysql_error(); exit; } echo "In these fields '$string_to_replace' have been replaced with '$new_string'\n\n"; while ($table = mysql_fetch_row($tables_result)) { echo "Table: {$table[0]}\n"; $fields_result = mysql_query("SHOW COLUMNS FROM ".$table[0]); if (!$fields_result) { echo 'Could not run query: ' . mysql_error(); exit; } if (mysql_num_rows($fields_result) > 0) { while ($field = mysql_fetch_assoc($fields_result)) { if (stripos($field['Type'], "VARCHAR") !== false || stripos($field['Type'], "TEXT") !== false) { echo " ".$field['Field']."\n"; $sql = "UPDATE ".$table[0]." SET ".$field['Field']." = replace(".$field['Field'].", '$string_to_replace', '$new_string')"; mysql_query($sql); } } echo "\n"; } } mysql_free_result($tables_result); ?> 

Hope it helps anyone who's stumbling into this problem in the future :)

Found a way with two (2) easy codes here. First do a mysqldump:

mysqldump -uUSERNAME -p DATABASE_NAME > database-dump.sql 

then grep the sqldump file:

grep -i "Search string" database-dump.sql 

It possible also to find/replace and re-import back to the database.

SQLyog is GUI based solution to the problem of data search across all columns, tables and databases. One can customize search restricting it to field, table and databases.

In its Data Search feature one can search for strings just like one uses Google.

MikeW presented an interesting solution, but as mentioned in comments, it's a SQL Server solution not a MySQL solution. Here is a MySQL solution:

use information_schema; set @q = 'Boston'; set @t = 'my_db'; select CONCAT('use \'',@q,'\';') as q UNION select CONCAT('select \'', tbl.`TABLE_NAME`,'\' as TableName, \'', col.`COLUMN_NAME`,'\' as Col, `',col.`COLUMN_NAME`,'` as value from `' , tbl.`TABLE_NAME`,'` where `' , col.`COLUMN_NAME` , '` like \'%' ,@q, '%\' UNION') AS q from `tables` tbl inner join `columns` col on tbl.`TABLE_NAME` = col.`TABLE_NAME`and col.DATA_TYPE='varchar' where tbl.TABLE_SCHEMA = @t ; 

I can't remember where I came across this script, but I've been using it with XCloner to move my WP multisites.

<?php // Setup the associative array for replacing the old string with new string $replace_array = array( 'FIND' => 'REPLACE', 'FIND' => 'REPLACE'); $mysql_link = mysql_connect( 'localhost', 'USERNAME', 'PASSWORD' ); if( ! $mysql_link) { die( 'Could not connect: ' . mysql_error() ); } $mysql_db = mysql_select_db( 'DATABASE', $mysql_link ); if(! $mysql_db ) { die( 'Can\'t select database: ' . mysql_error() ); } // Traverse all tables $tables_query = 'SHOW TABLES'; $tables_result = mysql_query( $tables_query ); while( $tables_rows = mysql_fetch_row( $tables_result ) ) { foreach( $tables_rows as $table ) { // Traverse all columns $columns_query = 'SHOW COLUMNS FROM ' . $table; $columns_result = mysql_query( $columns_query ); while( $columns_row = mysql_fetch_assoc( $columns_result ) ) { $column = $columns_row['Field']; $type = $columns_row['Type']; // Process only text-based columns if( strpos( $type, 'char' ) !== false || strpos( $type, 'text' ) !== false ) { // Process all replacements for the specific column foreach( $replace_array as $old_string => $new_string ) { $replace_query = 'UPDATE ' . $table . ' SET ' . $column . ' = REPLACE(' . $column . ', \'' . $old_string . '\', \'' . $new_string . '\')'; mysql_query( $replace_query ); } } } } } mysql_free_result( $columns_result ); mysql_free_result( $tables_result ); mysql_close( $mysql_link ); echo 'Done!'; ?> 

If you can use a bash - here is a script: It needs a user dbread with pass dbread on the database.

#!/bin/bash IFS=' ' DBUSER=dbread DBPASS=dbread echo -n "Which database do you want to search in (press 0 to see all databases): " read DB echo -n "Which string do you want to search: " read SEARCHSTRING for i in `mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | head -1\`` do for k in `mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | head -1\` | grep -v int | awk '{print $1}'` do if [ `mysql $DB -u$DBUSER -p$DBPASS -e "Select * from $i where $k='$SEARCHSTRING'" | wc -l` -gt 1 ] then echo " Your searchstring was found in table $i, column $k" fi done done 

If anyone wants an explanation: http://infofreund.de/?p=1670

Scott gives a good example of how to do it, but the question is why would you want to? If you need to do a find-and-replace on a specific string, you could also try doing a mysqldump of your database, do a find-and-replace in an editor, then re-load the database.

Maybe if you gave some background on what you are trying to achieve, others might be able to provide better answers.

I was looking for the same but couldn't find it, so I make a small script in PHP, you can find it at: http://tequilaphp.wordpress.com/2010/07/05/searching-strings-in-a-database-and-files/

Good luck! (I remove some private code, let me know if I didn't break it in the process :D)

The first 30 seconds of this video shows how to use the global search feature of Phpmyadmin and it works. it will search every table for a string.

http://www.vodahost.com/vodatalk/phpmyadmin-setup/62422-search-database-phpmyadmin.html

In unix machines, if the database is not too big:

mysqldump -u <username> -p <password> <database_name> --extended=FALSE | grep <String to search> | less -S 

Not an elegant solution, but you could achieve it with a nested looping structure

// select tables from database and store in an array // loop through the array foreach table in database { // select columns in the table and store in an array // loop through the array foreach column in table { // select * from table where column = url } } 

You could probably speed this up by checking which columns contain strings while building your column array, and also by combining all the columns per table in one giant, comma-separated WHERE clause.