包装设计接单网站,抖音代运营招商,主任说到学校新网站的建设工作,科技数码app排名MySQL遍历所有表所有字段查找字符数据
工作中有一些数据查找#xff0c;但是在那个库那个表那个字段中并不明确#xff0c;特别是敏感字符查找#xff0c;如果数据量并不大#xff0c;我们可以采用遍历整个库、表中字符来查找相关数据来解决该问题。
我们可以写一个存储过…MySQL遍历所有表所有字段查找字符数据
工作中有一些数据查找但是在那个库那个表那个字段中并不明确特别是敏感字符查找如果数据量并不大我们可以采用遍历整个库、表中字符来查找相关数据来解决该问题。
我们可以写一个存储过程来解决该问题
步骤1.先找个库创建一个存储查询的表表和该存储过程在一个库内
drop table if exists searchdata;
CREATE TABLE if not exists searchdata ( dbname varchar(200), tbname varchar(200),sqlscript text
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;步骤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_schemaSearchDB;
# 捕获系统抛出的 not found 错误如果捕获到将 done 设置为 1 相当于try异常declare continue handler for not found set done1;SET SESSION group_concat_max_len99999;#打开游标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 11 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_schemaSearchDBand table_namepar_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?p2651 (访问密码: 2651)
MySQL王者晋级之路.pdf: https://url41.ctfile.com/f/49289241-959127432-204284?p2651 (访问密码: 2651)
MySQL技术内幕InnoDB存储引擎第2版.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p2651 (访问密码: 2651)
MySQL技术内幕 第4版.pdf: https://url41.ctfile.com/f/49289241-959125506-a5bcec?p2651 (访问密码: 2651)
MySQL管理之道性能调优高可用与监控第二版.pdf: https://url41.ctfile.com/f/49289241-959124249-d59f54?p2651 (访问密码: 2651)
深入浅出MySQL数据库开发、优化与管理维护第2版.pdf: https://url41.ctfile.com/f/49289241-961464090-68bf10?p2651 (访问密码: 2651)
高性能MySQL.第3版.Baron Schwartz.pdf: https://url41.ctfile.com/f/49289241-961462308-52cc5d?p2651 (访问密码: 2651)
MYSQL内核INNODB存储引擎 卷1.pdf: https://url41.ctfile.com/f/49289241-961461357-ee63e3?p2651 (访问密码: 2651)
MySQL技术内幕InnoDB存储引擎第2版.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p2651 (访问密码: 2651)
MySQLDBA修炼之道.pdf: https://url41.ctfile.com/f/49289241-961459500-9b201d?p2651 (访问密码: 2651)
MySQL5.7从入门到精通.pdf: https://url41.ctfile.com/f/49289241-961459329-48cbcf?p2651 (访问密码: 2651)
高可用mysql.pdf: https://url41.ctfile.com/f/49289241-959127723-de4738?p2651 (访问密码: 2651)
HIVE电子书
Practical Hive.pdf: https://url41.ctfile.com/f/49289241-959129883-d35ee9?p2651 (访问密码: 2651)
Hive-Succinctly.pdf: https://url41.ctfile.com/f/49289241-959129709-30f30b?p2651 (访问密码: 2651)
Apache Hive Essentials.pdf: https://url41.ctfile.com/f/49289241-959129691-b1a4aa?p2651 (访问密码: 2651)
Apache Hive Cookbook.pdf: https://url41.ctfile.com/f/49289241-959129619-3a8ea6?p2651 (访问密码: 2651)
hadoop电子书
Practical Hadoop Migration.pdf: https://url41.ctfile.com/f/49289241-959131470-dd3e24?p2651 (访问密码: 2651)
Hadoop实战-陆嘉恒(高清完整版).pdf: https://url41.ctfile.com/f/49289241-959131365-433ec9?p2651 (访问密码: 2651)
Hadoop Spark大数据开发实战.pdf: https://url41.ctfile.com/f/49289241-959131032-ba40ea?p2651 (访问密码: 2651)
Expert Hadoop Administration.pdf: https://url41.ctfile.com/f/49289241-959130468-ba70cd?p2651 (访问密码: 2651)
Big Data Forensics - Learning Hadoop Investigations.pdf: https://url41.ctfile.com/f/49289241-959130435-9ab981?p2651 (访问密码: 2651)
python电子书
python学习手册.pdf: https://url41.ctfile.com/f/49289241-959129403-5b45b1?p2651 (访问密码: 2651)
Python基础教程-第3版.pdf: https://url41.ctfile.com/f/49289241-959128707-de6ef2?p2651 (访问密码: 2651)
Python编程从入门到实践.pdf: https://url41.ctfile.com/f/49289241-959128548-ce965d?p2651 (访问密码: 2651)
Python Projects for Beginners.pdf: https://url41.ctfile.com/f/49289241-959128461-b53321?p2651 (访问密码: 2651)
kafka电子书
Learning Apache Kafka, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959134953-a14305?p2651 (访问密码: 2651)
Kafka权威指南.pdf: https://url41.ctfile.com/f/49289241-959134932-295734?p2651 (访问密码: 2651)
Kafka in Action.pdf: https://url41.ctfile.com/f/49289241-959134116-12111a?p2651 (访问密码: 2651)
Apache Kafka实战.pdf: https://url41.ctfile.com/f/49289241-959133999-76ef77?p2651 (访问密码: 2651)
Apache Kafka Cookbook.pdf: https://url41.ctfile.com/f/49289241-959132547-055c36?p2651 (访问密码: 2651)
spark电子书
Spark最佳实践.pdf: https://url41.ctfile.com/f/49289241-959415393-5829fe?p2651 (访问密码: 2651)
数据算法--Hadoop-Spark大数据处理技巧.pdf: https://url41.ctfile.com/f/49289241-959415927-5bdddc?p2651 (访问密码: 2651)
Spark大数据分析实战.pdf: https://url41.ctfile.com/f/49289241-959416377-924161?p2651 (访问密码: 2651)
Spark 2.0 for Beginners.pdf: https://url41.ctfile.com/f/49289241-959416710-7ea156?p2651 (访问密码: 2651)
Pro Spark Streaming.pdf: https://url41.ctfile.com/f/49289241-959416866-6116d7?p2651 (访问密码: 2651)
Spark in Action.pdf: https://url41.ctfile.com/f/49289241-959416986-e759e9?p2651 (访问密码: 2651)
Learn PySpark.pdf: https://url41.ctfile.com/f/49289241-959417049-ac04a0?p2651 (访问密码: 2651)
Fast Data Processing with Spark.pdf: https://url41.ctfile.com/f/49289241-959417157-8ec3b0?p2651 (访问密码: 2651)
Fast Data Processing with Spark, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959417211-856d08?p2651 (访问密码: 2651)
OReilly.Learning.Spark.2015.1.pdf: https://url41.ctfile.com/f/49289241-959417292-90c1bc?p2651 (访问密码: 2651)
High Performance Spark.pdf: https://url41.ctfile.com/f/49289241-959417439-7e7893?p2651 (访问密码: 2651)
Machine Learning with PySpark.pdf: https://url41.ctfile.com/f/49289241-959417580-5941b3?p2651 (访问密码: 2651)
Spark for Python Developers.pdf: https://url41.ctfile.com/f/49289241-959417721-d59fbe?p2651 (访问密码: 2651)
Spark Cookbook.pdf: https://url41.ctfile.com/f/49289241-959417811-19c75d?p2651 (访问密码: 2651)
Big Data Analytics with Spark.pdf: https://url41.ctfile.com/f/49289241-959417907-41dbce?p2651 (访问密码: 2651)
PySpark SQL Recipes.pdf: https://url41.ctfile.com/f/49289241-959417970-c23242?p2651 (访问密码: 2651)
Advanced Analytics with Spark Patterns for Learning from Data at Scale .pdf: https://url41.ctfile.com/f/49289241-959417997-a5e3f5?p2651 (访问密码: 2651)
OReilly.Advanced.Analytics.with.Spark.Patterns.for.Learning.from.Data.at.Scale.pdf: https://url41.ctfile.com/f/49289241-959418024-2ff34c?p2651 (访问密码: 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?p2651 (访问密码: 2651)
