如何检查所有字段的记录不是NULL或空？

Question

I have a record from a MySQL Database that needs to be validated to see if all the fields have a value or post some alert.

Is the best way to put all the field values into an array and then check the SUM where NOT NULL or Empty to see if it matches the qty of selected fields?

EXAMPLE: $result = | Value | NULL | NULL | Value | EMPTY |

In this example the Record has values from 5 Fields and 2 of those Fields are not Null or Empty

I need Validate to see when all 5 Fields have some value

Answer 1

You could use GREATEST (or LEAST):

SELECT * 
FROM   table1 
WHERE  GREATEST(col1, col2, col3, col4) IS NOT NULL

See SQL fiddle

The idea is that GREATEST needs to check all of the arguments, and because expressions with NULL will return NULL (see here for exceptions to that rule), GREATEST will return NULL if any of its arguments is NULL.

The above will filter the records that meat the condition, i.e. that do no have NULL values. With the same idea you could something that does not filter, but adds the information on whether it has a NULL or not:

SELECT *, 
       CASE WHEN GREATEST(col1, col2, col3, col4) IS NULL 
           THEN 0 
           ELSE 1
       END AS is_OK
FROM   table1 

Now you will have an extra column in the result with either 0 or 1. It indicates whether your columns are all not NULL.