mysql - postgresql查询到mysql

Question

我想在 MySQL 中创建类似于 PostgreSQL 的查询:

CREATE FUNCTION check_unique_pair(IN id1 INTEGER, IN id2 INTEGER) RETURNS INTEGER AS   $body$
DECLARE retval INTEGER DEFAULT 0;
BEGIN 
 SELECT COUNT(*) INTO retval FROM (
  SELECT * FROM edges WHERE a = id1 AND b = id2
  UNION ALL
  SELECT * FROM edges WHERE a = id2 AND b = id1
)  AS pairs;
RETURN retval;
END
$body$
LANGUAGE 'plpgsql';

ALTER TABLE edges ADD CONSTRAINT unique_pair CHECK (check_unique_pair(a, b) < 1);

我是 MySQL 新手。

Answer 1

查询可以简化为:

SELECT COUNT(*)
FROM edges
WHERE a IN (id1, id2)
AND b in (id1, id2)
AND a <> b
   ;

，它可能会在 {a,b}

上使用复合索引