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

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问