mysql查询某个特定值在整个数据库中所在的表和字段的方法

-- 查询整个数据库中某个特定值所在的表和字段的方法

-- 创建表来存储查询结果

drop table if exists tmp_table;

CREATE TABLE tmp_table ( 

    tablename VARCHAR(1024),

    columnname VARCHAR(1024),

    columnvalue VARCHAR(1024)

);

DROP PROCEDURE IF EXISTS  search_value; 

DELIMITER $$

-- v1内容值

CREATE PROCEDURE search_value(v1 VARCHAR(1024))        

BEGIN

DECLARE done INT DEFAULT true;

DECLARE m_table VARCHAR(64);

DECLARE m_column VARCHAR(64); 

DECLARE m_tables CURSOR 

FOR 

-- 查询数据库字段类型为'varchar' 的字段

select table_name,column_name

from information_schema.columns 

where data_type='varchar' and table_schema='ael_web';

set @_v=v1;

open m_tables;

 REPEAT

  FETCH m_tables INTO m_table, m_column;

  if not done then

    ​    ​--  拼装sql

set @m_sql = concat("insert into tmp_table select '",m_table,"' as tablename,'",m_column,"' as columnname,", m_column ," as columnvalue from ", m_table ," where " ,m_column ," like '%",v1,"%';");

    

-- 编译sql

    prepare stmt from @m_sql;

-- 执行sql

    EXECUTE stmt;  

    deallocate prepare stmt;

  end if;

  UNTIL done END REPEAT;

 CLOSE m_tables;

 

End $$

DELIMITER ; 

-- 存储过程创建完成

call search_value('152'); -- 执行存储过程
select * from tmp_table;  -- 查询存储过程执行的结果