The default value is not inserted in mysql

Question

I have this IsTrial field in MySQL of type bit(1) with NULL set to No and default value of 0

Now this condition:

 if(!empty($subscription['IsTrial']) && (bool)$subscription['IsTrial'] == TRUE ) { echo ' (Trial)'; } 

.. ALWAYS get true , because mysql does not actually set the default value to 0 , does it save the BLANK field ?? To make the above condition, I have to set the default value in MYSQL from 0 to null , but I do not want to do this.

I am really confused what is happening here, why the default value of 0 will not be set and the field remains empty?

To repeat, the problem is that mysql does NOT save the field with the default value of 0 , when the value is not specified manually, it saves the BLANK field, due to which the conditions fail.

Answer 1

The field is not empty. It has a binary value of 0.

You did not define a default value for 0 , that is, a character with ASCII code 48. You defined a default value for the binary value 0, which is the nul character in ASCII. When you try to print an ASCII nul as a string, there is no visible representation. Similarly, a value of 1 is a binary value of 1 or Control-A, which is also not a print character. A.

 mysql> create table t (isTrial bit(1) not null default 0); mysql> insert into t () values (); mysql> insert into t (isTrial) values (DEFAULT); mysql> insert into t (isTrial) values (0); mysql> insert into t (isTrial) values (1); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t; +---------+ | isTrial | +---------+ | | | | | | | | +---------+ mysql> pager cat -v PAGER set to 'cat -v' mysql> select * from t; +---------+ | isTrial | +---------+ | | | | | | | ^A | +---------+ 

But PHP does what most users probably want, namely to match these BIT values ​​with the string values ​​"0" and "1" respectively. Here is some test code (using PHP 5.3.15, MySQL 5.5.30):

 $stmt = $dbh->prepare("select isTrial from t"); $result = $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { var_dump($row); if(!empty($row['isTrial']) && (bool)$row['isTrial'] == TRUE ) { echo "isTrial is true\n"; } else { echo "isTrial is false\n"; } echo "\n"; } 

Output:

 $ php bit.php array(1) { ["isTrial"]=> string(1) "0" } isTrial is false array(1) { ["isTrial"]=> string(1) "0" } isTrial is false array(1) { ["isTrial"]=> string(1) "0" } isTrial is false array(1) { ["isTrial"]=> string(1) "1" } isTrial is true 

---

Your comment:

Using BIT (1) should work fine for PHP, but this is confusing when we look at the data directly in the query tool. If you want to make the data more understandable, you can use TINYINT. Another option would be CHAR (1) CHARACTER SET ASCII. Both require 1 byte for storage.

The only advantage of BIT (1) is that it rejects any value other than 0 or 1. But the storage requirement for this data type is uniformly 1 byte.