统计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 

执行输出

 

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

任错错

如果对您有帮助我很开心

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值