How to search whole MySQL database for a particular string

  • is it possible to search a whole database tables ( row and column) to find out a particular string.

    I am having a Database named A with about 35 tables,i need to search for the string named "hello" and i dont know on which table this string is saved.Is it possible?

    Using MySQL

    i am a linux admin and i am not familiar with databases,it would be really helpful if u can explain the query also.

  • $ mysqldump -pPASSWORD database --extended=FALSE | grep pattern | less -S
    

    More: http://winashwin.wordpress.com/2012/08/28/mysql-search/

  • Back in July 2012 I wrote this post

    Query to find and replace text in all tables and fields of a mysql db

    It uses the table information_schema.columns to pick up every CHAR, VARCHAR, and TEXT field and perform a textual REPLACE.

    Please look over my old link and use its paradigm to do a search.

    As an example, this will create a separate SELECT for each text column in every table

    SELECT
        CONCAT('SELECT ',QUOTE(db),',',QUOTE(tb),',',QUOTE(col),',COUNT(1) FieldHasIt
        FROM ',db,'.',tb,' WHERE \`',col,'\`=''',SearchString,''';') SearchSQL
    FROM
    (
        SELECT table_schema db,table_name tb,column_name col FROM information_schema.columns
        WHERE table_schema = 'mydb' AND
        (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text')
    ) A,(SELECT 'Hello' SearchString) B;
    

    Create a Giant SQL text file with it. Then, execute that Giant SQL script:

    SQL="SELECT CONCAT('SELECT ',QUOTE(db),',',QUOTE(tb),',',"
    SQL="${SQL} QUOTE(col),',COUNT(1) FieldHasIt FROM ',db,'.',tb,'"
    SQL="${SQL} WHERE \`',col,'\`=''',SearchString,''';') SearchSQL FROM"
    SQL="${SQL} (SELECT table_schema db,table_name tb,column_name col FROM"
    SQL="${SQL} information_schema.columns WHERE table_schema='store_qa'"
    SQL="${SQL} AND (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%'"
    SQL="${SQL} OR column_type LIKE '%text')) A,(SELECT 'Hello' SearchString) B;"
    
    mysql -uroot -p... -ANe"${SQL}" > MegaSearch.sql
    mysql -uroot -p... -AN < MegaSearch.sql > MegaSearchResults.txt
    RESULTS_FOUND=`grep -c "1$" < MegaSearchResults.txt`
    echo ${RESULTS_FOUND}
    if [ ${RESULTS_FOUND} -gt 0 ] ; then grep "1$" < MegaSearchResults.txt ; fi
    

    The output tells you the Database, Table, and Column the data appears in.

    Give it a Try !!!.

    EDIT:

    CONCAT('SELECT ',QUOTE(db),',',QUOTE(tb),',',QUOTE(col),',COUNT(1) FieldHasIt
            FROM ',db,'.',tb,' WHERE \`',col,'\`=''',SearchString,''';')
    

    SHOULD: add the two back ticks shown below for tables which have a space in the name.

    db,'.`',tb,'`',' WHERE
    

    This solution worked very nicely! Is it necessary to manually copy the initial query into an environment variable line by line? I was able to paste and run the query inside a mysql session, but surely it's possible to also paste that query into a file?

    @JohnT I I do line-by-line to avoid having to vertically scroll my answer. You can do whichever way is simplest.

  • Taking another approach that doesn't require building SQL queries, I developed CRGREP as a free opensource command line tool that will grep databases (including MySQL) and supports both simple "fred customer.name" type searches for "fred" in the "name" column of the "customer" table to more complex pattern matching such as "fr?d *.author" for pattern matching against all "author" columns across all tables.

  • You can return all columns from all tables that match any values you're looking for all at once by building off of RolandoMySQLDBA's answer and using PREPARE and EXECUTE to run the query.

    -- Your values
    SET @table_schema = 'your_table_name';
    SET @condition = "LIKE '%your string to search%'";
    SET @column_types_regexp = '^((var)?char|(var)?binary|blob|text|enum|set)\\(';
    
    -- Reset @sql_query in case it was used previously
    SET @sql_query = '';
    
    -- Build query for each table and merge with previous queries with UNION
    SELECT
        -- Use `DISTINCT IF(QUERYBUILDING, NULL, NULL)`
        -- to only select a single null value
        -- instead of selecting the query over and over again as it's built
        DISTINCT IF(@sql_query := CONCAT(
            IF(LENGTH(@sql_query), CONCAT(@sql_query, " UNION "), ""),
            'SELECT ',
                QUOTE(CONCAT('`', `table_name`, '`.`', `column_name`, '`')), ' AS `column`, ',
                'COUNT(*) AS `occurrences` ',
            'FROM `', `table_schema`, '`.`', `table_name`, '` ',
            'WHERE `', `column_name`, '` ', @condition
        ), NULL, NULL) `query`
    FROM (
        SELECT
            `table_schema`,
            `table_name`,
            `column_name`
        FROM `information_schema`.`columns`
        WHERE `table_schema` = @table_schema
        AND `column_type` REGEXP @column_types_regexp
    ) `results`;
    select @sql_query;
    
    -- Only return results with at least one occurrence
    SET @sql_query = CONCAT("SELECT * FROM (", @sql_query, ") `results` WHERE `occurrences` > 0");
    
    -- Run built query
    PREPARE statement FROM @sql_query;
    EXECUTE statement;
    DEALLOCATE PREPARE statement;
    
  • If you're Linux admin, you should be familiar with the command line, so this one would be handy:

    $ mysqldump -u root -proot --skip-extended-insert db_name | grep --color=auto -w foo
    

    Change root/root to your mysql credentials (or use ~/.my.cnf), db_name to your database name and foo for your searching text. Parameter --skip-extended-insert for mysqldump will display each query in separate lines. Parameter --color=auto for grep will highlight your string and -w will match the whole word.

  • 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

  • There is a nice library for reading all tables, ridona

    $database = new ridona\Database('mysql:dbname=database_name;host=127.0.0.1', 'db_user','db_pass');
    
    foreach ($database->tables(['table_name1','table_name2'])->by_entire() as $row) {
    
      do...
    
    }
    
  • Use This Trick with One Line of Code

    SELECT * FROM `table_name1`,`table_name2` 
    WHERE '$data' IN (`column_name1`,`column_name2`,`column_name3`,`column_name4`);
    

    column_name : Type all the columns name in which you search.

    table_name : Type all the Table name in which you search.

    This may be practical in very small databases - it's less so if you're trying to locate a value across hundreds of tables and thousands of columns. Based on the wording "whole database" in the original question, I'm assuming this should deal with all tables in the DB.

  • If you are using MySQL Workbench, right click on the database schema and select "Search Table Data..." then fill out the form. This will search the entire database. When (or if) the results come up, click on the arrow to expand. It will show the schema, table, primary key data, column name and the actual data that matches your search.
    Tip: If nothing comes up, try widen your search.
    Please note that this is only really useful for searching text (char, varchar and text) data types.

  • If you have phpMyAdmin installed use its 'Search' feature.

    Select your DataBase Be sure you do have selected DataBase , not a table, otherwise you'll get a completely different search dialog

    1. Click 'Search' tab
    2. List item Choose the search term you want
    3. Choose the tables to search

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM