MySQL traverses all fields of all tables to find character data
There are some data searches at work, but it is not clear which field in which library, which table, especially sensitive character search. If the amount of data is not large, we can solve the problem by traversing the entire library and characters in the table to find relevant data. question.
We can write a stored procedure to solve this problem
Step 1. First find a library to create a table to store the query (the table and the stored procedure are in the same library)
drop table if exists searchdata; CREATE TABLE if not exists `searchdata` ( dbname varchar(200), tbname varchar(200), `sqlscript` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Step 2: Create stored procedure
The stored procedure is as follows:
The received parameters are:
SearchDB: search database
SearchStr: Search string
ResTable: Table to store results (library.table)
drop procedure if exists serarch_data; DELIMITER $$ CREATE PROCEDURE `serarch_data`( in SearchDB varchar(200), in SearchStr varchar(200) ) begin declare par_tbname varchar(200); declare done int default 0; #Define cursor variable #Define cursor declare get_name cursor for select table_name from information_schema.tables where table_schema=SearchDB; # Capture the not found error thrown by the system. If caught, set done to 1, which is equivalent to a try exception. declare continue handler for not found set done=1; SET SESSION group_concat_max_len=99999; #Open cursor open get_name; #loop loop definition does not use ; read_name:loop # 3. Use cursor 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 sqlscript from( select table_name, replace(group_concat(col ),',',' or ') as wh from ( -- select table_name , concat( column_name,' like ''%s3.%''') as col select table_name , concat( '`',column_name,''',' like ''%',SearchStr,'%''') as col from information_schema.columns where table_schema=SearchDB and table_name=par_tbname and data_type in( 'text','char','longtext','mediumtext','varchar')) as a group by table_name ) res; # If an exception occurs if done = 1 then leave read_name; end if ; end loop; close get_name; #Close cursor end$$ DELIMITER ;
How to use:
Enter the library name and search string call serarch_data('goodhope_dev','Hello'); truncate table searchdata; select * from searchdata;
3. Batch generation and calling stored procedure 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. Batch execution of stored procedures
5. Generate query sql in batches
select concat('select 1 ,''',dbname,'.',tbname,''' from (',sqlscript,') as ',tbname,' union all') as sqlscript from searchdata where dbname not in ('quote','exchange_otc','gateway_exchange','performance_schema','test');
6. Execute the SQL generated in step 5 in batches
MYSQL series books
Highly available mysql: https://url41.ctfile.com/f/49289241-959127723-de4738?p=2651 (Access password: 2651)
MySQL King’s Road to Advancement.pdf: https://url41.ctfile.com/f/49289241-959127432-204284?p=2651 (Access password: 2651)
MySQL Technology Insider InnoDB Storage Engine 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p=2651 (Access password: 2651)
MySQL Technology Insider 4th Edition.pdf: https://url41.ctfile.com/f/49289241-959125506-a5bcec?p=2651 (Access password: 2651)
MySQL management, performance tuning, high availability and monitoring (Second Edition).pdf: https://url41.ctfile.com/f/49289241-959124249-d59f54?p=2651 (Access password: 2651)
In-depth explanation of MySQL database development, optimization, management and maintenance 2nd edition.pdf: https://url41.ctfile.com/f/49289241-961464090-68bf10?p=2651 (Access password: 2651)
High Performance MySQL. 3rd Edition.Baron Schwartz.pdf: https://url41.ctfile.com/f/49289241-961462308-52cc5d?p=2651 (Access password: 2651)
MYSQL Kernel: INNODB Storage Engine Volume 1.pdf: https://url41.ctfile.com/f/49289241-961461357-ee63e3?p=2651 (Access password: 2651)
MySQL Technology Insider InnoDB Storage Engine 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p=2651 (Access password: 2651)
MySQLDBA training.pdf: https://url41.ctfile.com/f/49289241-961459500-9b201d?p=2651 (Access password: 2651)
MySQL5.7 from beginner to proficient.pdf: https://url41.ctfile.com/f/49289241-961459329-48cbcf?p=2651 (Access password: 2651)
Highly available mysql.pdf: https://url41.ctfile.com/f/49289241-959127723-de4738?p=2651 (Access password: 2651)
HIVE e-book
Practical Hive.pdf: https://url41.ctfile.com/f/49289241-959129883-d35ee9?p=2651 (Access password: 2651)
Hive-Succinctly.pdf: https://url41.ctfile.com/f/49289241-959129709-30f30b?p=2651 (Access password: 2651)
Apache Hive Essentials.pdf: https://url41.ctfile.com/f/49289241-959129691-b1a4aa?p=2651 (Access password: 2651)
Apache Hive Cookbook.pdf: https://url41.ctfile.com/f/49289241-959129619-3a8ea6?p=2651 (Access password: 2651)
hadoop e-book
Practical Hadoop Migration.pdf: https://url41.ctfile.com/f/49289241-959131470-dd3e24?p=2651 (Access password: 2651)
Hadoop in action – Lu Jiaheng (full HD version).pdf: https://url41.ctfile.com/f/49289241-959131365-433ec9?p=2651 (Access password: 2651)
Hadoop & Spark big data development practice.pdf: https://url41.ctfile.com/f/49289241-959131032-ba40ea?p=2651 (Access password: 2651)
Expert Hadoop Administration.pdf: https://url41.ctfile.com/f/49289241-959130468-ba70cd?p=2651 (Access password: 2651)
Big Data Forensics – Learning Hadoop Investigations.pdf: https://url41.ctfile.com/f/49289241-959130435-9ab981?p=2651 (Access password: 2651)
python e-book
python learning manual.pdf: https://url41.ctfile.com/f/49289241-959129403-5b45b1?p=2651 (Access password: 2651)
Python Basics Tutorial – 3rd Edition.pdf: https://url41.ctfile.com/f/49289241-959128707-de6ef2?p=2651 (Access password: 2651)
Python programming: from entry to practice.pdf: https://url41.ctfile.com/f/49289241-959128548-ce965d?p=2651 (Access password: 2651)
Python Projects for Beginners.pdf: https://url41.ctfile.com/f/49289241-959128461-b53321?p=2651 (Access password: 2651)
kafka e-book
Learning Apache Kafka, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959134953-a14305?p=2651 (Access password: 2651)
Kafka authoritative guide.pdf: https://url41.ctfile.com/f/49289241-959134932-295734?p=2651 (Access password: 2651)
Kafka in Action.pdf: https://url41.ctfile.com/f/49289241-959134116-12111a?p=2651 (Access password: 2651)
Apache Kafka in action.pdf: https://url41.ctfile.com/f/49289241-959133999-76ef77?p=2651 (Access password: 2651)
Apache Kafka Cookbook.pdf: https://url41.ctfile.com/f/49289241-959132547-055c36?p=2651 (Access password: 2651)
spark e-book
Spark best practices.pdf: https://url41.ctfile.com/f/49289241-959415393-5829fe?p=2651 (Access password: 2651)
Data algorithm–Hadoop-Spark big data processing skills.pdf: https://url41.ctfile.com/f/49289241-959415927-5bdddc?p=2651 (Access password: 2651)
Spark big data analysis in practice.pdf: https://url41.ctfile.com/f/49289241-959416377-924161?p=2651 (Access password: 2651)
Spark 2.0 for Beginners.pdf: https://url41.ctfile.com/f/49289241-959416710-7ea156?p=2651 (Access password: 2651)
Pro Spark Streaming.pdf: https://url41.ctfile.com/f/49289241-959416866-6116d7?p=2651 (Access password: 2651)
Spark in Action.pdf: https://url41.ctfile.com/f/49289241-959416986-e759e9?p=2651 (Access password: 2651)
Learn PySpark.pdf: https://url41.ctfile.com/f/49289241-959417049-ac04a0?p=2651 (Access password: 2651)
Fast Data Processing with Spark.pdf: https://url41.ctfile.com/f/49289241-959417157-8ec3b0?p=2651 (Access password: 2651)
Fast Data Processing with Spark, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959417211-856d08?p=2651 (Access password: 2651)
OReilly.Learning.Spark.2015.1.pdf: https://url41.ctfile.com/f/49289241-959417292-90c1bc?p=2651 (Access password: 2651)
High Performance Spark.pdf: https://url41.ctfile.com/f/49289241-959417439-7e7893?p=2651 (Access password: 2651)
Machine Learning with PySpark.pdf: https://url41.ctfile.com/f/49289241-959417580-5941b3?p=2651 (Access password: 2651)
Spark for Python Developers.pdf: https://url41.ctfile.com/f/49289241-959417721-d59fbe?p=2651 (Access password: 2651)
Spark Cookbook.pdf: https://url41.ctfile.com/f/49289241-959417811-19c75d?p=2651 (Access password: 2651)
Big Data Analytics with Spark.pdf: https://url41.ctfile.com/f/49289241-959417907-41dbce?p=2651 (Access password: 2651)
PySpark SQL Recipes.pdf: https://url41.ctfile.com/f/49289241-959417970-c23242?p=2651 (Access password: 2651)
Advanced Analytics with Spark Patterns for Learning from Data at Scale .pdf: https://url41.ctfile.com/f/49289241-959417997-a5e3f5?p=2651 (Access password: 2651)
OReilly.Advanced.Analytics.with.Spark.Patterns.for.Learning.from.Data.at.Scale.pdf: https://url41.ctfile.com/f/49289241-959418024-2ff34c?p=2651 (Access password : 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 (Access password: 2651)