How to convert tinyint back to boolean when reading from SQL?

Question

`mysql> select * from movies;
+----------+-------+---------+
| movie_id | title | watched |
+----------+-------+---------+
|        1 | bo    |       0 |
|        2 | NEW   |       0 |
|        3 | NEW 2 |       0 |
+----------+-------+---------+

CREATE TABLE MOVIES (
  movie_id INTEGER NOT NULL AUTO_INCREMENT,
  title VARCHAR(50) NOT NULL,
  watched BOOLEAN NOT NULL,
  PRIMARY KEY (movie_id)
);

` I am having to store the "watched" field as a tiny int instead of typical boolean, I am trying to find a way of converting it back to boolean when reading from table, so I dont have to loop through all responses and convert manually.

ie. {movie_id: 1, title: 'bo', watched: 0} ---> {movie_id: 1, title: 'bo', watched: false}

I have tried select cast but am unfamiliar with the syntax

You have to read the records into a domain object at some point though - so why not just cast the values then rather than in the db layer? — PeteGO
– PeteGO, Commented Nov 20, 2022 at 1:27
"I am having to store the "watched" field as a tiny int instead of typical boolean" => When doing SHOW CREATE TABLE movies, on the table where you have this field defined as BOOLEAN, you will notice the type of the field watched to be a tinyint(1). — Luuk
– Luuk, Commented Nov 20, 2022 at 10:15

nbk · Accepted Answer · 2022-11-20 09:51:29Z

1

MySQL saves Boolean as 0 and 1 as it handles all Boolean that way.

It is very practical, then you can add true or false from a comparison in a SUM without CASE WHEN or a FILTER

You need still to make a condition to give bak True or False, but they only text of course

SELECT
  movie_id , title , 
 CASE WHEN watched = 0 THEN 'False' ELSE 'True' END IF

edited Nov 20, 2022 at 9:51

answered Nov 19, 2022 at 22:34

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave S · Accepted Answer · 2022-11-19 22:42:50Z

0

This is similar to 'IF' in 'SELECT' statement - choose output value based on column values

Borrowing from the answer there,

SELECT movie_id, IF (watched > 0, true, false) as bwatched, ...

Note that this assumes your schema still includes "NOT NULL" for watched. Without that (or some extra code) NULL would become false.

The way "IF()" works is IF(expression , value / expression if true, v /e if false)

edited Nov 19, 2022 at 22:42

answered Nov 19, 2022 at 22:36

Dave S

1,5201 gold badge17 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to convert tinyint back to boolean when reading from SQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related