MySQL find a value in the library which table that a field (semi-finished)

MySQL find a value in the library which table that a field (semi-finished)

DROP PROCEDURE IF EXISTS ' test3 ';

CREATE definer = ' root ' @ ' localhost ' PROCEDURE ' test3 ' ()

BEGIN

DECLARE tablename varchar (100);

DECLARE columnname varchar (100);

DECLARE b int default 0;

DECLARE cur CURSOR

For

Select Column_name,table_name from INFORMATION_SCHEMA. COLUMNS where table_name in (select table_name from information_schema.tables where table_schema= ' database name to find ') Li            MIT 0,100; #子查询是根据库名得到该库的所有表名, and then get all the column names of the table based on the table name

DECLARE CONTINUE HANDLER for not FOUND SET B = 1;

OPEN cur;

FETCH cur into columnname,tablename;

While b<>1 do

#INSERT into TBT (CN,TB) VALUES (columnname,tablename);

Set @sqlcmd = concat (' SELECT ', ColumnName, ' from the database name to find . ', TableName, ' where ', ColumnName, ' like '% to find the value % "'); #concat () function is to merge strings

PREPARE STMT1 from @sqlcmd; Convert #把 @sqlcmd string to SQL statement

EXECUTE STMT1; #执行sql语句

FETCH cur into columnname,tablename;

End while;

Close cur;

END;

It is important to note that because there may be too many fields, the limit is used, so finding the field is not complete and modifies the value of limit as appropriate.

MySQL find a value in the library which table that a field (semi-finished)