MySQL traverses all fields of all tables to find character data

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)