PHP 7.1.9 retrieves MySQL bit(1) as string(20)

Viewed 48

System: Debian Jessie (on cubietruck, armv7)

PHP and MySQL have been compiled from the latest sources (PHP 7.1.9, MySQL 5.7.19). PHP info: https://ibb.co/ggbsZQ

When I'm trying to retrieve bit(1) column value using mysqli or pdo it returns string(20), basically it is a huge number not a one bit.

Table structure and data:

create table TestValue (
     Id             int             not null auto_increment
    ,StringValue    nvarchar(32)
    ,BitValue       bit(1)
    ,TinyintValue   tinyint(1)
    ,IntValue       int
    ,DoubleValue    double
    ,DateTimeValue  datetime

    ,primary key (Id)
) engine=InnoDB default charset=utf8 collate=utf8_general_ci;

insert into TestValue (StringValue, BitValue, TinyintValue, IntValue, DoubleValue, DateTimeValue)
values   (N'Test string 2', 1, 1, 42, 3.141517, '2017-09-01 12:45:11')
        ,(N'String 2', 0, 0, 128, 4.5, '2017-09-01')
        ,(null, null, null, null, null, null);

The test code:

echo "pdo:\n";
$pdo = new PDO("mysql:host=localhost;dbname=$db", $user, $password);
$stmt = $pdo->prepare('select BitValue, TinyintValue from TestValue');
$stmt->execute();
$rowset = $stmt->fetchAll(PDO::FETCH_OBJ);
var_dump($rowset);

echo "\nmysqli:\n";
$mysqli = new mysqli('localhost', $user, $password, $db);
$result = $mysqli->query('select BitValue, TinyintValue from TestValue');

while ($row = $result->fetch_assoc())
{
    var_dump($row);
}

The results:

pdo:
array(3) {
  [0]=>
  object(stdClass)#4 (2) {
    ["BitValue"]=>
    string(20) "12999622736513859585"
    ["TinyintValue"]=>
    string(1) "1"
  }
  [1]=>
  object(stdClass)#5 (2) {
    ["BitValue"]=>
    string(20) "12999622757988696064"
    ["TinyintValue"]=>
    string(1) "0"
  }
  [2]=>
  object(stdClass)#6 (2) {
    ["BitValue"]=>
    NULL
    ["TinyintValue"]=>
    NULL
  }
}

mysqli:
array(2) {
  ["BitValue"]=>
  string(20) "12999763474002214913"
  ["TinyintValue"]=>
  string(1) "1"
}
array(2) {
  ["BitValue"]=>
  string(20) "12999763495477051392"
  ["TinyintValue"]=>
  string(1) "0"
}
array(2) {
  ["BitValue"]=>
  NULL
  ["TinyintValue"]=>
  NULL
}

I could use tinyint(1) instead of bit(1) however many projects use bit to store bool values and probably those projects won't work correctly on my environment.

Main question is how to retrieve bit value correctly?

0 Answers
Related