Mysql存储过程全库搜索

记录一次对Mysql数据库 库内指定字符串搜索

实现方法：对所有表,拼接表的一行数据成一个字符串，对行数据进行查询，对每张表的查询结果用UNION ALL拼接。

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_name`()
BEGIN
#想要一次性处理表的数量
DECLARE tableCount int DEFAULT 1500;
#库里符合条件表的数量
DECLARE realTableCount int DEFAULT 0;
#游标状态，跳出循环用
DECLARE curosrFlag int DEFAULT 0;
#表名
DECLARE tableName VARCHAR(100);
#表的所有列
DECLARE columnNames VARCHAR(3000);
#单表sql
DECLARE selectSql VARCHAR(3000);
#单表sql模板
DECLARE selectSqlTemp VARCHAR(3000);
#全部表sql加 UNION ALL 拼接
DECLARE totalSql MEDIUMTEXT;
#表名游标
DECLARE tableNameCurosr 
CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME NOT IN (SELECT table_name FROM aa_url)
AND TABLE_NAME NOT IN ('aa_url');

SELECT COUNT(*) INTO realTableCount FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME NOT IN (SELECT table_name FROM aa_url)
AND TABLE_NAME NOT IN ('aa_url');

# 取 Min(realTableCount, tableCount)
IF realTableCount < tableCount THEN
	SET tableCount = realTableCount;
END IF;

SET selectSqlTemp = 
"
SELECT * FROM
(
	SELECT '{tableName1}' AS table_name, CONCAT_WS({columnNames1}) str
	FROM {tableName1}
) t1
WHERE t1.str LIKE '%http://118%'
";

SET totalSql = '';

IF tableCount > 0 THEN 

	# 打开游标
	OPEN tableNameCurosr;

		WHILE curosrFlag < tableCount DO
			SET curosrFlag= 1 + curosrFlag;
			
			FETCH tableNameCurosr INTO tableName;
			SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") INTO columnNames FROM information_schema.COLUMNS
			WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME = tableName;
			SET columnNames = CONCAT('"#",', columnNames);
			# 拼接要执行的单表的sql
			SET selectSql = selectSqlTemp;
			SET selectSql = REPLACE(selectSql,"{tableName1}",tableName);
			SET selectSql = REPLACE(selectSql,"{columnNames1}",columnNames);
			# 拼接全部表sql
			SET totalSql = CONCAT(totalSql, selectSql);
			IF curosrFlag < tableCount THEN
				SET totalSql = CONCAT(totalSql, " UNION ALL ");
			END IF;
			# 记录已经查询过的表
			INSERT INTO aa_url(table_name, row_value) VALUES(tableName, selectSql);
			#PREPARE exeSql FROM @selectSql;
			#EXECUTE exeSql;
		END WHILE;
		SET @totalSql = totalSql;
		# 执行全部表sql
		PREPARE tSql FROM @totalSql;
		EXECUTE tSql;
		DEALLOCATE PREPARE tSql;
	# 关闭游标
	CLOSE tableNameCurosr;
END IF;

END

如果你要这样👇把数据库转成sql，用readLine去匹配，我也没有办法

    public static void main(String[] args) throws IOException {
        File sql = new File("D:/mydb.sql");
        BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(sql)));
        String line = "";
        int i = 1;
        while((line = br.readLine())!=null){
            if (line.contains("http://118.")) {
                i++;
                System.out.println(line);
            }
        }
        br.close();
        System.out.println(i);
    }