mysql condition to select empty fulltext cell?

Question

i need write a select query to find the number of rows which have an empty fulltext field but for some reason both:

select count(id) from table where field is null;

and

select count(id) from table where field = "";

don't seem to work!

what else is there?!

Answer 1

select
   count(id)
from
   table
where
   isnull(field);

However, your query with "field is null" should work to (and it does, for me at least).

Answer 2

And if that doesn't work... you might try

where len( trim( field )) = 0

Answer 3

When you say they don't work, do you mean the empty and non-empty fields are returned or nothing is returned?

If you get empty and non-empty, try using PHP to test with the return is_null or is_string. Perhaps there is some whitespace in there or something like that?

Answer 4

Try doing:

SELECT id, LENGTH(field) AS len, field
FROM table

and see if there's a non-zero string length on fields that do look "empty" and would otherwise match on your initial queries. If the length is non-zero, there's whitespace of some sort in there.