如何定位数据库表中存在NULL值的列？Collate函数使用报错

首先明确：你用COLLATE函数是找错了用法——COLLATE是用来设置字符排序规则的，根本不是用来检测多列是否存在NULL的，所以报错是必然的。

下面分几种常用数据库给出可行方案：

MySQL #

通过聚合函数统计每列的NULL数量，结果大于0的列就是存在NULL值的列：

SELECT
  SUM(CASE WHEN column1 IS NULL THEN 1 ELSE 0 END) AS column1_null_count,
  SUM(CASE WHEN column2 IS NULL THEN 1 ELSE 0 END) AS column2_null_count,
  -- 按此格式依次列出表中所有列
  SUM(CASE WHEN columnN IS NULL THEN 1 ELSE 0 END) AS columnN_null_count
FROM Table_name;

SQL Server #

手动列写法 #

SELECT
  COUNT(CASE WHEN column1 IS NULL THEN 1 END) AS column1_nulls,
  COUNT(CASE WHEN column2 IS NULL THEN 1 END) AS column2_nulls,
  -- 依次列出所有列
  COUNT(CASE WHEN columnN IS NULL THEN 1 END) AS columnN_nulls
FROM Table_name;

自动生成查询（适合列多的表） #

用动态SQL自动拼接所有列的统计语句，不用手动写每一列：

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'COUNT(CASE WHEN ' + QUOTENAME(name) + ' IS NULL THEN 1 END) AS ' + QUOTENAME(name + '_nulls') + ', '
FROM sys.columns 
WHERE object_id = OBJECT_ID('Table_name');

SET @sql = LEFT(@sql, LEN(@sql)-1) + ' FROM Table_name;';
EXEC sp_executesql @sql;

PostgreSQL #

手动列写法 #

PostgreSQL支持FILTER子句，写法更简洁：

SELECT
  COUNT(*) FILTER (WHERE column1 IS NULL) AS column1_null_count,
  COUNT(*) FILTER (WHERE column2 IS NULL) AS column2_null_count,
  -- 依次列出所有列
  COUNT(*) FILTER (WHERE columnN IS NULL) AS columnN_null_count
FROM Table_name;

自动生成查询 #

DO $$
DECLARE
  col RECORD;
  sql TEXT := '';
BEGIN
  FOR col IN SELECT column_name FROM information_schema.columns WHERE table_name = 'Table_name' LOOP
    sql := sql || 'COUNT(*) FILTER (WHERE ' || quote_ident(col.column_name) || ' IS NULL) AS ' || quote_ident(col.column_name || '_nulls') || ', ';
  END LOOP;
  sql := rtrim(sql, ', ') || ' FROM Table_name;';
  EXECUTE sql;
END $$;

所有方案的核心逻辑都是统计每列的NULL记录数，只要数值大于0，就说明该列存在NULL值。

内容的提问来源于stack exchange，提问作者Abi