MySQL - How to check for a value in all columns

Question

I am curious, is there a good way to search all columns for a given value? For my purposes, it doesn't need to be at all fast, it's just a 1-off kinda thing, and I don't really want to have to type out every field name. That's precisely what I'll be doing for now, but I think surely there's a better way.

I would like to turn this:

SELECT * FROM table WHERE col1 = 'val' OR col2 = 'val' OR col3 = 'val';

into this:

SELECT * FROM table WHERE * = 'val'

...or, even better (though I seriously doubt it...)

SELECT * FROM table WHERE * like '%val%'

I found this, which seems kinda-not-really close, but I'm not finding anything closer:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

Difference being, that searches a selection of columns for the specified values, whereas I'm trying to search ALL columns for a single value.

It's not important though, like I said more than anything I'm just curious

Answer 1

SQL doesn't provide a good way of doing this because it's probably not a good table design to have N columns that might have values in the same domain. This is potentially a case of "repeating groups" like if you have columns phone1, phone2, phone3, ... phoneN.

If you have a number of columns that have the same logical domain of values, it could be a case where it's a multi-valued attribute (like the phones example). The standard way of storing multi-valued attributes is as multiple rows in another table, with a reference to the row they belong to in the primary table.

If you do that, then you only have to search for the specific value in one column of the dependent table.

SELECT t.* FROM mytable AS t
JOIN phones AS p ON t.primaryKey = p.refKey 
WHERE p.phone = ?

Answer 2

The closer you can get is this, using IN:

SELECT * FROM table WHERE 'val' IN (col1, col2, ..., colN) ;

You still have to write all the columns you want to check.
And it's not any different than the OR expression you have, not in performance or otherwise. This is just a different, equivalent way to write the expression, with a bit fewer characters.

Answer 3

Feels like it's been a year since I asked this, but I just stumbled on what appears to be the exact thing I was looking for! It's not a SQL statement, like I was anticipating, but it DOES what I was wanting.

In MySQL Workbench, you can right click the table or the schema, and choose Search Table Data.

Answer 4

You need to do it in two steps, first generate the sql like (assuming your table is named T in schema S:

select concat(' SELECT * FROM t WHERE ''a'' in ('
             , GROUP_CONCAT(COLUMN_NAME)
             , ')')
from INFORMATION_SCHEMA.columns 
where table_schema = 's' 
  and table_name = 't'
  and DATA_TYPE IN ('char','varchar');

Now you can execute this string. Note that you have to quote the 'a' with extra '. If you put this in for example a procedure, you can prepare and execute the string that was generated.

I tested with:

create table t (a char(3), b varchar(5), c int);
insert into t(a,b) values ('a','b'),('b','c'),('c','c');    

The query generated where:

SELECT * FROM t WHERE 'a' in (a,b)

and executing that results in:

a   b   c
---------
a   b   

Answer 5

The simplest solution is doing

mysqldump ... --skip-extended-insert db table | grep 'val'

Unless val is something that is a common occurrence in SQL syntax.

This is a valid way to find something if you do not know "where" it is in the database or table. The grep will return entire rows (including the table name) where the value is present. It is not SQL and is not very useful if you want to process the output programmatically, but it is simple and quite fast.

Using mysqldump and grep may be far from user-friendly, and it might look very basic, but many people will not even think of it when they are fixed on finding a SQL solution. For some people, the CLI solution may be more accessible than, say, installing and connecting Workbench.

Answer 6

Try:

select
    *
from
    _table_
where
    concat('.', col1, '.', col2, '.', col3, '.') like "%.search_value.%";

The way this tries to solve the problem is by creating a long string containing every value of a given row, separated by a dot character. then it performs a string search for the desired term between two dots.

Note that if any column may be NULL, you should use CONCAT_WS() instead.

This assumes that there is no '.' in your search string. If you cannot guarantee that, maybe you can use a different "separator character".

If you want to search for substrings in the individual collumns, you can move the % signs to be inside the '.' like so:

select
    *
from
    _table_
where
    concat('.', col1, '.', col2, '.', col3, '.') like ".%search_value%.";

Answer 7

You can do this fairly easily in MySQL, as long as your have the FULLTEXT index set up against your table. It allows for a heap of different search mechanisms to be used including natural language, and additional metadata to score the results.

Taken from the MySQL manual:

mysql> SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

This only really works for character-based columns. If you are looking for a specific value in numerical or other special types, you'll need to use CONCAT or other mechanisms to achieve that.