php - INSERT 中的子查询在 PDO 中的执行方式与 SQL 不同

Question

我想将一个新的数据集插入到一个tab带有外部数据的 MySQL 表中，但也可以otherTab使用其他表的主键和另一个条件从另一个表中插入数据。但是，可能是请求的行根本不存在（不再存在），或者由于其他提供的数据不匹配而导致结果集为空。然后，原来的 INSERT 应该会失败。所有列都禁止为 NULL。

我的第一次尝试是：

INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234

但它的问题是返回的空结果集被强制转换为 in 列的类型tab，导致 a0作为条目数据。

查询要高效，避免不必要的查询。这就是我通过四个子查询实现它的方式：

INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234
WHERE EXISTS (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT')
  AND EXISTS (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG')

我尝试使用其他构造，例如(SELECT IFNULL(SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT', NULL))强制NULL甚至将字符串放入目标列，但它也被强制转换为一个0或某个值。

这是 dbFiddle 的代码：

代码

CREATE TABLE `tab` (
  `id` int NOT NULL,
  `seUuid4` binary(16) NOT NULL,
  `rxUuid4` binary(16) NOT NULL,
  `text` varchar(16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `otherTab` (
  `uuid4` binary(16) NOT NULL,
  `lgUuid4` binary(16) NOT NULL,
  `data` varchar(16)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `otherTab`
  ADD PRIMARY KEY(`uuid4`);
ALTER TABLE `tab`
  ADD CONSTRAINT `tab_ibfk_1` FOREIGN KEY (`rxUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `tab_ibfk_2` FOREIGN KEY (`seUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO `otherTab` (uuid4, lgUuid4, data) VALUES
(UNHEX("22224444aaaa49c782408b2fe8c4dee0"), UNHEX("00001234aaaa4444aaaa432187654321"), "JPG"),
(UNHEX("11113333aaaa49c782408b2fe8c4dee0"), UNHEX("12340000bbbb6666bbbb432187654321"), "TXT");

INSERT INTO tab (id, seUuid4, rxUuid4, text)
SELECT
    1,
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('00001234aaaa4444aaaa432187654321') AND data = 'JPK' LIMIT 0,1),
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('12340000bbbb6666bbbb432187654321') AND data = 'TXT' LIMIT 0,1),
    'some text'

有趣的是，这完全符合预期：注意JPK代替JPG. 我验证了我的代码，并且 PDO 准备好的语句触发了完全相同的命令，但它INSERT INTO tab (id, seUuid4, rxUuid4) VALUES (1, 0x00000000000000000000000000000000, 0x00000000000000000000000000000000, 'datatext');在 SQL 客户端和 phpMyadmin 传递预期的cannot insert null错误消息时被插入。

我在 PDO 选项中找不到任何内容。如果有帮助，我将 PDO 与模拟的准备好的语句一起使用，但也尝试过没有任何变化。

PS：我已经发布在 dba.stackexchange.com/posts/276868

标签： phpmysqlpdo

Answer 1

您可以使用子查询：

INSERT INTO tab (id, extid1, extid2, value)
SELECT 
FROM (
    SELECT 
       1 id,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT') extid1,
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG') extid2
       1234 value
) t
WHERE extid1 IS NOT NULL and extid2 IS NOT NULL

或者，可能更好的是，您可以CROSS JOIN使用两个子查询：

INSERT INTO tab (id, extid1, extid2, value)
SELECT 1, t1.id, t2.id, 1234
FROM (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT') t1,
CROSS JOIN (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG') t2

实际上，由于您重新使用与过滤相同的值，因此两个exists子查询可能就足够了：

INSERT INTO tab (id, extid1, extid2, value)
SELECT t.*
FROM (SELECT 1 id, 12 extid1, 34 extid2, 1234 value) t
WHERE EXISTS (SELECT 1 FROM otherTab t1 WHERE t1.id = t.extid1 AND t1.data = 'TXT')
  AND EXISTS (SELECT 1 FROM otherTab t1 WHERE t1.id = t.extid2 AND t1.data = 'JPG')