boolean

[bokeh]

What data types are most commonly used to represent a boolean in MySQL and why?

[chazzy]

sometimes i use enum(0,1)

because I can do this:

SELECT
CASE `my_column`
WHEN 1 then 'TRUE'
WHEN 0 then 'FALSE'
end
FROM
`my_table`

if for purely reporting capabilities. what is the application

[bokeh]

What is that doing? Does it return a true boolean to the scripting language?

[chazzy]

no, returns the text "true" or "false", it's just easier to read

to most languages, you can only return strings from SQL - in some cases like Pro C/Pro C++ it can map the Oracle Datatypes to valid types in C/C++. Also, you can use databinding techniques (in php, java, etc) to map the query results to variables for some form of data type validation.

[bokeh]

Ok! I was already using ENUM('0','1') so it look like I made a reasonable choice. String "0" evaluates to false in PHP whereas string "false" evaluate to true so for my use I think sticking with zero and one looks preferable.

[mattyblah]

Bit fields are usually used to represent boolean, at least that's what I use. You can only store 0 or 1.