Search for all occurrences of a string in a mysql database

The Question :

204 people think this question is useful

I’m trying to figure out how to locate all occurrences of a url in a database. I want to search all tables and all fields. But I have no idea where to start or if it’s even possible.

The Question Comments :
  • An example of when I needed this is when I needed to do a global find and replace on a database I was not familiar with, e.g. when migrating a WordPress site to a different domain I needed to replace all occurrences of the old domain name.
  • Refer this: winashwin.wordpress.com/2012/08/28/mysql-search
  • Export as text all tables, then Ctrl+F 🙂

The Answer 1

230 people think this answer is useful

A simple solution would be doing something like this:

mysqldump -u myuser --no-create-info --extended-insert=FALSE databasename | grep -i "<search string>"

The Answer 2

108 people think this answer is useful

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:

phpMyAdmin Database Search tab

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:

MySQL Workbench Database Search

The Answer 3

63 people think this answer is useful

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.

The Answer 4

22 people think this answer is useful

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.

enter image description here

The Answer 5

11 people think this answer is useful

You can do this by using 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 contains our string.

6) Select the row with higher relevance %.

7) Click “SEE RESULTS

enter image description here

The Answer 6

8 people think this answer is useful

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 🙂

The Answer 7

5 people think this answer is useful

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.

SQLyog Datasearch screenshot

The Answer 8

5 people think this answer is useful

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  ;

The Answer 9

4 people think this answer is useful

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.

The Answer 10

2 people think this answer is useful

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!';

?>

The Answer 11

2 people think this answer is useful

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

The Answer 12

1 people think this answer is useful

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.

The Answer 13

1 people think this answer is useful

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 Answer 14

1 people think this answer is useful

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

The Answer 15

1 people think this answer is useful

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

The Answer 16

0 people think this answer is useful

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.

Tags:

Add a Comment