mysql 存储过程 全库搜索指定 内容

mysql：
存储过程：
全数据库，所有表所有字段（此处是字符型）查找包含某个值！

BEGIN
	DECLARE tn VARCHAR(1000);
	DECLARE cn VARCHAR(1000);
	DECLARE sel_sql VARCHAR(10000) DEFAULT '';
	DECLARE col_sql VARCHAR(10000) DEFAULT '';
	DECLARE whe_sql VARCHAR(10000) DEFAULT '';
	DECLARE temp_col VARCHAR(10000) DEFAULT '';
	DECLARE id int(11) DEFAULT 0;
	DECLARE circle_id int(1) DEFAULT 1;
	DECLARE have_col int(1) DEFAULT 0;
	
	-- 定义结束标识
	DECLARE done INT DEFAULT FALSE;
	DECLARE done1 INT DEFAULT FALSE;
	
	BEGIN
		-- 定义游标以及结束标识
		DECLARE purCrusor CURSOR for (select TABLE_NAME from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME NOT IN ('keep') ); -- 第一个游标遍历所有的表
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
		
		open purCrusor; -- 打开游标
		
			table_loop: LOOP -- 循环游标开始
			fetch purCrusor into tn;
				if done then
					LEAVE table_loop;
				ELSE 
					SET done = FALSE;
					SET done1 = FALSE;
					SET circle_id = 1;
					SET sel_sql = '';
					SET col_sql = '';
					SET whe_sql = '';
					SET temp_col = '';
					SET have_col = 0;
					BEGIN
						DECLARE purCrusor1 CURSOR for select COLUMN_NAME from information_schema.`COLUMNS` where TABLE_NAME=tn and TABLE_SCHEMA = 'database_name' AND DATA_TYPE in ('varchar', 'nvarchar', 'char', 'nchar'); 
						-- 第二个游标是第一个游标的嵌套游标，遍历某个表的所有字段
						DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = true;
						
						open purCrusor1;
							column_loop: LOOP
								fetch purCrusor1 into cn;
								if done1 then
									LEAVE column_loop;
								ELSE 
									SET have_col = 1;
									SET done1 = FALSE;
								-- 业务 
									SET col_sql = CONCAT(col_sql,'IFNULL(','t.',cn,',','\'n\')',',');
									IF circle_id = 1 THEN
										SET temp_col = CONCAT(temp_col,'IFNULL(','t.',cn,',','\'n\')');
										SET whe_sql = CONCAT('t.',cn,' like ','\'','%','your find string','%','\'');
									ELSE
										SET whe_sql = CONCAT(whe_sql,' or ','t.',cn,' like ','\'','%','your find string','%','\'');
									END IF;
									SET id = id + 1;
									SET circle_id = 0;
								END IF;
							END LOOP;
						CLOSE purCrusor1;
					END;
				
					IF have_col = 1 THEN
					    -- 此处 结果处理  这边采用 查出结果并且处理插入到新表中，再进行代码处理
						SET sel_sql = CONCAT('INSERT INTO new_table_name(content)',' select ','CONCAT_WS(\',\'',',',col_sql,temp_col,')',' as content',' from ',tn,' as t where ');
						SET @sql = CONCAT(sel_sql,whe_sql);
						SELECT CONCAT(sel_sql,whe_sql);
						PREPARE stmt_name FROM @sql;
						EXECUTE stmt_name;
						DEALLOCATE PREPARE stmt_name;
					END IF;
			END IF;
			END LOOP;
		CLOSE purCrusor;
	END;

END