如何在PHP MySQL中对比JSON数组列与对象列并获取最大折扣

我有如下数据库结构及数据：

CREATE TABLE `test`.`patients` ( `id` BIGINT NOT NULL AUTO_INCREMENT , `branchID` INT NOT NULL , `patientNo` VARCHAR(20) NOT NULL , `billingAccounts` VARCHAR(50) NOT NULL , `firstName` VARCHAR(20) NOT NULL , `lastName` VARCHAR(20) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `patients` (`id`, `branchID`, `patientNo`, `billingAccounts`, `firstName`, `lastName`) VALUES (NULL, '1', 'PN017830', '["-1","-2","7632","7774"]', 'John', 'Daka'), (NULL, '1', 'PN017890', '["-1","-2","8120","7742"]', 'Ann', 'Mikail');

CREATE TABLE `test`.`products` ( `id` INT NOT NULL AUTO_INCREMENT , `type` ENUM('pharmaceutical','other') NOT NULL , `productCode` VARCHAR(50) NOT NULL , `brand` VARCHAR(50) NOT NULL , `manufacturer` INT NOT NULL , `generics` TEXT NOT NULL , `privateBranchID` INT NOT NULL , `regID` INT NOT NULL , `regDate` DATETIME NOT NULL , `status` ENUM('active','deactivated') NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `products` (`id`, `type`, `productCode`, `brand`, `manufacturer`, `generics`, `privateBranchID`, `regBy`, `regDate`, `status`) VALUES (10, 'pharmaceutical', '500015806877', 'gaviscon', '217', '["magaldrate","simethicone"]', '0', '1', CURRENT_TIMESTAMP, 'active'), (11, 'pharmaceutical', '7640153080325', 'lofral', '199', '["amlodipine"]', '0', '1', CURRENT_TIMESTAMP, 'active');

CREATE TABLE `test`.productdiscounts ( `id` BIGINT NOT NULL AUTO_INCREMENT , `branchID` INT NOT NULL , `productID` BIGINT NOT NULL , `accountID` BIGINT NOT NULL , `discount` DECIMAL NOT NULL , `isActive` ENUM('1','0') NOT NULL , `regBy` INT NOT NULL , `regTimestamp` DATETIME on update CURRENT_TIMESTAMP NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `productdiscounts` (`branchID`, `productID`, `accountID`, `discount`, `isActive`, `regBy`, `regTimestamp`) VALUES ('1', '10', '7723', '90', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '7724', '70', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '-2', '70', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '7720', '55', '1', '1', '2022-08-25 08:01:59');

$searchSQL="
    select 
        distinct 
        products.id,
        products.type as productType,
        products.brand,
        products.status,
        products.productCode,
        products.generics,
        coalesce((select discount from productdiscounts where(productID=products.id and branchID=1 and isActive='1' and patients.billingAccounts like concat('%\"',productdiscounts.accountID,'\"%')) order by discount desc limit 1),0.00) as discount,
        concat('[',(select group_concat('{\"productID\":\"',productdiscounts.productID,'\",\"accountID\":\"',productdiscounts.accountID,'\",\"discount\":\"',productdiscounts.discount,'\",\"accountName\":\"',accounts.name,'\",\"accountNo\":\"',accounts.accountNo,'\"}') from productdiscounts 
        left join accounts on(productdiscounts.accountID=accounts.id)
        where(productdiscounts.productID=products.id and productdiscounts.branchID = 1 and productdiscounts.isActive = '1' )),']') as allDiscounts
    from products 
    left join stock on (products.id=stock.productID) 
    left join pricetags on (stock.priceTag=pricetags.id) 
    left join countries on (products.manufacturer=countries.id)
    left join diagnosis on (diagnosis.diagnosisRef='')
    left join patients on (patients.id=10)
    where(
        products.productCode='' or 
        products.generics like concat('%\"','magaldrate','%\"%') or  
        products.brand like concat('% ','gaviscon','%')
        and (stock.isActive='1' and products.status='active')
        )
    order by products.brand limit 30
";
$productsQ=(new DB)->getRef()->prepare($searchSQL); 
$productsQ->execute([]);
$productsD=$productsQ->fetchAll();

patients表的billingAccounts字段存储为JSON数组格式（如["-1","-2","7632","7774"]），productdiscounts表存储各账户的产品折扣信息。当前使用含concat/group_concat的SQL查询获取匹配患者账单账户的产品最大折扣，但查询速度较慢。现需优化该查询，在不使用concat/group_concat的前提下，高效返回符合条件的最大折扣：即获取对该产品有折扣且存在于患者账单账户中的所有账户里的最大折扣值。

核心优化点 #

1. 替换模糊匹配为JSON函数：用MySQL原生JSON_CONTAINS函数直接匹配JSON数组中的账户ID，避免LIKE拼接字符串的低效操作，匹配更精准。
2. 用JOIN+聚合函数替代关联子查询：将单条产品的子查询逻辑改为JOIN后用MAX()聚合，减少重复查询开销。
3. 移除无效关联：删掉diagnosis表的无意义关联，降低查询复杂度。
4. 预取患者账户：单独查询目标患者的账单账户，避免主查询重复关联patients表。

优化后的SQL #

-- 预取目标患者的账单账户JSON
SET @patientAccounts = (SELECT billingAccounts FROM patients WHERE id = 10);

SELECT
    p.id,
    p.type AS productType,
    p.brand,
    p.status,
    p.productCode,
    p.generics,
    COALESCE(MAX(pd.discount), 0.00) AS discount
FROM products p
LEFT JOIN stock s ON p.id = s.productID
LEFT JOIN pricetags pt ON s.priceTag = pt.id
LEFT JOIN countries c ON p.manufacturer = c.id
LEFT JOIN productdiscounts pd 
    ON pd.productID = p.id 
    AND pd.branchID = 1 
    AND pd.isActive = '1'
    AND JSON_CONTAINS(@patientAccounts, CONCAT('"', pd.accountID, '"'))
WHERE
    (p.productCode = '' 
     OR p.generics LIKE '%\"magaldrate\"%' 
     OR p.brand LIKE '%gaviscon%')
    AND s.isActive = '1' 
    AND p.status = 'active'
GROUP BY p.id, p.type, p.brand, p.status, p.productCode, p.generics
ORDER BY p.brand
LIMIT 30;

优化后的PHP代码 #

// 先获取目标患者的账单账户JSON
$getAccountsSQL = "SELECT billingAccounts FROM patients WHERE id = 10";
$accountsQ = (new DB)->getRef()->prepare($getAccountsSQL);
$accountsQ->execute();
$patientAccounts = $accountsQ->fetchColumn();

// 构建优化后的查询SQL
$searchSQL = "
    SELECT
        p.id,
        p.type AS productType,
        p.brand,
        p.status,
        p.productCode,
        p.generics,
        COALESCE(MAX(pd.discount), 0.00) AS discount
    FROM products p
    LEFT JOIN stock s ON p.id = s.productID
    LEFT JOIN pricetags pt ON s.priceTag = pt.id
    LEFT JOIN countries c ON p.manufacturer = c.id
    LEFT JOIN productdiscounts pd 
        ON pd.productID = p.id 
        AND pd.branchID = 1 
        AND pd.isActive = '1'
        AND JSON_CONTAINS(?, CONCAT('\"', pd.accountID, '\"'))
    WHERE
        (p.productCode = '' 
         OR p.generics LIKE '%\"magaldrate\"%' 
         OR p.brand LIKE '%gaviscon%')
        AND s.isActive = '1' 
        AND p.status = 'active'
    GROUP BY p.id, p.type, p.brand, p.status, p.productCode, p.generics
    ORDER BY p.brand
    LIMIT 30;
";

$productsQ = (new DB)->getRef()->prepare($searchSQL);
// 绑定患者账户参数
$productsQ->execute([$patientAccounts]);
$productsD = $productsQ->fetchAll();

性能提升说明 #

• JSON_CONTAINS优势：相比LIKE拼接字符串，JSON_CONTAINS是专门针对JSON的匹配函数，若将billingAccounts字段改为JSON类型，还可创建JSON索引进一步提速。
• JOIN+MAX替代子查询：原查询每个产品都要执行一次子查询找最大折扣，优化后通过一次JOIN+GROUP_BY完成聚合，减少数据库查询次数。
• 移除无效关联：原查询中left join diagnosis on (diagnosis.diagnosisRef='')无业务意义，移除后降低了查询IO开销。

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