MySQL Booleans and stored procedures

Question

I am trying to save some booleans in a MySQL table via stored procedures from NodeJS, executing it with mysql2.

Important: I did SET @@global.sql_mode= ''; to disable strict mode.

Lets say I have this table

DROP TABLE IF EXISTS `test_table`;
CREATE TABLE IF NOT EXISTS `test_table` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`booleanField` BOOLEAN,
PRIMARY KEY (`id`)
);

And I want to add a new row with a { someBoolean: true }:

DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$

CREATE PROCEDURE test_procedure(
    IN DATA JSON
)

BEGIN

  SET @someBoolean      = JSON_UNQUOTE(JSON_EXTRACT(DATA, '$.someBoolean'));

  INSERT INTO test_table (
    booleanField
  ) VALUES (
    @someBoolean
  );

  SELECT 
    @someBoolean AS receivedValue,
    id,
    booleanField
  FROM test_table;


END $$
DELIMITER ;

And I execute it:

CALL test_procedure('{"someBoolean": true}');

The result of this is a new row with some interesting result:

Running all this code in the MySQL terminal client I receive this:

+---------------+----+--------------+
| receivedValue | id | booleanField |
+---------------+----+--------------+
| true          |  1 |            0 |
+---------------+----+--------------+

So even then the received value was true,the boolean field was set as 0.

I can make it work checking if @someBoolean is a string "true", and setting it to TRUE, etc.

DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$

CREATE PROCEDURE test_procedure(
    IN DATA JSON
)

BEGIN
  DECLARE CASTED_VOTE INT; -- NEW
  SET @someBoolean      = JSON_UNQUOTE(JSON_EXTRACT(DATA, '$.someBoolean'));

  -- NEW
  IF      @someBoolean = "true"  THEN SET  CASTED_VOTE = 1;
  ELSEIF  @someBoolean = "false" THEN SET  CASTED_VOTE = 0;
  ELSE SET                          CASTED_VOTE = NULL;
  END IF;
  -- END NEW

  INSERT INTO test_table (
    booleanField
  ) VALUES (
    CASTED_VOTE
  );

  SELECT 
    CASTED_VOTE, -- NEW
    @someBoolean AS receivedValue,
    id,
    booleanField
  FROM test_table;


END $$
DELIMITER ;

Execute again…

CALL test_procedure('{"someBoolean": true}');

And voila:

+-------------+---------------+----+--------------+
| CASTED_VOTE | receivedValue | id | booleanField |
+-------------+---------------+----+--------------+
|           1 | true          |  1 |            1 |
+-------------+---------------+----+--------------+

But that means that I have to do the same thing over and over in all my queries. What I'm doing wrong? Am I missing something?

Answer 1

CREATE PROCEDURE test_procedure(
    IN DATA JSON
)

BEGIN

  SET @someBoolean      = JSON_UNQUOTE(JSON_EXTRACT(DATA, '$.someBoolean'));

  INSERT INTO test_table (
    booleanField
  ) VALUES (
    @someBoolean = 'true'
  );

  SELECT 
    @someBoolean AS receivedValue,
    id,
    booleanField
  FROM test_table;


END

or

CREATE PROCEDURE test_procedure(
    IN DATA JSON
)

BEGIN

  SET @someBoolean := JSON_UNQUOTE(JSON_EXTRACT(DATA, '$.someBoolean')) = 'true';

  INSERT INTO test_table (
    booleanField
  ) VALUES (
    @someBoolean
  );

  SELECT 
    @someBoolean AS receivedValue,
    id,
    booleanField
  FROM test_table;


END

fiddle

---

what if I want to pass null? CALL test_procedure('{"someBoolean": "null"}'); – Emille C.

SET @someBoolean := CASE JSON_UNQUOTE(JSON_EXTRACT(DATA, '$.someBoolean'))
                    WHEN 'true' THEN 1
                    WHEN 'false' THEN 0
                    END;

Any value except 'true' or 'false' (including the parameter absence) will be treated (and assigned of course) as NULL.

fiddle