Mysql查询特定值是哪些表哪些字段

摘自网上 

 

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

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

drop table if exists tmp_table;


CREATE TABLE tmp_table (

  tablename   VARCHAR(1024) null,

  columnname  VARCHAR(1024) null,

  columnvalue VARCHAR(1024) null

);


DROP PROCEDURE IF EXISTS search_value;

DELIMITER $$
-- v1内容值
CREATE PROCEDURE search_value(v1 VARCHAR(1024))
  BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE m_table VARCHAR(64);
    DECLARE m_column VARCHAR(64);

    -- 查询数据库字段类型为'varchar' 的字段
    DECLARE m_tables CURSOR
    FOR
      select table_name, column_name
      from information_schema.columns
      where data_type = 'varchar'
      -- 注意修改这里的 table_schema
      and table_schema = 'table_schema'
    --      and table_name = 'biz_patient_register'
    ;
    declare continue handler for not FOUND set done = 1;

    set @_v = v1;
    open m_tables;
    FETCH m_tables
    INTO m_table, m_column;
    WHILE done != 1 do
      #       insert into tmp_table select m_table as tablename, m_column as columnname, v1 as columnvalue;
      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,
                          '` = ''%', v1, '%'';');
      -- 编译sql
      prepare stmt from @m_sql;

      -- 执行sqL
      EXECUTE stmt;
      deallocate prepare stmt;
      #     select m_table, m_column;
      FETCH m_tables
      INTO m_table, m_column;
    END WHILE;

    CLOSE m_tables;
  End $$
DELIMITER ;

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