长查询中的 MySQL 变量

Question

我认为 mysql 5.6 上的 mysql 查询中的变量有问题:

SELECT DISTINCT TRIM(TRAILING '.' FROM merge_s.rdata) AS `content`,
            '120' AS ttl,
            `merge_s`.`pri` AS `prio`,
            (SELECT type FROM types WHERE type = @type:='MX') AS `type`,
            (SELECT id FROM soa WHERE CONCAT(SUBSTRING_INDEX((SELECT @rr:='mina.net'),'.',-2),'.')=soa.origin) AS domain_id,
            `merge_s`.`rr` AS `rr`
    FROM (`merge_s` LEFT JOIN `merge_s` `db2` ON
            (((`merge_s`.`rr` = `db2`.`rr`) AND (`merge_s`.`pri` < `db2`.`pri`) and (`merge_s`.`type` = `db2`.`type`))))
    WHERE ((`merge_s`.`status` = '1') OR (`merge_s`.`type` = 'NS') OR (`merge_s`.`type` = 'SOA')) AND
            (isnull(`db2`.`pri`) OR (`merge_s`.`type` = 'MX')) AND
            (merge_s.type=@type AND merge_s.rr=@rr)

查询返回正确但没有行。同样的查询，我用手替换最后一行 (merge_s.type=@type AND merge_s.rr=@rr) 与此变量中的 i 方面返回正确的 2 行

SELECT DISTINCT TRIM(TRAILING '.' FROM merge_s.rdata) AS `content`,
            '120' AS ttl,
            `merge_s`.`pri` AS `prio`,
            (SELECT type FROM types WHERE type = @type:='MX') AS `type`,
            (SELECT id FROM soa WHERE CONCAT(SUBSTRING_INDEX((SELECT @rr:='mina.net'),'.',-2),'.')=soa.origin) AS domain_id,
            `merge_s`.`rr` AS `rr`
    FROM (`merge_s` LEFT JOIN `merge_s` `db2` ON
            (((`merge_s`.`rr` = `db2`.`rr`) AND (`merge_s`.`pri` < `db2`.`pri`) and (`merge_s`.`type` = `db2`.`type`))))
    WHERE ((`merge_s`.`status` = '1') OR (`merge_s`.`type` = 'NS') OR (`merge_s`.`type` = 'SOA')) AND
            (isnull(`db2`.`pri`) OR (`merge_s`.`type` = 'MX')) AND
            (merge_s.type='MX' AND merge_s.rr='mina.net')

2 行:

content         ttl     prio    type    domain_id   rr  
mail.mina.net   120     50       MX       3         mina.net
mail.tes.net    120     60       MX       3         mina.net

Answer 1

在您的查询中，变量在 select 子句的子查询中设置:

SELECT DISTINCT TRIM(TRAILING '.' FROM merge_s.rdata) AS `content`,
       '120' AS ttl, `merge_s`.`pri` AS `prio`,
        (SELECT type FROM types WHERE type = @type:='MX') AS `type`,
        (SELECT id
         FROM soa
         WHERE CONCAT(SUBSTRING_INDEX((SELECT @rr:='mina.net'),'.',-2),'.')=soa.origin
        ) AS domain_id,
        `merge_s`.`rr` AS `rr`
FROM `merge_s` LEFT JOIN
     `merge_s` `db2`
      ON(((`merge_s`.`rr` = `db2`.`rr`) AND (`merge_s`.`pri` < `db2`.`pri`) and (`merge_s`.`type` = `db2`.`type`))))
WHERE ((`merge_s`.`status` = '1') OR (`merge_s`.`type` = 'NS') OR (`merge_s`.`type` = 'SOA')) AND
      (isnull(`db2`.`pri`) OR (`merge_s`.`type` = 'MX')) AND
      (merge_s.type=@type AND merge_s.rr=@rr);

您发现，在执行此类子句之前，SQL 语句中会发生大量处理。一般来说，from 子句是第一个执行的子句，select 是最后一个执行的子句。我总是使用交叉连接将变量声明放在主from子句中。这个版本应该可以工作:

SELECT DISTINCT TRIM(TRAILING '.' FROM merge_s.rdata) AS `content`,
       '120' AS ttl, `merge_s`.`pri` AS `prio`,
        (SELECT type FROM types WHERE type = @type) AS `type`,
        (SELECT id
         FROM soa
         WHERE CONCAT(SUBSTRING_INDEX( @rr,'.',-2),'.')=soa.origin
        ) AS domain_id,
        `merge_s`.`rr` AS `rr`
FROM `merge_s` LEFT JOIN
     `merge_s` `db2`
      ON(((`merge_s`.`rr` = `db2`.`rr`) AND (`merge_s`.`pri` < `db2`.`pri`) and
          (`merge_s`.`type` = `db2`.`type`)))) cross join
      (select @type:='MX', @rr:='mina.net') const
WHERE ((`merge_s`.`status` = '1') OR (`merge_s`.`type` = 'NS') OR (`merge_s`.`type` = 'SOA')) AND
      (isnull(`db2`.`pri`) OR (`merge_s`.`type` = 'MX')) AND
      (merge_s.type=@type AND merge_s.rr=@rr);