TINYINT column & usage in Where clause - unexpected behaviour

Question

I want to have a control to hide some products contemporarily on the website. I thought that it is easier to make the change on the database than to change the sql and to load the page again.

I added a new column by:

ALTER TABLE product ADD hide BINARY NULL;

It created a TINYINT(1) column. If I make some products 1 to hide, then make some of them 0 again to show, I will have a table with a hide column like:

*hide*
NULL
0
1

When I query by:

SELECT * FROM product WHERE hide <> 1;

it shows only 0's, but not NULL's. When I changed the column into BIT, it turned into BIT(1), the same query results in the same.

Logically, if something is defined as 0 or 1, there is not another option. That's why you use binary. A third option is not logical.

To comply with the definitions, is the only way to write as below?

SELECT * FROM product WHERE hide <> 1 OR hide IS NULL;

(using MySQL 5.6.17)

Question-2: How do you hide a product temporarily? In a different way?

Answer 1

Since a product can either be hidden or not hidden, I would suggest changing the definition of the field and force a NOT NULL constraint on that.

As stated here:

First, make all current NULL values disappear:

UPDATE `product` SET `hide`=0 WHERE `hide` IS NULL

Then, update the table definition to disallow NULLs:

ALTER TABLE `product` ALTER COLUMN `hide` TINYINT NOT NULL

If, for some reason, you really want to keep the NULL values, you have to change your query to the second version you provided.

That said, I would not use the database approach on a "temporary" hide. For such cases, I think it would be better to hide the products on application level.

Answer 2

I think the mysql will search the row that have value first and then the value itself, thats why the null value will not shown.

But if the value is blank, the row will be shown.