MySQL数据库如何快速查找表中零日期记录

delimiter $$

drop procedure if exists count_zero_date$$

create procedure count_zero_date()
begin
    declare done int default false;
    declare tbl_name varchar(255);

    -- 游标：获取所有基表（排除视图）中具有 created_date 列的表
    declare cur cursor for
        select distinct c.table_name
        from information_schema.columns c
        inner join information_schema.tables t
            on c.table_schema = t.table_schema
            and c.table_name = t.table_name
        where c.table_schema = database()
          and c.column_name = 'created_date'
          and c.data_type in ('datetime', 'timestamp', 'date')
          and t.engine is not null;   -- 关键：排除视图

    declare continue handler for not found set done = true;

    -- 创建内存临时表存储统计结果
    drop temporary table if exists tmp_zero_count;
    create temporary table tmp_zero_count (
        table_name varchar(255) primary key,
        zero_count int not null
    ) engine = memory;

    open cur;
    read_loop: loop
        fetch cur into tbl_name;
        if done then
            leave read_loop;
        end if;

        -- 动态统计并插入（仅记录 zero_count > 0 的表）
        set @sql = concat(
            'insert into tmp_zero_count (table_name, zero_count) ',
            'select ''', tbl_name, ''', count(*) ',
            'from `', tbl_name, '` ',
            'where created_date = ''0000-00-00 00:00:00'' ',
            'having count(*) > 0'
        );
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;
    end loop;
    close cur;

    -- 返回最终结果
    select * from tmp_zero_count order by zero_count desc;

    drop temporary table tmp_zero_count;
end$$

delimiter ;

call count_zero_date();

pip3 install pymysql

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import pymysql
import sys
import csv
# ==================== 配置区（请按实际修改）====================
db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'your_user',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4'
}
zero_date = '0000-00-00 00:00:00'          # 待查找的零日期
output_csv = 'zero_date_records.csv'       # 输出文件，设为 none 则只打印不写文件
# =============================================================
def get_tables_with_column(cursor, db_name, column_name):
    """
    获取当前数据库中所有包含指定列的基表（排除视图）
    使用 engine is not null 判断基表，兼容所有 mysql 版本
    """
    sql = """
        select c.table_name
        from information_schema.columns c
        inner join information_schema.tables t
            on c.table_schema = t.table_schema
            and c.table_name = t.table_name
        where c.table_schema = %s
          and c.column_name = %s
          and c.data_type in ('datetime', 'timestamp', 'date')
          and t.engine is not null
    """
    cursor.execute(sql, (db_name, column_name))
    return [row[0] for row in cursor.fetchall()]
def main():
    conn = none
    try:
        conn = pymysql.connect(**db_config)
        cursor = conn.cursor()
        db_name = db_config['database']
        tables = get_tables_with_column(cursor, db_name, 'created_date')
        if not tables:
            print("未找到任何包含 'created_date' 列的基表。")
            return
        print(f"共发现 {len(tables)} 个表含有 created_date 列，开始扫描...")
        total_records = 0
        result_rows = []          # 存储所有匹配记录（含表名列）
        for tbl in tables:
            # 使用参数化查询，安全可靠
            query = f"select * from `{tbl}` where created_date = %s"
            cursor.execute(query, (zero_date,))
            rows = cursor.fetchall()
            if rows:
                # 首次遇到数据时，获取列名构造表头
                if not result_rows:
                    col_names = [desc[0] for desc in cursor.description]
                    result_rows.append(['table_name'] + col_names)
                for row in rows:
                    result_rows.append([tbl] + list(row))
                total_records += len(rows)
                print(f"表 {tbl}: 发现 {len(rows)} 条零日期记录")
        print(f"\n扫描完成，总计发现 {total_records} 条记录。")
        # 输出结果
        if output_csv and result_rows:
            with open(output_csv, 'w', newline='', encoding='utf-8-sig') as f:
                writer = csv.writer(f)
                writer.writerows(result_rows)
            print(f"详细结果已写入文件：{output_csv}")
        elif result_rows:
            # 不写文件则打印前20条示例
            print("\n示例数据（前20条）：")
            for i, row in enumerate(result_rows[1:21], 1):
                print(f"{i}. {row}")
        else:
            print("未发现任何零日期记录。")
        cursor.close()
    except pymysql.error as e:
        print(f"数据库错误: {e.args[0]} - {e.args[1]}")
        sys.exit(1)
    except exception as e:
        print(f"发生未知错误: {e}")
        sys.exit(1)
    finally:
        if conn:
            conn.close()
if __name__ == '__main__':
    main()

python3 find_zero_date.py

-- 将零日期更新为 null（需确认业务是否允许）
update table_name set created_date = null where created_date = '0000-00-00 00:00:00';

-- 或更新为有效默认值（如 1970-01-01）
update table_name set created_date = '1970-01-01 00:00:00' where created_date = '0000-00-00 00:00:00';