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

摘自网上 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70

-- 查询整个数据库中某个特定值所在的表和字段的方法 # 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; -- 查询存储过程执行的结果