MySQL中如何在元组比较中使用多种运算符？

MySQL的IN操作符仅支持完全等值匹配，没法在元组查询中直接使用>=这类不等运算符，不过可以通过以下几种方式实现你的需求：

1. 单组条件场景 #

如果只是针对一组(x,y,z)的查询，直接写常规WHERE条件即可，注意用更准确的时间差函数避免单位歧义：

SELECT * FROM tbl
WHERE col_a = 'x_value' 
  AND col_b = 'y_value' 
  AND TIMESTAMPDIFF(SECOND, created_at, NOW()) >= z_value;

注：TIMESTAMPDIFF可指定单位（如SECOND/HOUR/DAY），比直接NOW()-created_at更精准，避免不同时间类型计算的误差。

2. 多组条件场景 #

如果需要匹配多组(x,y,z)组合，有两种常用方法：

方法一：用OR连接多个条件块 #

适合组数较少的情况：

SELECT * FROM tbl
WHERE (col_a = 'x1' AND col_b = 'y1' AND TIMESTAMPDIFF(HOUR, created_at, NOW()) >= z1)
   OR (col_a = 'x2' AND col_b = 'y2' AND TIMESTAMPDIFF(HOUR, created_at, NOW()) >= z2)
   -- 按需添加更多OR条件块

方法二：JOIN临时数据集（推荐组数多的场景） #

通过派生表存储所有条件组，再与原表关联，语法更整洁：

MySQL 8.0.19+版本（支持VALUES子句）

SELECT t.* 
FROM tbl t
JOIN (
  VALUES 
    ('x1', 'y1', z1),
    ('x2', 'y2', z2),
    ('x3', 'y3', z3)
) AS conditions(a, b, min_age)
ON t.col_a = conditions.a 
   AND t.col_b = conditions.b 
   AND TIMESTAMPDIFF(HOUR, t.created_at, NOW()) >= conditions.min_age;

旧版本MySQL（用UNION ALL构建派生表）

SELECT t.* 
FROM tbl t
JOIN (
  SELECT 'x1' AS a, 'y1' AS b, z1 AS min_age
  UNION ALL
  SELECT 'x2' AS a, 'y2' AS b, z2 AS min_age
  UNION ALL
  SELECT 'x3' AS a, 'y3' AS b, z3 AS min_age
) AS conditions
ON t.col_a = conditions.a 
   AND t.col_b = conditions.b 
   AND TIMESTAMPDIFF(HOUR, t.created_at, NOW()) >= conditions.min_age;

内容的提问来源于stack exchange，提问作者Aizaz