Search text in fields in every table of a MySQL database
You can peek into the information_schema
schema. It has a list of all tables and all fields that are in a table. You can then run queries using the information that you have gotten from this table.
The tables involved are SCHEMATA, TABLES and COLUMNS. There are foreign keys such that you can build up exactly how the tables are created in a schema.
mySQL query to search all tables within a database for a string?
If you want to do it purely in MySQL, without the help of any programming language, you could use this:
## Table for storing resultant output
CREATE 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;
## Procedure for search in all fields of all databases
DELIMITER $$
#Script to loop through all tables using Information_Schema
DROP PROCEDURE IF EXISTS get_table $$
CREATE PROCEDURE get_table(in_search varchar(50))
READS SQL DATA
BEGIN
DECLARE trunc_cmd VARCHAR(50);
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
WHERE TABLE_SCHEMA NOT IN ('information_schema','test','mysql');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
#Truncating table for refill the data for new search.
PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";
EXECUTE trunc_cmd ;
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 $$
DELIMITER ;
Source: http://forge.mysql.com/tools/tool.php?id=232
Search for all occurrences of a string in a mysql database
A simple solution would be doing something like this:
mysqldump -u myuser --no-create-info --extended-insert=FALSE databasename | grep -i "<search string>"
MYSQL query for searching through ALL the fields?
It is not possible with one query.
However when you do:
DESCRIBE table_name;
you get the field names which you can generate the query from.
Search in all fields from every table of a MySQL database May be useful.
Search all fields in mySql
Try this..
concate fields to search whole table
Select * from tblproduct where Concat(field1, '', field2, '', fieldn) like "%textbox%"
or
Using MATCH and AGAINST in mysql
SELECT * FROM tblproduct WHERE MATCH (field1, field2, field3) AGAINST ('textbox')
Find and Replace text in the entire table using a MySQL query
For a single table
update
UPDATE `table_name`
SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')
From multiple tables
-
If you want to edit from all tables, best way is to take the dump
and then find/replace
and upload it back.
way to search entire database for a string in MySQL
There is one solution, which might not be what you want. If you dumped the table into a file (mysqldump) with the data, then you would be able to grep any information you wanted out of it.
It would remove the need for time consuming search queries, and is the most efficient way I can think of.
Search a whole table in mySQL for a string
Try something like this:
SELECT * FROM clients WHERE CONCAT(field1, '', field2, '', fieldn) LIKE "%Mary%"
You may want to see SQL docs for additional information on string operators and regular expressions.
Edit: There may be some issues with NULL fields, so just in case you may want to use IFNULL(field_i, '')
instead of just field_i
Case sensitivity: You can use case insensitive collation or something like this:
... WHERE LOWER(CONCAT(...)) LIKE LOWER("%Mary%")
Just search all field: I believe there is no way to make an SQL-query that will search through all field without explicitly declaring field to search in. The reason is there is a theory of relational databases and strict rules for manipulating relational data (something like relational algebra or codd algebra; these are what SQL is from), and theory doesn't allow things such as "just search all fields". Of course actual behaviour depends on vendor's concrete realisation. But in common case it is not possible. To make sure, check SELECT
operator syntax (WHERE
section, to be precise).
Related Topics
Sql Query to Select from 1 Hour Ago
How to Find What Privileges a Group Has in Redshift
Sql Get Parent Where Children Have Specific Values
Remove Numbers from String SQL Server
Job for Mysqld.Service Failed See "Systemctl Status Mysqld.Service"
How to Delete Multiple Rows in Diferent Tables
Select Query to Remove Non-Numeric Characters
Joining the Table Conditionally in SQL
How to Extract Year from Hire_Date in My SQL
How to Select All Records from One Table That Do Not Exist in Another Table
Select and Compare Two Datetime Columns from Different Table Without Having Any Relation
How to Modify Query to Remove Double-Quotes from All Columns
Multiple Rows Output into Variables in MySQL
How to Merge Rows on Specific Condition
How to Exclude Specific Row from Query in MySQL
Oracle Pl/Sql String Compare Issue
How to Use Json_Extract in MySQL and Get a String Without the Quotes