MySQL Boolean Type

Question

I'm really torn on what to do on this one. For the past 10 years I've just put up with this but I think it's time to decide the right way to handle this issue.

According to the internet, MySQL doesn't support a native datatype, which makes sense why whenever I choose "BOOLEAN" I see the column end up as a tinyint(1).

I have no problem with storing data as a tinyint with a 1 or 0 value, however I do have a problem with the fact that when I select many rows from the database with this column I get a string "1" or "0" instead of a PHP native bool true/false. PHP can still deal with this variable as a boolean value of course as it seems to automatically know what I want it to do whenever I do == 0 or == 1 or even == '0' or == '1' if I'm really feeling up to it.

So, here I go, taking this data and running it into PHP's json_encode function and outputting it when my Backbone/Handlebars implementation makes an ajax call, and getting a nice beautiful JSON object (usually with many rows' data in it) with this: {"myVariable":"1"} or this {"myVariable":"0"}.

I'm sure you can see the issue with wanting to do something like this in Handlebars.js:

{{#if myVariable}} 
    Do this
{{else}}
    Do that
{{/if}}

Now, what I really DON'T want to do is something like this:

<?php
for($i = 0; $i < count($recordSet); $i++) {
    $recordSet[$i]['myVariable'] = (bool)$recordSet[$i]['myVariable'];
}
?>

Being that the application I'm building has thousands of columns in the database I can't (or just should need to) just run the above code for every column I want to have act like a bool in my javascript. And I really don't want to do this (although it would probably be easiest)

Handlebars.registerHelper('ifTrue', function(a, b) {
    // Cast the compared value to a bool and then run a regular handlebars #if statement
});

Is there a better way? I'm using code igniter... can the database class be modified in some way to detect a tinyint(1) column and automatically cast it to a bool and then just do this until MySQL supports a true boolean column? I know they're planning it, but...

That's a great solution for what data type to use, however it doesn't quite answer how to get a pure PHP true/false value out of a query efficiently, it more outlines which data types store the value. Whether I'm using BIT or tinyint(1), the same issue persists with getting a string "1" or "0" back when running the results of the query through `json_encode`. — Benjamin Oman, Apr 15 '13 at 18:12
`select , CASE blnColumn WHEN '0' THEN 'FALSE' WHEN '1' THEN 'TRUE'` - http://www.dreamincode.net/forums/topic/296164-forget-how-to-convert-bit-to-truefalse/ — David Starkey, Apr 15 '13 at 18:24
Or else, you can always cast myVariable as number client side. Something like: {{#if +myVariable}}. — Elad, Apr 24 '13 at 17:26
tinyint(1) should work just fine for your purpose. Works with both PHP and javascript true/false syntax as long as you decode the json object correctly on the client (more on zeros and js: http://stackoverflow.com/questions/7615214/in-javascript-why-is-0-equal-to-false-but-not-false-by-itself) — Michael Krikorev, Jul 07 '13 at 21:15

score 1 · Answer 1 · answered Jul 12 '13 at 00:33

Keep in mind that by default, columns values returned by the PHP/MySQL drivers are just strings (i.e. untyped arrays of bytes). This short snippet demonstrates it:

// mysqli driver
$c = mysqli_connect ($host, $user, $pass, $dbname);
$r = mysqli_query($c, 'SELECT * FROM foo');
$f = mysqli_fetch_array($r);
foreach ($f as $k => $v) {
    echo "$k => $v [" . gettype($v) . ']' . PHP_EOL;
}

// PDO
$c = new PDO("mysql:dbname=$dbname;host=$host", $user, $pass);
$r = $c->query('SELECT * FROM foo');
$f = $r->fetch();
foreach ($f as $k => $v) {
    echo "$k => $v [" . gettype($v) . ']' . PHP_EOL;
}

"By default", because one can provide the driver with a mapping so that it can cast these strings into native PHP types. For this purpose, mysqli has mysqli_result::fetch_object(), PDO has PDOStatement::fetchObject().

While it is possible to retreive the actual column type from its metadata, or even from the table definition, in the typical case, the client application knows it in advance.

The bottomline is: you do want to do this (or something equivalent):

$recordSet[$i]['myBoolColumn'] = (bool)$recordSet[$i]['myBoolColumn'];

... because in fact you should do this too:

$recordSet[$i]['myIntColumn'] = (int)$recordSet[$i]['myIntColumn'];

But PHP being very laxist with regards to typing, I wouldn't worry too much about this, after all.

MySQL Boolean Type

1 Answers1