Is it somehow possible to do a select for empty strings and NULL values in MySQL without using or?
This:
select * from table where col IN (null, "");
doesn't work, it ignores the null (or possibly matches it with the str开发者_Python百科ing 'null').
SELECT *
FROM mytable
WHERE COALESCE(col, '') = ''
Note, however, than OR
query will be much more efficient if the column is indexed:
SELECT *
FROM mytable
WHERE col = '' OR col IS NULL
This will use ref_or_null
access path on the index.
If you need to select from a list of values along with NULLs
, just put all not-null values into the list and add a single OR IS NULL
condition:
SELECT *
FROM mytable
WHERE col IN ('val1', 'val2', 'val3') OR col IS NULL
This will use an index on col
as well.
In case anyone else is looking for this solution, as I was; if you're searching by more than one column, you need to group the where/or statement in parenthesis.
This will not return the results you expect:
SELECT *
FROM table
WHERE col1 IN ('val1', 'val2') OR col1 IS NULL AND col2 IN ('val3', 'val4')
The "OR col1 IS NULL" will completely override your "AND col2 IN" statement.
Putting all of the conditions for col1 inside parenthesis will work:
SELECT *
FROM table
WHERE (col1 IN ('val1', 'val2') OR col1 IS NULL) AND col2 IN ('val3', 'val4')
It took me a while to consider trying this, because (at least in my experience) you don't typically put WHERE statements in parenthesis.
That's work great for me
SELECT *
FROM table
WHERE IFNULL(col1, '-') IN('val1', 'val2', '-') AND col2 IN ('val3', 'val4')
精彩评论