问题

If the id in table1 does not exist, and the nested SELECT is null, you can make the transaction fail by adding a condition to check if the SELECT statement returns any rows. If it doesn't return any rows, the transaction will not insert anything into relation_table1_table2, effectively failing.

Here's the modified code:

INSERT INTO table2 ..... ;

SET @table2_insert_id = LAST_INSERT_ID();

INSERT INTO relation_table1_table2 (table1_id, table2_id)
SELECT id, @table2_insert_id FROM table2
WHERE table1.column1 = 'ABC'
AND EXISTS (SELECT 1 FROM table1 WHERE column1 = 'ABC');

By using the EXISTS condition, the second INSERT will only occur if there is a matching row in table1 where column1 is 'ABC'. If no such row exists, the transaction will fail to insert into relation_table1_table2.

INSERT INTO table2 ..... ;

SET @table2_insert_id = LAST_INSERT_ID();\

INSERT INTO relation_table1_table2 (table1_id, table2_id)
SELECT id, @table2_insert_id FROM table2
WHERE table1.column1 = 'ABC';

答案1

得分: 1

你可以在`INSERT`后调用`ROW_COUNT()`来获取插入的行数。如果为零，则触发错误。

IF ROW_COUNT() == 0
THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '未找到要插入的内容';
END IF;

如果不能使用SQL条件语句，请在JavaScript中执行条件检查。

db.query(INSERT INTO relation_table1_table2 (table1_id, table2_id)
SELECT id, @table2_insert_id FROM table2
WHERE table1.columnX = 'ABC', function(err, res) {
if (err) {
throw "查询失败";
}
if (res.affectedRows == 0) {
db.rollback();
throw "未插入任何内容";
}
});

<details>
<summary>英文:</summary>

You can call `ROW_COUNT()` after the `INSERT` to get the number of rows inserted. If it&#39;s zero, raise an error.

IF ROW_COUNT() == 0
THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nothing found to insert';
END IF;

If you can't use SQL conditional statements, do the condition in JavaScript.

db.query(INSERT INTO relation_table1_table2 (table1_id, table2_id)
SELECT id, @table2_insert_id FROM table2
WHERE table1.columnX = 'ABC', function(err, res) {
if (err) {
throw "Query failed";
}
if (res.affectedRows == 0) {
db.rollback();
throw "Nothing inserted";
}
});

</details>