同じクエリ、異なるプラットフォーム、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");