Zmirror
Zmirror
2017-05-27 09:11

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

  • 存储

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 $$
这是网上找的存储过程 以前没写过 一直报错

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

0条回答