Concat优化

Question

CONCAT(me.nativeName,tp.nativeName) NOT IN (SELECT CONCAT(e_node,e_n_t)
                                                          FROM m_tp_info
                                                          WHERE m_id = mid)

有什么方法可以优化这个concat吗？

Answer 1

对于大多数DBMS，我会以语义上有意义的方式来处理：

SELECT  ...
FROM    ...
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    m_tp_info AS i
            WHERE   i.mid = me.m_id
            AND     CONCAT(i.e_node, i.e_n_t) = CONCAT(me.nativeName,tp.nativeName)
        )

但是MySQL通常会优化LEFT JOIN/IS NULL better than NOT IN or NOT EXISTS，所以你可以试试：

SELECT  ...
FROM    ...
        LEFT JOIN m_tp_info AS i
            ON i.mid = me.m_id
            AND CONCAT(i.e_node, i.e_n_t) = CONCAT(me.nativeName,tp.nativeName)
WHERE   i.mid IS NULL;

当然，如果您只是使用CONCAT，因为您想要比较两列，而NOT IN不允许这样做，那么您可以只连接这两列：

SELECT  ...
FROM    ...
        LEFT JOIN m_tp_info AS i
            ON i.mid = me.m_id
            AND i.e_node = me.nativeName
            AND i.e_n_t = tp.nativeName
WHERE   i.mid IS NULL;

或

SELECT  ...
FROM    ...
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    m_tp_info AS i
            WHERE   i.mid = me.m_id
            AND     i.e_node = me.nativeName
            AND     i.e_n_t = tp.nativeName
        );