Question 22176 in Mysql Question posted in
The official MySQL documentation can be found here.

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

`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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 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)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search