0

I want to insert a new dataset into a MySQL table tab with external data, but also with data from another table otherTab using the others' table primary key and another condition. However, it could be that the requested row simply does not exist (anymore) or the result set is empty due to a mismatch in the other supplied data. All columns are forbidden to be NULL.

My first attempt was:

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

but the problem with it is that a returned empty result set is cast to the type of the column in tab, leading to a 0 as entry data.

The query shall be efficient and avoid unnecessary querying. This is how I achieve it with four subqueries:

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')

Is this, or a JOIN, or a transaction with a temporary variable and IF construct the only option?

I tried with other constructs, e.g. (SELECT IFNULL(SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT', NULL)) to enforce NULL or even a string into the target column, but it also gets casted to a 0 or some value instead.

EDIT Here is the code for dbFiddle:

code
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'

This interestingly works exactly as expected. I verified my code (PHP - I know..) and the PDO prepared statement fires out exactly the same command, but it gets inserted as INSERT INTO tab (id, seUuid4, rxUuid4) VALUES (1, 0x00000000000000000000000000000000, 0x00000000000000000000000000000000, 'datatext'); while the SQL client and phpMyadmin deliver the expected cannot insert null error message.

I am off to check the PDO options - sorry for the confusion :-)

EDIT2: changed the title name and will leave it open if someone has an idea by chance. Otherwise, I will ask the PHP/PDO experts.

  • Can it be that extid1 has a default value 0? you dn't need a slect fr only ne row, that is used to insert a bunch of rows. – nbk Oct 11 at 20:34
  • No, the extid1 and extid2 fields are set to have no default value and cannot be NULL (actually, these are BINARY(16) fields, but should not make a difference). – naletups Oct 11 at 20:53
  • please post the create table for tab binary(16) was a suprise – nbk Oct 11 at 21:06
  • I tried you insert with you data, but i can't reproduce it see if you can make a fiddle like this db-fiddle.com/f/4yPorU6k3SjQ5nmhgi1wGo/40 – nbk Oct 11 at 21:17
  • Added the dbFiddle code which executes correctly in MySQL. Seems to be an issue with the interfacing PHP/PDO driver. – naletups Oct 11 at 22:44
0

I could fix your problem, with the insert

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

but this gives following error

Schema Error: Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (test.tab, CONSTRAINT tab_ibfk_2 FOREIGN KEY (seUuid4) REFERENCES otherTab (uuid4))

Which means that you must enter there a uuid, which is exists

| improve this answer | |
  • Unfortunately not: it just returns a 0/1 in dbfiddle and in phpmyadmin, leading to your stated error message. As I said above: the INSERT works directly in SQL as expected, but somehow does not throw an error in PHP/PDO. Your suggested code here for example leads to an inserted value of 0x3100000...00 instead of an error. – naletups Oct 11 at 23:31
  • in db-fiddle.com/f/4yPorU6k3SjQ5nmhgi1wGo/41 i can't enter any value, because if the error. PDO should not have have an impacted – nbk Oct 11 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.