MYSQL 中的动态列名 [英] Dynamic Column name in MYSQL

问题描述

昨天我问了一个不小心重复列名的问题，结果搞砸了.这产生了有趣的解决方案.所以我会在这里再次问同样的问题，但这次我会直接 mysql dump，所以没有混淆.

Yesterday I screwed up by asking question which had dupe column name by accident. This caused interesting solutions. So I will ask same question here again, but this time I will directly mysql dump, so there are no confusion.

CREATE TABLE `tbl1` (
  `UserID` char(15) DEFAULT NULL,
  `col1` char(15) DEFAULT NULL,
  `col2` char(15) DEFAULT NULL,
  `col3` char(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tbl2` (
  `UserID` char(15) DEFAULT NULL,
  `col4` char(15) DEFAULT NULL,
  `col5` char(15) DEFAULT NULL,
  `col6` char(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO tbl1
  (`UserID`, `col1`, `col2`, `col3`)
VALUES
  ('user1', 'one', 'two', 'three');

INSERT INTO tbl1
  (`UserID`, `col1`, `col2`, `col3`)
VALUES
  ('user1', 'oneone', 'twotwo', 'threethree');

INSERT INTO tbl2
  (`UserID`, `col4`, `col5`, `col6`)
VALUES
  ('user1', 'col4name', 'col5name', 'col6name');

在 mysql 查询之后，我正在寻找的最终结果将如下所示.

End result what I am looking for would look like this, after mysql query.

CREATE TABLE `endresult` (
  `UserID` char(15) DEFAULT NULL,
  `col4name` char(15) DEFAULT NULL,
  `col5name` char(15) DEFAULT NULL,
  `col6name` char(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

您将看到 user1 在 tbl2 上的 col4 记录col4name"下，由 user1 添加为他/她对该特定列的自定义名称.现在我希望看到这些自定义列名称显示为它们显示在最终结果"表上.

You will see that user1 has on tbl2 under col4 record "col4name" which was added by user1 for his/hers custom name for that particular column. Now I would like to see these custom column names displayed like they are displayed on "endresult" table.

我知道在最后使用 php 显示这将是首选，但我确实需要在 mysql 端执行此操作.再次感谢

I know this would be preferred to do at the end using php to display, but I really need to do this in mysql side. Thanks again

推荐答案

我认为将 SQL 语句作为字符串(动态地)执行可能是您最好的选择.请注意 SQL 注入和预期在此 SQL 语句中使用的数据.

I think executing the SQL statement as a string (dynamically) may be your best bet. Please be careful with respect to SQL injection and the data to expect to use within this SQL statement.

类似于:

SET @qry = (
            SELECT CONCAT('SELECT t1.userid, 
                           t1.col1 AS "', t2.col4, '", 
                           t1.col2 AS "', t2.col5, '",
                           t1.col3 AS "', t2.col6, '"
                    FROM tbl1 t1') AS QUERY_TEXT                      
            FROM tbl2 t2 
            WHERE t2.userid = @userId
           );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

此查询将返回来自 tbl1 的数据，但 col1、col2 和 col3 由匹配的 tbl2 记录中的值作为别名，给定特定用户 ID @userId.

This query will return data from tbl1, but col1, col2, and col3 are aliased by the values within the matching tbl2 record given a specific userid @userId.

这就是问题所在，我似乎无法弄清楚在此问题上要扩展什么以使其在单个 SELECT 语句中适用于多个用户.这是有道理的，因为当列的名称不同时，不可能正确排列列(另外，我相信这在 SQL 中是不可能做到的).

Therein lies the issue, I can't seem to figure out what to expand on this issue to make it apply to multiple users in a single SELECT statement. This makes sense, because it's not possible to line up the columns correctly when they're named different (plus, I believe that this is impossible to do in SQL).

这篇关于MYSQL 中的动态列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！