MySQL仅在条件为真时才插入 [英] MySQL insert only if a condition is true

问题描述

我有几个插入语句，只有在它们为true时才要执行.

I have several insert statements that I only want to execute if they are true.

这是我的例子:

START TRANSACTION;

INSERT INTO fields (field_name, control_type_id, needs_approval)
VALUES 
('Array Photos', 3, 0);

INSERT INTO field_to_job_type (field_id, job_type_id, sequence_number, parent_id)
VALUES
(last_insert_id(), (SELECT job_type_id FROM job_types WHERE job_type_name = 'Cash'), 1, (SELECT field_id FROM fields where field_name = 'Photo Pack'));

COMMIT;

现在，如果此查询只有1个结果，我真的只想运行第二个查询:

Now, I only really want to run the second query if there is only 1 result for this query:

SELECT job_type_id FROM job_types WHERE job_type_name = 'Cash'.

如果该查询返回2个结果，则我想放弃交易.

If that query returns 2 results, I would want to abandon the transaction.

有可能吗?我尝试过的事情:

Is that possible? Something I tried:

START TRANSACTION;
INSERT ...;
INSERT ...;
IF(SELECT count(job_type_name) FROM job_types WHERE job_type_name = 'Cash') = 1, 'COMMIT', 'ROLLBACK');

显然，这没用.

推荐答案

将计数选择为变量，然后在比较中使用.

Select the count into a variable and then use that in the comparison.

DECLARE total_row INT DEFAULT 0

START TRANSACTION;

INSERT INTO fields (field_name, control_type_id, needs_approval)
VALUES ('Array Photos', 3, 0);


SELECT count(job_type_name) FROM job_types WHERE job_type_name = 'Cash' INTO total_rows;

IF total_rows =2 THEN
    ROLLBACK;
ELSE

    INSERT INTO field_to_job_type (field_id, job_type_id, sequence_number, parent_id)
    VALUES (last_insert_id(), (SELECT job_type_id FROM job_types WHERE job_type_name = 'Cash'), 1, (SELECT field_id FROM fields where field_name = 'Photo Pack'));
   COMMIT;
END IF;

这篇关于MySQL仅在条件为真时才插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！