同じクエリ、異なるプラットフォーム、REGEXPでの異なる結果

Aug 21 2020

私は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で機能しないのですか?正規表現パターンをエスケープする必要がありますか?それは理にかなっていますか?

回答

Nathanael Aug 21 2020 at 18:41

私は次のいずれかがうまくいくと思います

$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");