将字符串动态转换为列名.的MySQL [英] Dynamic conversion of string into column name. MySQL

问题描述

我有两个表:items和orders

items
--------------
id (int) | type_1 (int) | type_2  (int)|

orders
--------------
id (int) | transaction_type enum ('type_1', 'type_2')

基本上，我想执行以下操作:

Basically, I want to do the following:

select (select transaction_type from orders where id=1) from items;

因此，问题在于select transaction_type from orders where id=1返回的string无法转换为列名.

So, the problem is that string returned by select transaction_type from orders where id=1, cannot be converted into column name.

推荐答案

您可能希望看到此处:

You may want to see the answer to this question, which I believe is what you're trying to accomplish. In short, the answer suggests using prepared statements in order to simulate an eval()-esque functionality. In your case, this may work (you can see the SQLFiddle here:

SELECT transaction_type FROM orders WHERE id=1 into @colname;
SET @table = 'items';
SET @query = CONCAT('SELECT ',@colname,' FROM ', @table);

PREPARE stmt FROM @query;
EXECUTE stmt;

我不会声称自己是工作中的基础机制的任何专家，但是根据评论，它似乎可以达到目标.再次，这是从另一个答案中采纳的，因此，如果它有效，请确保为那个+1:)

I won't claim to be any sort of expert on the underlying mechanics at work, but per the comments it seems to achieve the goal. Again, this was adopted from another answer, so if it works makes sure to +1 that one :)

这篇关于将字符串动态转换为列名.的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！