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

`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

CodePudding user response：

MySQL and SQL Server saves Boolean/Bit as 0 and 1.

It is very practical for when you might need to use something like SUM, etc.

You can get True or False by using a CASE WHEN or a FILTER.

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

CodePudding user response：

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)