Search code examples
mysqlmariadbsql-null

How to include NULL into ENUM on select?

My field country in ENUM type: (mysql - mariaDB)

`country` enum('France','Belgique','Suisse') DEFAULT NULL;

Is there a way to include NULL into a select ?

Select * from users where country in ('France','Suisse', NULL, '') ?

I would like to retrieve users from France, Suisse or those who did not specify the country (NULL)

Of course I can still use : 

Select * from users where (country in ('France','Suisse') OR country is NULL)

But this is quite verbose for me

Solution

  • Change the ENUM so that '' is the default and have it mean 'not specified'.

    That is:

    ENUM ('', 'France','Belgique','Suisse') NOT NULL DEFAULT '';

    Then this works:

    in ('France','Suisse', '')

    (I like to have the first item in the declaration be the default.)