MySQL遍历所有表所有字段查找字符数据

MySQL遍历所有表所有字段查找字符数据

工作中有一些数据查找，但是在那个库那个表那个字段中并不明确，特别是敏感字符查找，如果数据量并不大，我们可以采用遍历整个库、表中字符来查找相关数据来解决该问题。

我们可以写一个存储过程来解决该问题

步骤1.先找个库创建一个存储查询的表（表和该存储过程在一个库内）

drop table if exists  searchdata;
CREATE TABLE if not exists `searchdata` ( dbname varchar(200), tbname varchar(200),`sqlscript` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步骤2: 创建存储过程

存储过程如下:

接收参数为:

SearchDB: 查找库

SearchStr: 查找字符串

ResTable: 存储结果的表(库.表)

drop procedure if exists serarch_data;DELIMITER $$
CREATE  PROCEDURE `serarch_data`( in SearchDB varchar(200), in SearchStr varchar(200) )
begindeclare par_tbname varchar(200);declare done int default 0;  # 定义游标变量# 定义游标declare get_name cursor for select  table_name from information_schema.tables where table_schema=SearchDB;
# 捕获系统抛出的 not found 错误，如果捕获到，将 done 设置为 1  相当于try异常declare continue handler for not found set done=1;SET SESSION group_concat_max_len=99999;#打开游标open get_name;#loop 循环定义不使用 ;read_name:loop# 3、使用游标fetch get_name into par_tbname;-- select par_tbname;insert into searchdata(dbname,tbname,sqlscript)select SearchDB,table_name,concat(  'select * from ',SearchDB ,'.',table_name, ' where 1=1 and (',  wh,') limit 1' ) as sqlscriptfrom(select table_name, replace(group_concat(col ),',',' or ') as whfrom (-- select table_name , concat(  column_name,' like ''%s3.%''') as col select table_name , concat(  '`',column_name,'`',' like ''%',SearchStr,'%''') as col from information_schema.columnswhere table_schema=SearchDBand table_name=par_tbnameand data_type in( 'text','char','longtext','mediumtext','varchar')) as agroup by table_name ) res;# 如果发生异常if done = 1 thenleave read_name;end if ;end loop;close get_name; #关闭游标  
end$$
DELIMITER ;

如何使用:

输入库名 和查找字符串即可
call serarch_data('goodhope_dev','你好');
truncate table searchdata;
select *  from searchdata;

3. 批量产生 调用存储过程 sql

select concat(  'call serarch_data(''',  SCHEMA_NAME ,''',''','cheerex.info'');')from information_schema.SCHEMATA
where SCHEMA_NAME not in ('test','mysql','information_schema','sys','tmp')

4. 批量执行存储过程

5. 批量产生查询sql

select concat('select 1 ,''',dbname,'.',tbname,''' from (',sqlscript,') as ',tbname,' union all') as sqlscriptfrom searchdatawhere dbname not in ('quote','exchange_otc','gateway_exchange','performance_schema','test');

6. 批量执行第5步产生的sql即可

MYSQL系列书籍

高可用mysql: https://url41.ctfile.com/f/49289241-959127723-de4738?p=2651 (访问密码: 2651)

MySQL王者晋级之路.pdf: https://url41.ctfile.com/f/49289241-959127432-204284?p=2651 (访问密码: 2651)

MySQL技术内幕InnoDB存储引擎第2版.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p=2651 (访问密码: 2651)

MySQL技术内幕 第4版.pdf: https://url41.ctfile.com/f/49289241-959125506-a5bcec?p=2651 (访问密码: 2651)

MySQL管理之道，性能调优，高可用与监控（第二版）.pdf: https://url41.ctfile.com/f/49289241-959124249-d59f54?p=2651 (访问密码: 2651)

深入浅出MySQL数据库开发、优化与管理维护第2版.pdf: https://url41.ctfile.com/f/49289241-961464090-68bf10?p=2651 (访问密码: 2651)

高性能MySQL.第3版.Baron Schwartz.pdf: https://url41.ctfile.com/f/49289241-961462308-52cc5d?p=2651 (访问密码: 2651)

MYSQL内核：INNODB存储引擎 卷1.pdf: https://url41.ctfile.com/f/49289241-961461357-ee63e3?p=2651 (访问密码: 2651)

MySQL技术内幕InnoDB存储引擎第2版.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p=2651 (访问密码: 2651)

MySQLDBA修炼之道.pdf: https://url41.ctfile.com/f/49289241-961459500-9b201d?p=2651 (访问密码: 2651)

MySQL5.7从入门到精通.pdf: https://url41.ctfile.com/f/49289241-961459329-48cbcf?p=2651 (访问密码: 2651)

高可用mysql.pdf: https://url41.ctfile.com/f/49289241-959127723-de4738?p=2651 (访问密码: 2651)

HIVE电子书

Practical Hive.pdf: https://url41.ctfile.com/f/49289241-959129883-d35ee9?p=2651 (访问密码: 2651)

Hive-Succinctly.pdf: https://url41.ctfile.com/f/49289241-959129709-30f30b?p=2651 (访问密码: 2651)

Apache Hive Essentials.pdf: https://url41.ctfile.com/f/49289241-959129691-b1a4aa?p=2651 (访问密码: 2651)

Apache Hive Cookbook.pdf: https://url41.ctfile.com/f/49289241-959129619-3a8ea6?p=2651 (访问密码: 2651)

hadoop电子书

Practical Hadoop Migration.pdf: https://url41.ctfile.com/f/49289241-959131470-dd3e24?p=2651 (访问密码: 2651)

Hadoop实战-陆嘉恒(高清完整版).pdf: https://url41.ctfile.com/f/49289241-959131365-433ec9?p=2651 (访问密码: 2651)

Hadoop & Spark大数据开发实战.pdf: https://url41.ctfile.com/f/49289241-959131032-ba40ea?p=2651 (访问密码: 2651)

Expert Hadoop Administration.pdf: https://url41.ctfile.com/f/49289241-959130468-ba70cd?p=2651 (访问密码: 2651)

Big Data Forensics - Learning Hadoop Investigations.pdf: https://url41.ctfile.com/f/49289241-959130435-9ab981?p=2651 (访问密码: 2651)

python电子书

python学习手册.pdf: https://url41.ctfile.com/f/49289241-959129403-5b45b1?p=2651 (访问密码: 2651)

Python基础教程-第3版.pdf: https://url41.ctfile.com/f/49289241-959128707-de6ef2?p=2651 (访问密码: 2651)

Python编程：从入门到实践.pdf: https://url41.ctfile.com/f/49289241-959128548-ce965d?p=2651 (访问密码: 2651)

Python Projects for Beginners.pdf: https://url41.ctfile.com/f/49289241-959128461-b53321?p=2651 (访问密码: 2651)

kafka电子书

Learning Apache Kafka, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959134953-a14305?p=2651 (访问密码: 2651)

Kafka权威指南.pdf: https://url41.ctfile.com/f/49289241-959134932-295734?p=2651 (访问密码: 2651)

Kafka in Action.pdf: https://url41.ctfile.com/f/49289241-959134116-12111a?p=2651 (访问密码: 2651)

Apache Kafka实战.pdf: https://url41.ctfile.com/f/49289241-959133999-76ef77?p=2651 (访问密码: 2651)

Apache Kafka Cookbook.pdf: https://url41.ctfile.com/f/49289241-959132547-055c36?p=2651 (访问密码: 2651)

spark电子书

Spark最佳实践.pdf: https://url41.ctfile.com/f/49289241-959415393-5829fe?p=2651 (访问密码: 2651)

数据算法--Hadoop-Spark大数据处理技巧.pdf: https://url41.ctfile.com/f/49289241-959415927-5bdddc?p=2651 (访问密码: 2651)

Spark大数据分析实战.pdf: https://url41.ctfile.com/f/49289241-959416377-924161?p=2651 (访问密码: 2651)

Spark 2.0 for Beginners.pdf: https://url41.ctfile.com/f/49289241-959416710-7ea156?p=2651 (访问密码: 2651)

Pro Spark Streaming.pdf: https://url41.ctfile.com/f/49289241-959416866-6116d7?p=2651 (访问密码: 2651)

Spark in Action.pdf: https://url41.ctfile.com/f/49289241-959416986-e759e9?p=2651 (访问密码: 2651)

Learn PySpark.pdf: https://url41.ctfile.com/f/49289241-959417049-ac04a0?p=2651 (访问密码: 2651)

Fast Data Processing with Spark.pdf: https://url41.ctfile.com/f/49289241-959417157-8ec3b0?p=2651 (访问密码: 2651)

Fast Data Processing with Spark, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959417211-856d08?p=2651 (访问密码: 2651)

OReilly.Learning.Spark.2015.1.pdf: https://url41.ctfile.com/f/49289241-959417292-90c1bc?p=2651 (访问密码: 2651)

High Performance Spark.pdf: https://url41.ctfile.com/f/49289241-959417439-7e7893?p=2651 (访问密码: 2651)

Machine Learning with PySpark.pdf: https://url41.ctfile.com/f/49289241-959417580-5941b3?p=2651 (访问密码: 2651)

Spark for Python Developers.pdf: https://url41.ctfile.com/f/49289241-959417721-d59fbe?p=2651 (访问密码: 2651)

Spark Cookbook.pdf: https://url41.ctfile.com/f/49289241-959417811-19c75d?p=2651 (访问密码: 2651)

Big Data Analytics with Spark.pdf: https://url41.ctfile.com/f/49289241-959417907-41dbce?p=2651 (访问密码: 2651)

PySpark SQL Recipes.pdf: https://url41.ctfile.com/f/49289241-959417970-c23242?p=2651 (访问密码: 2651)

Advanced Analytics with Spark Patterns for Learning from Data at Scale .pdf: https://url41.ctfile.com/f/49289241-959417997-a5e3f5?p=2651 (访问密码: 2651)

OReilly.Advanced.Analytics.with.Spark.Patterns.for.Learning.from.Data.at.Scale.pdf: https://url41.ctfile.com/f/49289241-959418024-2ff34c?p=2651 (访问密码: 2651)

Big Data Analytics Beyond Hadoop_ Real-Time Applications with Storm, Spark, and More Hadoop Alternatives.pdf: https://url41.ctfile.com/f/49289241-959418042-581fb9?p=2651 (访问密码: 2651)