mysql根据列名获得值_根据值获得值相同的字段名和所在的表（mysql）

这个问题来源于一个奇怪的需求。。太长了懒得解释了。。反正就是希望根据值找到这个值相同的字段名和所在表

用程序写可以 我不用了

直接sql写应该不行吧

就用存储过程了

begin

declare loopIndex int;

declare sqlstr varchar(50);

declare tableLoop int;

declare tableAmount int;

set loopIndex = 0;

set tableLoop = 0;

SELECT count(TABLE_NAME) INTO tableAmount

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'DB_NAME';

WHILE tableLoop < tableAmount DO

SELECT TABLE_NAME INTO @currentTable

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'shopnc' LIMIT tableLoop,1;

SET tableLoop = tableLoop + 1;

SET @getColumnAmountSql = CONCAT("SELECT count(column_name) INTO @columnAmount from information_schema.columns where table_schema = 'DB_NAME' AND table_name = '",@currentTable,"'");

PREPARE STMT1 FROM @getColumnAmountSql;

EXECUTE STMT1;

SET loopIndex = 0;

WHILE loopIndex < @columnAmount DO

SET @getCurrentColumnSql = CONCAT("SELECT column_name INTO @tempColumn from information_schema.columns where table_schema = 'shopnc' AND table_name = '",@currentTable,"' LIMIT ",loopIndex,",1");

PREPARE STMT2 FROM @getCurrentColumnSql;

EXECUTE STMT2;

SET loopIndex = loopIndex+1;

SET @getEquelValueColumnSql = CONCAT("select count(",@tempColumn,") INTO @tempValue from ",@currentTable," where ",@tempColumn," = ",memberid);

PREPARE STMT3 FROM @getEquelValueColumnSql;

EXECUTE STMT3;

IF @tempValue > 0 THEN

select @currentTable,@tempColumn;

END IF;

END WHILE;

END WHILE;

end

直接用的时候改掉DB_NAME就好了 还有要建立一个存储过程先 一句命令的事不会不懂吧

赶着下班了 先写到这里了 明天记得再细改了 拜拜