统计Mysql库中每个表的总行数，解决table

这篇具有很好参考价值的文章主要介绍了统计Mysql库中每个表的总行数，解决table_rows不准确问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1、拼接SQL

select
	substring( GROUP_CONCAT(a.sf SEPARATOR ''),1,length(GROUP_CONCAT(a.sf SEPARATOR ''))-10) as sql_str
from
	( select concat(
    'select "', 
    TABLE_name , 
    '", count(*) as row_num from ', 
    TABLE_SCHEMA, 
    '.',
    TABLE_name,
    ' union all '
) as sf
from
	information_schema.tables
where
	TABLE_SCHEMA = 'szwx') a

注意：GROUP_CONCAT()默认容量是1024，拼接sql会出现截断，设置的大一些。

执行：SET SESSION group_concat_max_len = 102400;

2.输出SQL

select "city_government_service", count(*) as row_num from szwx.city_government_service union all select "city_production_pack", count(*) as row_num from szwx.city_production_pack union all select "city_service_pack", count(*) as row_num from szwx.city_service_pack union all select "city_traffic_pack", count(*) as row_num from szwx.city_traffic_pack union all select "czj_dwd_wx_expenditure_report", count(*) as row_num from szwx.czj_dwd_wx_expenditure_report union all select "czj_dwd_wx_financeincome", count(*) as row_num from szwx.czj_dwd_wx_financeincome union all select "czj_dwd_wx_income_report", count(*) as row_num from szwx.czj_dwd_wx_income_report union all select "czj_dwd_wx_tax_revenue", count(*) as row_num from szwx.czj_dwd_wx_tax_revenue union all select "dwd_wx_geography_tab", count(*) as row_num from szwx.dwd_wx_geography_tab union all select "dwd_wx_tourist_route", count(*) as row_num from szwx.dwd_wx_tourist_route union all select "dwd_wx_tourist_spots", count(*) as row_num from szwx.dwd_wx_tourist_spots union all select "fgj_great_item_table", count(*) as row_num from szwx.fgj_great_item_table union all select "jyj_dwd_teacher_education", count(*) as row_num from szwx.jyj_dwd_teacher_education union all select "jyj_dwd_wx_expected_retire_data", count(*) as row_num from szwx.jyj_dwd_wx_expected_retire_data union all select "jyj_dwd_wx_school_counts", count(*) as row_num from szwx.jyj_dwd_wx_school_counts union all select "jyj_dwd_wx_student_data", count(*) as row_num from szwx.jyj_dwd_wx_student_data union all select "jyj_dwd_wx_teacher_data", count(*) as row_num from szwx.jyj_dwd_wx_teacher_data union all select "lyj_dwd_wx_forestry", count(*) as row_num from szwx.lyj_dwd_wx_forestry union all select "lyj_june2023_scenicareareception", count(*) as row_num from szwx.lyj_june2023_scenicareareception union all select "mzj_cx_ep_support_statistics", count(*) as row_num from szwx.mzj_cx_ep_support_statistics union all select "mzj_dis_disabled_statistics", count(*) as row_num from szwx.mzj_dis_disabled_statistics union all select "mzj_dwd_wx_county_civil_affairs_objects_and_standard", count(*) as row_num from szwx.mzj_dwd_wx_county_civil_affairs_objects_and_standard union all select "mzj_dwd_wx_county_orphan_statistic", count(*) as row_num from szwx.mzj_dwd_wx_county_orphan_statistic union all select "mzj_dwd_wx_county_social_welfare", count(*) as row_num from szwx.mzj_dwd_wx_county_social_welfare union all select "mzj_dwd_wx_minimum_living_security_objects", count(*) as row_num from szwx.mzj_dwd_wx_minimum_living_security_objects union all select "nyncj_dwd_wx_cold_storage_subsidy_info", count(*) as row_num from szwx.nyncj_dwd_wx_cold_storage_subsidy_info union all select "py_cloud_img", count(*) as row_num from szwx.py_cloud_img union all select "qyrkxxgl_contraception_statistics", count(*) as row_num from szwx.qyrkxxgl_contraception_statistics union all select "qyrkxxgl_fecund_women_conditions", count(*) as row_num from szwx.qyrkxxgl_fecund_women_conditions union all select "qyrkxxgl_population_basic_info", count(*) as row_num from szwx.qyrkxxgl_population_basic_info union all select "qyrkxxgl_population_natural_change", count(*) as row_num from szwx.qyrkxxgl_population_natural_change union all select "scjdglj_basic_library_establishment_query", count(*) as row_num from szwx.scjdglj_basic_library_establishment_query union all select "scjdglj_task_progress_query", count(*) as row_num from szwx.scjdglj_task_progress_query union all select "shp_dwd_wx_jg", count(*) as row_num from szwx.shp_dwd_wx_jg union all select "tjj_domestic_trade", count(*) as row_num from szwx.tjj_domestic_trade union all select "tjj_dwd_wx_2023_grain_production", count(*) as row_num from szwx.tjj_dwd_wx_2023_grain_production union all select "tjj_dwd_wx_agri_forest_total_output", count(*) as row_num from szwx.tjj_dwd_wx_agri_forest_total_output union all select "tjj_dwd_wx_animal_veggie_prod", count(*) as row_num from szwx.tjj_dwd_wx_animal_veggie_prod union all select "tjj_dwd_wx_industrial_value_added_growth", count(*) as row_num from szwx.tjj_dwd_wx_industrial_value_added_growth union all select "tjj_dwd_wx_major_target_performance", count(*) as row_num from szwx.tjj_dwd_wx_major_target_performance union all select "tjj_dwd_wx_monthly_economic_indicators_monitor", count(*) as row_num from szwx.tjj_dwd_wx_monthly_economic_indicators_monitor union all select "tjj_dwd_wx_national_economic_accounting", count(*) as row_num from szwx.tjj_dwd_wx_national_economic_accounting union all select "tjj_fixed_asset_investment", count(*) as row_num from szwx.tjj_fixed_asset_investment union all select "tjj_industrial_economic_benefit", count(*) as row_num from szwx.tjj_industrial_economic_benefit union all select "tjj_industrial_output", count(*) as row_num from szwx.tjj_industrial_output union all select "tjj_industrial_sales", count(*) as row_num from szwx.tjj_industrial_sales union all select "tjj_real_estate_dev_status", count(*) as row_num from szwx.tjj_real_estate_dev_status union all select "wtj_dwd_wx_health_service_statistics", count(*) as row_num from szwx.wtj_dwd_wx_health_service_statistics union all select "wtj_public_health_data_65yearold_management_statistics", count(*) as row_num from szwx.wtj_public_health_data_65yearold_management_statistics union all select "wtj_public_health_data_diabetes_health_service_statistics", count(*) as row_num from szwx.wtj_public_health_data_diabetes_health_service_statistics union all select "wtj_public_health_data_health_education_service_statistics", count(*) as row_num from szwx.wtj_public_health_data_health_education_service_statistics union all select "wtj_public_health_data_hyperglycemia_health_service_statistics", count(*) as row_num from szwx.wtj_public_health_data_hyperglycemia_health_service_statistics union all select "wtj_public_health_data_hypertension_service_statistics", count(*) as row_num from szwx.wtj_public_health_data_hypertension_service_statistics union all select "wtj_public_health_data_mental_disorder_health_service_statistics", count(*) as row_num from szwx.wtj_public_health_data_mental_disorder_health_service_statistics union all select "wtj_public_health_data_standard_profile_statistics", count(*) as row_num from szwx.wtj_public_health_data_standard_profile_statistics union all select "wtj_public_health_data_tcm_health_service_statistics", count(*) as row_num from szwx.wtj_public_health_data_tcm_health_service_statistics union all select "xczx_dwd_wx_project_stats", count(*) as row_num from szwx.xczx_dwd_wx_project_stats union all select "xczx_hamlet_type_statistics", count(*) as row_num from szwx.xczx_hamlet_type_statistics union all select "xczx_poverty_mem_statistics", count(*) as row_num from szwx.xczx_poverty_mem_statistics union all select "xczx_project_cc_statistics", count(*) as row_num from szwx.xczx_project_cc_statistics union all select "xczx_village_db_statistics", count(*) as row_num from szwx.xczx_village_db_statistics union all select "xczx_village_work_statistics", count(*) as row_num from szwx.xczx_village_work_statistics union all select "ybj_dwd_wx_medical_insurance_data", count(*) as row_num from szwx.ybj_dwd_wx_medical_insurance_data union all select "yjj_dwd_wx_shelter", count(*) as row_num from szwx.yjj_dwd_wx_shelter

执行输出

统计Mysql库中每个表的总行数，解决table_rows不准确问题,mysql,数据库文章来源地址https://www.toymoban.com/news/detail-666592.html