0

I'm using mysql for node.js.

This query checks if a column has all NULL values, by checking if at least one row is not NULL.

SELECT 1 FROM `table` WHERE `column` IS NOT NULL LIMIT 1

If the column has all NULL values, this query ends in the SQL processlist after a few seconds (~120), but I never get a callback from the js code:

import {createPool as mysqlCreatePool, MysqlError} from 'mysql';

const db = mysqlCreatePool({...})

const query = db.query(sql, undefined, function (err: MysqlError | null, res?: any) {
  console.log(err, res);
});

query.on('end', () => console.log("end"));

(Nothing is ever printed in the console)

Amit
  • 5,924
  • 7
  • 46
  • 94

1 Answers1

1

Might not be the same approach but will get you the answer I think you're after and always return a result.

Query for a count rather than a fixed value and use IS NULL rather than IS NOT NULL:

SELECT count(*) AS null_count FROM `table` WHERE `column` IS NULL;

If your response is 0 every record has a value for that column. If your response is greater than 0 you have that many records with a NULL value in that column.

dusthaines
  • 1,320
  • 1
  • 11
  • 17
  • I understand how this will always get a result, but, this is much more expensive time-wise, as the other method is negation based (NOT NULL LIMIT 1) and works really fast. Any idea specifically why my query doesn't return anything on nodejs musql? – Amit Mar 24 '20 at 20:29
  • You could try a variation of the `CASE WHEN` approach. I'm not familiar with the performance impact compared to my prior example and your original. Consider this approach: `SELECT (column IS NOT NULL) AS null_check FROM table LIMIT 1`. Testing each of the 3 variations on a remote DB table with ~120k records, all have a similar response time of appx. 16ms to 18ms. More details here on the alternate approach : (https://stackoverflow.com/a/9387864/1720873) – dusthaines Mar 24 '20 at 20:50