Storing booleans in mySql database as integers/boolean/text for true and false?

Publish Date: Mar 13 '22

2 8

I came across an interesting issue. I've had a habit a long time ago to store my boolean values as 1 and 0 in the database, it has many advantages. However I came across a silly trouble, that took lot of my free time to work out. (I'm the kind of coder, who has a backlog but able to work on an issue for weeks if I find the logic to be dodgy, sometimes even change the code back to database level if I feel that'll help me in the long run.)
The trouble is - or correct me if I'm wrong, I looked it up and found no solution - that a html checkbox doesn't understand 0 and 1, it returns false for 1.
I've got lots of switches in my application, that can be turned on and off so the best is to handle this case for once and for all. I am considering testing storing simply boolean values and see what the api returns to the frontend.
How do you store your booleans?

Update: I've tested the mysql boolean, and it returns 0 or 1 LOL.

Update2:
Sending true/false to the database is totally cool, returns no error, stores as 1 and 0. So it doesn't need to be 'translated back' to int from true/false.

Comments 8 total

Marissa BMar 14, 2022
I think you're able to use the BIT type in MySQL, which also has the boolean type. Didn't take long to find on a Google search. Stack Overflow has several threads on it over the years.

dev.mysql.com/doc/refman/8.0/en/bi...

When reading your value back from the database, cast it into whatever your UI needs. Usually there's a layer of logic between the crunchy data stuff and fancy interactive UI stuff.

In something like Sqlite which has very limited types available, I go with the text option 'true' and 'false'.
- dgloriawebMar 14, 2022
  storing string "true" or "false" takes more time to return and slower running in a large app, I assume?
  - Marissa BMar 14, 2022
    Not really, no.
    - dgloriawebMar 14, 2022
      So you've developed a validation on frontend and backend as well that filters out every typo and other words than true/false? Not being mean here, but that's essential if you choose that road.
      - Marissa BMar 14, 2022
        There's no need to do that if you're checking on a situation like whether a checkbox is checked.
        
        If your process involves a user manually entering the words true/false/0/1/etc and you're not validating it before using it on the backend.... That's a big problem for introducing weird issues.
        
        Flipping whatever the checkbox state is into the words true/false to save somewhere should be one line in most languages.
        
        dgloriawebMar 14, 2022
        That's perfectly true. Was overthinking it. :)
Muhammad UzairMar 14, 2022
I am using boolean in my MySql database, it does store boolean as 0, 1 but while reading or writing it treat them as true/false instead of 0/1.

I am using Sequelize ORM, maybe it converts it but it does work
dgloriawebMar 19, 2022
Update2:
Sending true/false to the database is totally cool, returns no error, stores as 1 and 0. So it doesn't need to be 'translated back' to int.

Add comment

dgloriaweb @dgloriaweb

Storing booleans in mySql database as integers/boolean/text for true and false?

Comments 8 total