Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted November 7, 2013

Locating Instances of a Word or Expression within a MySQL Database

By Rob Gravelle

Ever have one of those times that you need to find a word or phrase in the database, but you have no idea what table it might be in? If not, you are one lucky DBA! Most of us have to take care of a database that we don't know like the back of our hand at some point or another. When a request comes in to change a value it helps tremendously to have the capability to perform a database-wide sweep; one that won't take the better part of our work week. Well, despair not. There are some options available to us. This article will present three of them!

The Low-Tech Solution

MySQL comes with a handy command line utility called mysqldump. People usually use it to perform backups of the database because it creates all of the statements required to reconstruct the database's table structure and data. Another use for it is to quickly create a snapshot of the data contained in one or more databases. The trick is to include a few options that will streamline the output to just the data, or more precisely, just the insert statements that would repopulate the data:

  • -- skip-triggers: There are a number of options pertaining to cutting down on the amount of output produces, and that's a good thing, because mysqldump has a habit of producing a lot of output! As you might have guessed, this option suppresses the printing of trigger statements.
  • -- compact: This option enables the -- skip-add-drop-table, -- skip-add-locks, -- skip-comments, -- skip-disable-keys, and -- skip-set-charset options all at once, in order to produce less output.
  • -- no-create-info: This option loses the CREATE TABLE statements that would re-create each dumped table.

The easiest way to use mysqldump is to redirect the output to a file so that you can then use your favorite text editor to search for your text terms:

C:\>mysqldump -- user=root -- password=mypassword test   -- skip-triggers -- compact -- no-create-info > C:\db_data.txt
  

The produced file will contain a number of INSERT statements similar to the following:

INSERT INTO `quotes` VALUES
(1,'Stephen Colbert','Is that a pizza or did an angel just   give birth in my mouth?',NULL),
   (2,'Sir Paul McCartney','Animation is not just for children. It\'s also for   adults who take 
   drugs.',NULL),
   (3,'Michael Caine','I have never seen [Jaws: The Revenge], but by all   accounts it was terrible. 
   However, I have seen the house that it built, and it is terrific.',NULL),
   (4,'Professor Rohl','What we feel isn\'t important…The only question is what   we do.','2008 
   movie \"The Reader\"'),
   (5,'Steve McConnell','When someone says, \"This is really tricky   code,\" I hear them say, \"This 
   is really bad code.\"',NULL),
   (6,'Charlie Sheen','I have a disease? Bulls**t! I cured it ... with my   mind.',NULL),
   (7,'Anonymous','Amateurs practice something until they get it right.   Professionals practice 
   something until they almost never get it wrong.',NULL);

The Pure SQL Solution

Some people are really do-it-yourselfers. For them, the ideal approach is to create their own stored procedure. Sure, it's a lot more work initially, but you can tailor it to your exact specifications and it's completely reusable!

What makes this type of procedure possible is the information_schema database. It's where MySQL keeps information about all the other databases that the MySQL server maintains. As such, it contains database and table names, column data types, and access privileges. I've already discussed this amazing resource in my Understanding the MySQL Information Schema Database article. You'll find out a lot more about the information_schema database there.

The following proc uses a cursor to store the relevant schema (database), table, and column names, along with a count of matches. Then, a loop iterates over the cursor and inserts rows that have matches (i.e., a count greater than zero) into a temp table. Finally, a SELECT * returns the matched rows:

DELIMITER $$
   CREATE DEFINER=`root`@`localhost` PROCEDURE `db_search`(in_search   varchar(255))
   READS SQL DATA
   BEGIN
   DECLARE search_string VARCHAR(250);
   DECLARE db,tbl,clmn CHAR(50);
   DECLARE done INT DEFAULT 0;
   DECLARE COUNTER INT;
   DECLARE table_cur CURSOR FOR
   SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',
   table_schema,'`.`',table_name,'` WHERE `', column_name,
   '` REGEXP \'',in_search,'\';')
   ,table_schema,table_name,column_name
   FROM information_schema.COLUMNS
   # These tables contain configuration info and not user data
   WHERE TABLE_SCHEMA NOT IN   ('information_schema','performance_schema','mysql');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
DROP TEMPORARY TABLE IF EXISTS `temp_details`;
## Table for storing resultant output
   CREATE TEMPORARY TABLE `temp_details` (
   `t_schema` varchar(45) NOT NULL,
   `t_table` varchar(45) NOT NULL,
   `t_field` varchar(45) NOT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
OPEN table_cur;
   table_loop:LOOP
   FETCH table_cur INTO search_string,db,tbl,clmn;
#Executing the search
   SET @search_string = search_string;
   SELECT search_string;
   PREPARE search_string FROM @search_string;
   EXECUTE search_string;
SET COUNTER = @CNT_VALUE;
   SELECT COUNTER;
IF COUNTER>0 THEN
   # Inserting required results from search to table
   INSERT INTO temp_details VALUES(db,tbl,clmn);
   END IF;
IF done=1 THEN
   LEAVE table_loop;
   END IF;
   END LOOP;
   CLOSE table_cur;
#Finally Show Results
   SELECT * FROM temp_details;
   END

Let's say that I'm looking for instances of the string "Jaws". I would call my proc with the string as follows:

Mysql> call db_search('jaws');

MySQL will output all of the cursor queries as it goes as well as the value of the COUNTER variable. This info is also helpful, because it can tell you what is being searched, sort of like a progress report.

...
+   --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  -- -+
    | search_string|
    |
    + --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  -- -+
    | SELECT COUNT(*) INTO @CNT_VALUE FROM `test`.`words` WHERE `is_num` REGEXP   'jaws'; |
    + --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  -- -+
1   row in set (23.91 sec)
+   --  --  --  -- -+
    | COUNTER |
    + --  --  --  -- -+
    |    0 |
    + --  --  --  -- -+
1   row in set (23.93 sec)
+   --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  -- -+
    | search_string
    |
    + --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  -- -+
    | SELECT COUNT(*) INTO @CNT_VALUE FROM `test`.`words` WHERE `is_num` REGEXP   'jaws'; |
    + --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --    --  --  --  -- -+
1 row in set (23.93 sec)
+   --  --  --  -- -+
    | COUNTER |
    + --  --  --  -- -+
    |    0 |
    + --  --  --  -- -+
1 row in set (23.93 sec)

After a while, the dust will settle and you'll get a final tabulation of matching database schemas, tables, and columns:

+   --  --  --  --  -- + --  --  --  -- -+ --  --  --  -- -+
    | t_schema | t_table | t_field |
    + --  --  --  --  -- + --  --  --  -- -+ --  --  --  -- -+
    | test   | quotes | quote  |
    + --  --  --  --  -- + --  --  --  -- -+ --  --  --  -- -+
1   row in set (23.95 sec)
Query   OK, 0 rows affected (23.95 sec)

An extra benefit of using stored procedure is that it can search all your databases at once. Powerful stuff!

The Easy GUI Solution

This final plan of attack is not only easy to execute, but it may already be within your grasp. It's the Search utility that comes with the phpMyAdmin database administration program. A lot of web hosts have it installed, so if your database(s) reside(s) on an Internet host, then it would be worth looking into.

If you're hosting your database yourself, you can always download and install phpMyAdmin on your server. It's a great tool for managing your MySQL databases.

The Search facilities include several options, like the use of regular expressions, limiting target tables and fields, as well as wildcards:

Conclusion

Hopefully you'll never have to babysit a database that you aren't all that familiar with, but if such a fate should befall you, at least you'll be able to lookup values even if you have no idea where to look!

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date