I converted some TINYINT columns that held 0/1 to BIT, as there would be storage savings. This is on MySQL 5.7, latest DBI, my DBD::mysql is 3 versions old (4.050 - the latest suddenly requires mysql 8). I expected my code to not have to change, as DBI usually does the right thing for bind variables etc. So, since this works the same for both BIT and TINYINT:
$dbh->do("INSERT INTO TABLE thetable (bitcol) VALUES (0)");
I was hoping the bind version to also work:
my $sth = $dbh->prepare("INSERT INTO TABLE thetable (bitcol) VALUES (?)");
$sth->execute(0);
Nope! I get 1 stored (overflow basically). I read the documentation, and I came up with this, which is a bit cumbersome when I have lots of bind values so prepare to use the shorthand with an array passed to execute, but in any case:
use DBI qw(:sql_types);
my $sth = $dbh->prepare("INSERT INTO TABLE thetable (bitcol) VALUES (?)");
$sth->bind_param(1, 0, SQL_BIT);
$sth->execute();
Nope! Still the same. The only way the bind works is if I use pack to explicitly pass the bit.
my $sth = $dbh->prepare("INSERT INTO TABLE thetable (bitcol) VALUES (?)");
$sth->execute(pack('b1', 0));
And then, reading is the same, the only way I can read the SELECT results is with unpack.
I understand that BIT is actually for storing bits, and bits are obviously different than the 8-bit characters 0 and 1, but since the SQL for INSERT works fine with 0/1 and the SELECT outputs 0/1 and not bits in the command line, I'd expect there to be a simple way to use the BIT columns for storing 0/1 (true/false) essentially.
Am I missing something?
Edit: I do understand how BIT works with relation to potential savings. It would be a few GB if I did it on the tables that use many of these fields, so it might be significant, but it all depends exactly on my question. If you have to pack/unpack the savings start feeling smaller.