By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `test_table` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`booleanField` BOOLEAN,
PRIMARY KEY (`id`)
);
CREATE PROCEDURE test_procedure2(
IN DATA JSON
)
BEGIN
SET @someBoolean := CASE JSON_UNQUOTE(JSON_EXTRACT(DATA, '$.someBoolean'))
WHEN 'true' THEN 1
WHEN 'false' THEN 0
END;
INSERT INTO test_table (
booleanField
) VALUES (
@someBoolean
);
SELECT
@someBoolean AS receivedValue,
id,
booleanField
FROM test_table;
END
CALL test_procedure2('{"someBoolean": true}');
CALL test_procedure2('{"someBoolean": false}');
CALL test_procedure2('{"someBoolean": null}');
CALL test_procedure2('{"someBoolean": "none"}');
CALL test_procedure2('{"someBoolean": 1}');
CALL test_procedure2('{"wrongBoolean": true}');
| receivedValue | id | booleanField |
|---|---|---|
| 1 | 1 | 1 |
| receivedValue | id | booleanField |
|---|---|---|
| 0 | 1 | 1 |
| 0 | 2 | 0 |
| receivedValue | id | booleanField |
|---|---|---|
| null | 1 | 1 |
| null | 2 | 0 |
| null | 3 | null |
| receivedValue | id | booleanField |
|---|---|---|
| null | 1 | 1 |
| null | 2 | 0 |
| null | 3 | null |
| null | 4 | null |
| receivedValue | id | booleanField |
|---|---|---|
| null | 1 | 1 |
| null | 2 | 0 |
| null | 3 | null |
| null | 4 | null |
| null | 5 | null |
| receivedValue | id | booleanField |
|---|---|---|
| null | 1 | 1 |
| null | 2 | 0 |
| null | 3 | null |
| null | 4 | null |
| null | 5 | null |
| null | 6 | null |