mysql - 如果内部查询没有找到任何内容，则取消插入

Question

我收到以下查询:

INSERT INTO contracts_settings (contract_id, setting_id, setting_value)
VALUES (:contract_id, (
    SELECT  setting_id
    FROM    settings
    WHERE   setting_type = :setting_type
        AND setting_name = :setting_name
    LIMIT   1
), :setting_value)
ON DUPLICATE KEY UPDATE setting_value = :setting_value

使用 PHP PDO::bindBalue 将带有前缀 : 的值替换为数据。

如果内部查询什么也没找到(它返回NULL)还要INSERT一个NULL语句。如何避免这种情况？

谢谢。

Answer 1

将 INSERT ... VALUES 语法转换为 INSERT ... SELECT :

INSERT INTO contracts_settings 
  (contract_id, setting_id, setting_value)
SELECT 
    :contract_id,
    setting_id,
    :setting_value
FROM  settings
WHERE setting_type = :setting_type
  AND setting_name = :setting_name
LIMIT   1

ON DUPLICATE KEY UPDATE 
    setting_value = :setting_value ;