Mysql-找出实例下所有基表的真实行数

最新推荐文章于 2024-07-04 23:58:48 发布

原创最新推荐文章于 2024-07-04 23:58:48 发布 · 置顶 · 1.4k 阅读

CC 4.0 BY-SA版权

本文提供了一系列SQL查询脚本，用于验证数据库中触发器、存储过程、函数及事件的完整性，并检查表数据和自增长键的最大值，帮助确保数据库的一致性和准确性。

-- 验证触发器 
SELECT t.TRIGGER_SCHEMA, t.TRIGGER_NAME,  
MD5(  
CONCAT(  
 IFNULL(TRIGGER_CATALOG,''),IFNULL(TRIGGER_SCHEMA,''),IFNULL(TRIGGER_NAME,''),IFNULL(EVENT_MANIPULATION,''),IFNULL(EVENT_OBJECT_CATALOG,''),IFNULL(EVENT_OBJECT_SCHEMA,''),IFNULL(EVENT_OBJECT_TABLE,''),IFNULL( ACTION_ORDER,''),IFNULL(ACTION_CONDITION,''),IFNULL(ACTION_STATEMENT,''),IFNULL(ACTION_ORIENTATION,''),IFNULL(ACTION_TIMING,''),IFNULL(ACTION_REFERENCE_OLD_TABLE,''),IFNULL(ACTION_REFERENCE_NEW_TABLE,''),IFNULL(ACTION_REFERENCE_OLD_ROW,''),IFNULL(ACTION_REFERENCE_NEW_ROW,''),IFNULL(CREATED,''),IFNULL(SQL_MODE,''),IFNULL(DEFINER,''),IFNULL(CHARACTER_SET_CLIENT,''),IFNULL(COLLATION_CONNECTION,''),IFNULL(DATABASE_COLLATION,'')   
)) AS md5_  
FROM information_schema.TRIGGERS  t; 
  
  
-- 验证存储过程与函数
SELECT  CONCAT(p.db,'.',p.name) AS object ,TYPE ,MD5(body) md5_value FROM mysql.proc AS p   
WHERE db NOT LIKE '%-%'  
ORDER BY 2 ,1 ; <span style="font-family: Arial, Helvetica, sans-serif;">
-- 验证event  
SELECT CONCAT(A.DB,'.',A.NAME) AS EVENT ,MD5(BODY) MD5_ ,A.definer FROM mysql.event  A ; 


-- 验证表数据 ，拼出来的语句，稍微修改一下即可
SELECT CONCAT(' select  ''', t.TABLE_SCHEMA ,''' as db ,''',t.TABLE_NAME,''' as tbname, count(1) as row_count from `', t.TABLE_SCHEMA ,'`.`',t.TABLE_NAME,'` union all ')  AS Sql_Exec    
FROM information_schema.TABLES AS t    
LEFT JOIN ( SELECT * FROM information_schema.TABLES   
        ORDER BY TABLE_ROWS DESC LIMIT 13 ) d ON d.TABLE_SCHEMA = t.TABLE_SCHEMA AND d.TABLE_NAME = t.TABLE_NAME  
WHERE t.TABLE_TYPE = 'BASE TABLE'   
AND t.TABLE_SCHEMA NOT LIKE  '%-%'   
AND d.TABLE_SCHEMA IS NULL   
ORDER BY Sql_Exec ; 
-- 找出所有的自增加键的最大值
SELECT CONCAT('select ''',c.TABLE_SCHEMA,'.',c.TABLE_NAME,''' _table_name, max(a.', COLUMN_NAME,') _MaxValue from `',c.TABLE_SCHEMA ,'`.`', c.TABLE_NAME,'` a  union all ') AS auto_increment_MaxValue
-- ,c.* 
FROM   information_schema.COLUMNS c -- on t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME 
WHERE c.EXTRA = 'auto_increment'
AND c.TABLE_SCHEMA NOT LIKE '%-%'
ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME ;