Find column that contains a given value in MySQL

Question

Hello all

I have a table in a MySQL database. I am given a value that occurs as a cell value in that table but I do not know which cell is it i.e. the row and column of that cell. What is the most efficient way to find the column to which that value belongs? Thanks in advance.

Example:

Column_1 | Column_2 | Column_3
1        | 2        | 3
4        | 5        | 6
7        | 8        | 9

Now I am given an input value of "8". I want to know if there is an efficient way to find out that value of "8" belongs to Column_2.

Answer 1

It's a bit strange that you don't know which column the data is in, since columns are meant to have a well-defined function.

[Original response scrubbed.]

EDIT: Your updated post just asks for the column. In that case, you don't need the view, and can just run this query

SELECT col FROM (
   SELECT "Column_1" AS col, Column_1 AS value FROM YourTable
   UNION ALL SELECT "Column_2", Column_2 FROM YourTable
   UNION ALL SELECT "Column_3", Column_3 FROM YourTable
) allValues
WHERE value=8;

When you run this query against your table, it will return "Column_2"

Answer 2

You might be able to adapt this for your needs. (If it is always a number you are searching for then excluding all character columns will make it more efficient)

Answer 3

Without knowing more about your app, you have several options:

• Use MySQL's built-in full-text search. You can check the MATCH function in the MySQL documentation.
• Depending on the needs of your app you could decide to index your whole table with an external full-text search index, like Solr or Sphynx. This provides instant response time, but you'll need to keep the index updated.
• You can loop through all the columns in the table doing a LIKE query in MySQL (very expensive in CPU and time)

Answer 4

You're designing this table with repeating groups, which is not satisfying First Normal Form.

You should create a second table and store the values for column1, column2, and column2 in a single column, on three rows.

Learn about the rules of database normalization for more details.