MySQL : search multiple columns with a single statement

Question

I have a MySQL(5.7) table like this.

+--------+--------------+--------+-------+----------+--------+
| Field  | Type         | Null   | Key   | Default  | Extra  |
|--------+--------------+--------+-------+----------+--------|
| colA   | varchar(50)  | NO     | PRI   | <null>   |        |
| colB   | int(11)      | YES    |       | <null>   |        |
| colC   | int(11)      | YES    |       | <null>   |        |
| colD   | int(11)      | YES    |       | <null>   |        |
| colE   | int(11)      | YES    |       | <null>   |        |
| colF   | int(11)      | YES    |       | <null>   |        |
| colG   | int(11)      | YES    |       | <null>   |        |
| colH   | int(11)      | YES    |       | <null>   |        |
| colI   | int(11)      | YES    |       | <null>   |        |
| colJ   | int(11)      | YES    |       | <null>   |        |
| colK   | int(11)      | YES    |       | <null>   |        |
| colL   | int(11)      | YES    |       | <null>   |        |
| colM   | int(11)      | YES    |       | <null>   |        |

I would like to search all the colB, colC, ..., colM mod 100 is 1 or 2. I know I can do this with this.

SELECT * FROM MYTABLE WHERE 
(mod(colB, 100) = 1 OR mod(colB, 100) = 2) AND 
(mod(colC, 100) = 1 OR mod(colC, 100) = 2) AND 
(mod(colD, 100) = 1 OR mod(colD, 100) = 2) AND 
                 :
(mod(colM, 100) = 1 OR mod(colM, 100) = 2)

This SQL is very long and inefficient. Can I do this more smart?

Why do you have so many columns in the first place?

Tim Biegeleisen
– Tim Biegeleisen

2017-06-05 01:35:37 +00:00
Commented Jun 5, 2017 at 1:35 — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 5, 2017 at 1:35

Gordon Linoff · Accepted Answer · 2017-06-05 01:37:08Z

1

Your query is pretty much the only way to do it. You can simplify it using in:

where mod(colB, 100) in (1, 2) AND
      mod(colC, 100) in (1, 2) AND
      . . .

More importantly, though, the need for this type of operation suggests that all the columns are representing a similar entity. That further suggests that you should be storing the values as one row per pair. Such a table would look something like this:

colA
colType
colValue

The query would be much simpler and shorter with this data structure.

answered Jun 5, 2017 at 1:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zxcvc · Accepted Answer · 2017-06-05 03:59:55Z

0

You can do it like below:

SELECT * FROM MYTABLE WHERE 
where mod(colB, 100) in (1, 2) AND
colB = colC AND colB = colD AND colB = colE ... etc

Since you already check colB, as long as colB and other column is equals its means it should match with your first filter.

answered Jun 5, 2017 at 3:59

zxcvc

3551 silver badge16 bronze badges

Collectives™ on Stack Overflow

MySQL : search multiple columns with a single statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related