2016/07/08 09:15 ... Johan De Meersman:
You will have to repeat all the column names - no wildcards in where clause
fieldnames - but the clause you're looking for is WHERE field IS NULL. Or IS
NOT NULL if you want those:-)
Well, one could try this:
set @sel=(select 'SELECT ' || GROUP_CONCAT(column_name) || ' FROM
membership.address WHERE ' || GROUP_concat(column_name || ' IS NULL'
SEPARATOR ' OR ') as cmd
from information_schema.columns where (table_schema, table_name,
is_nullable) = ('membership', 'address', 'yes'));
but changing "membership" and "address" to the right own database and
table name. If one goes this way, I advise saving the result in a file
to be fetched by 'source', not a variable, because I suspect that one
would want to adjust it to one s own situation.
(After the aforesaid, I did
prepare w from @sel;
execute w;
and got 1000 rows out of 1216; only 216 rows have no NULL in any field.)
Maybe Lejeczek really wants to make some of his table s fields NOT NULL?
Remember, NULL is a special value that is not the same as zero or the empty
string; nor to itself: NULL != NULL, by definition. Personally, I mostly
discourage the use of it (use DEFAULT VALUE in your table definition wherever
possible) except in circumstances where it really is necessary to know the
difference between 'nothing here' and 'we have no information about this at
all'.
In SQL NULL has too many uses. It is as if they got this bright idea of
NULL as you describe and all over used it (x/0 ?).
We have fields "HomePhone", "CellPhone", and "WorkPhone" and allow them
to be NULL, not because maybe we don't know, although that sometimes is
true, but because in the expression
CONCAT_WS(', ', 'c' || CellPhone, HomePhone, 'w' || WorkPhone)
any NULL argument (after the first) so conveniently completely
disappears from the result.
When one makes a field NOT NULL in MySQL one also uses slightly less
storage.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
