使用mysql中的enum字段选择行

Question

I am facing an abnormal issue with the enum field(UserStatus) of my table(userinfo). The table structure is as follows:

Field       Type        Default
------------------------------------------
Id          int(11)       (NULL)
FirstName   varchar(50)   (NULL)
LastName    varchar(50)   (NULL)
....        ...           ...
....        ...           ...   
UserStatus  enum('0','1') (NULL)

Here I have rows with the UserStatus values as NULL, 0 and 1. But When I am trying to select rows on the basis of the UserStatus field like:

SELECT 
    * 
FROM 
    userinfo 
WHERE 
    UserStatus<>'1'

Its not working and resulting in an empty resultset.

Thanks in advance.

Answer 1

Remember that NULL <> '1' is itself NULL and therefore your condition won't match rows whose UserStatus is NULL. Use this expression instead:

UserStatus IS NULL OR UserStatus <> '1'