MySQL - How to check for a value in all columns

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 = ?

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.

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