mysql where 括号_mysql – 与括号比较有什么区别：WHERE(a,b)=(1,2)

当需要使用IN()将多个列与值的多个组合进行比较时，它可以非常方便：

SELECT * FROM YourTable

WHERE (col1,col2) IN((1,2),(2,3),(4,4)...)

代替：

SELECT * FROM YourTable

WHERE (col1 = 1 and col2 = 2) OR

(col1 = 2 and col2 = 3) OR

(col1 = 4 and col2 = 4) OR

....

在查看了两个查询的执行计划之后，我可以说在Oracle(使用IN()基本相同的情况下)，优化器会以相同的方式进行评估，同时使用索引：

独立条件：

EXPLAIN PLAN FOR

SELECT * FROM dim_remedy_tickets_cache t

where t.tt_id = '1' and t.region_name = 'one';

6 | 0 | SELECT STATEMENT | | 1 | 311 | 30 (0)| 00:00:01 |

7 | 1 | TABLE ACCESS BY INDEX ROWID| DIM_REMEDY_TICKETS_CACHE | 1 | 311 | 30 (0)| 00:00:01 |

8 | 2 | INDEX RANGE SCAN | DIM_REMEDY_TICKETS_HISTORYPK | 1 | | 20 (0)| 00:00:01 |

组合条件：

EXPLAIN PLAN FOR

SELECT * FROM dim_remedy_tickets_cache t

where (t.tt_id,t.region_name) in (('1','one'))

6 | 0 | SELECT STATEMENT | | 1 | 311 | 30 (0)| 00:00:01 |

7 | 1 | TABLE ACCESS BY INDEX ROWID| DIM_REMEDY_TICKETS_CACHE | 1 | 311 | 30 (0)| 00:00:01 |

8 | 2 | INDEX RANGE SCAN | DIM_REMEDY_TICKETS_HISTORYPK | 1 | | 20 (0)| 00:00:01 |

我假设所有的RDBMS都会对这个查询进行评估。

Row constructors are legal in other contexts. For example, the following two statements are semantically equivalent (and are handled in the same way by the optimizer):

所以：

缺点 – 一些人可能不太可读，但基本上没有缺点。

优点 – 较少的代码，以及使用IN()的多列比较的组合：