对于SQLite，您需要尝试以下内容：

DELETE FROM myTable WHERE myColumn IS NULL OR trim(myColumn) = '';

你必须使用另一种语言来实现自动化。

## pip install sqlite_utils

import argparse

import sqlite_utils

def tracer(sql, params) -> None:
    print("SQL: {} - params: {}".format(sql, params))

def connect(args) -> sqlite_utils.Database:
    db = sqlite_utils.Database(args.database, tracer=tracer if args.verbose >= 2 else None)
    db.execute("PRAGMA main.cache_size = 8000")
    return db

def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser()
    parser.add_argument("database")
    parser.add_argument("table")
    parser.add_argument("--verbose", "-v", action="count", default=0)
    args = parser.parse_args()
    return args

def remove_empty_cols() -> None:
    args = parse_args()
    db = connect(args)
    total_rows = db[args.table].count
    for col in [col.name for col in db[args.table].columns if col.type == 'TEXT']:
        details = db[args.table].analyze_column(col, total_rows=total_rows)
        if details.num_null == total_rows and details.num_distinct == 0:
            with db.conn:
                db.execute(f'alter table "{args.table}" drop column "{col}"')

if __name__ == "__main__":
    remove_empty_cols()

按如下方式运行：

python remove_empty_cols.py video.db reddit_posts

使用这样的子查询似乎不起作用：

SELECT 'alter table reddit_posts drop column ' || name || ';' ddl
FROM pragma_table_info('reddit_posts') t
WHERE "notnull"=0
AND (
    SELECT count(t.name) FROM reddit_posts
) = 0

但如果您不想使用python，您可以运行此命令，然后手动填充结果为0的列；

SELECT 'select count(' || name || ') from reddit_posts;' dml
FROM pragma_table_info('reddit_posts') t
WHERE "notnull"=0 AND "type"='TEXT';

SELECT 'alter table reddit_posts drop column ' || name || ';' ddl
FROM pragma_table_info('reddit_posts') t
WHERE name IN (
    ...
);