REGEXP를 사용한 동일한 쿼리, 다른 플랫폼, 다른 결과

Question

이 쿼리는 PHP에 있습니다. 오류 및 값 없음.

$query = $connection->prepare("SELECT main.a as pkg, COUNT(1) as count
FROM
(SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, '$[0]'))) AS a FROM (SELECT packages, JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '$.packages[*]') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, 'all',  'openstack-nova-api-%', NULL, '$.packages[*]') IS NOT NULL AND cloud = 'ch-dc-os-dhn-56') AS sub UNION SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, '$[1]'))) AS a FROM 
(SELECT packages, JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '$.packages[*]') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '$.packages[*]') IS NOT NULL AND cloud = 'ch-dc-os-dhn-56') AS sub 
UNION
) AS main
WHERE main.a is not null AND main.a REGEXP '((?i)openstack-nova-api-(?-i))\\d.*'
GROUP BY main.a");


 $query->execute(); $result = $query->fetchAll(\PDO::FETCH_ASSOC); echo json_encode($result);

mysql 클라이언트를 사용하는 경우. 내 레코드 수를 반환합니다. 문제 없다.

이것이 PHP에서 작동하지 않는 이유는 무엇입니까 ?? REGEX 패턴을 이스케이프해야합니까 ?? 말이 돼?

Answer 1

다음 중 하나가 작동 할 것 같습니다.

$query = $connection->prepare('SELECT main.a as pkg, COUNT(1) as count
FROM
(SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, \'$[0]\'))) AS a FROM (SELECT packages, JSON_SEARCH(packages, \'all\', \'openstack-nova-api-%\', NULL, \'$.packages[*]\') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, \'all\',  \'openstack-nova-api-%\', NULL, \'$.packages[*]\') IS NOT NULL AND cloud = \'ch-dc-os-dhn-56\') AS sub UNION SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, \'$[1]\'))) AS a FROM 
(SELECT packages, JSON_SEARCH(packages, \'all\', \'openstack-nova-api-%\', NULL, \'$.packages[*]\') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, \'all\', \'openstack-nova-api-%\', NULL, \'$.packages[*]\') IS NOT NULL AND cloud = \'ch-dc-os-dhn-56\') AS sub 
UNION
) AS main
WHERE main.a is not null AND main.a REGEXP \'((?i)openstack-nova-api-(?-i))\\d.*\'
GROUP BY main.a');

또는

$query = $connection->prepare("SELECT main.a as pkg, COUNT(1) as count
FROM
(SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, '\$[0]'))) AS a FROM (SELECT packages, JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '\$.packages[*]') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, 'all',  'openstack-nova-api-%', NULL, '\$.packages[*]') IS NOT NULL AND cloud = 'ch-dc-os-dhn-56') AS sub UNION SELECT node, JSON_EXTRACT(packages, JSON_UNQUOTE(JSON_EXTRACT(PATH, '\$[1]'))) AS a   FROM 
 (SELECT packages, JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '\$.packages[*]') AS PATH, cloud, node FROM Node WHERE JSON_SEARCH(packages, 'all', 'openstack-nova-api-%', NULL, '\$.packages[*]') IS NOT NULL AND cloud = 'ch-dc-os-dhn-56') AS sub 
UNION
) AS main
WHERE main.a is not null AND main.a REGEXP '((?i)openstack-nova-api-(?-i))\\d.*'
GROUP BY main.a");