根据另一个表中定义的映射重命名表列的列-使用MYSQL [英] Renaming the columns of a table columns according to a mapping defined in another table - with MYSQL

问题描述

我想创建一个MYSQL存储过程，该存储过程以动态方式 重命名 TABLE_EXAMPLE 的列名称，其内容来自 TABLE_MASTER ->预期结果显示在 TABLE_RESULT 上.

I'd like to create a MYSQL stored procedure that dynamically renames the column names of TABLE_EXAMPLE with content from TABLE_MASTER -> the expected result is shown on TABLE_RESULT.

这是表格的内容:

    TABLE_EXAMPLE (THE HEADERS MIGHT CHANGE BUT THEY ARE MAPPED WITH A COLUMN IN TABLE_EXAMPLE):

           |header01 | header02|...|header n|
           |data01 ..| data02..|...|data 0n|
           |data11 ..| data12..|...|data 1n|
             ..........etc.................
           |data n1..|data n2..|...|data nn|

    TABLE_MASTER (STATIC TABLE, UNCHANGED):

           |ORIGIN| TARGET| NAME|
           |header01|header_master01|Paul|
           |header02|header_master02|Paul|
            ..........etc.................
           |header n|header_master n|Paul|

预期结果包含来自TABLE_EXAMPLE的数据，但具有通过TABLE_MASTER.TARGET找到的映射列名称:

The expected result contains the data from TABLE_EXAMPLE but with mapped column names found via TABLE_MASTER.TARGET:

         TABLE_RESULT:
            |data_master01|data_master02|...|data_master0n| NAME|
            |data01.......|data02.......|...|data 0n.......|Paul|
            |data11.......|data12.......|...|data 1n.......|Paul|  
            .........................etc.........................
           |data n1..|data n2...........|...|data nn.......|Paul|

PS:一个简单的例子:更改表table_example更改列old new char(250)".

PS: A simple: "ALTER TABLE table_example CHANGE COLUMN old new char(250)", won't do.

感谢您的帮助！

我试图写这篇文章，但没有成功，因为'oldname'和'newname'不被视为变量.

EDIT 1: I have tried to write this but without success because 'oldname' and 'newname' are not considered as variables.

BEGIN

DECLARE n INT(10) ; DECLARE i INT(10) ;
DECLARE oldname VARCHAR(40); DECLARE newname VARCHAR(40);

SET n=(SELECT count(id) FROM `master_table` where `name`='paul');

SET i=1; WHILE i<n DO 

SET oldname=(SELECT `COLUMN_NAME`  FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE 
`TABLE_SCHEMA`='mydb'AND `TABLE_NAME`='table_example' LIMIT 1, 1) ; 
SET newname=(SELECT TARGET FROM MASTER_TABLE WHERE ORIGIN='oldname');

ALTER TABLE `table_example` CHANGE oldname newname VARCHAR(50) NOT NULL;

SET i=i+1 ; END WHILE ;
END

推荐答案

我认为您希望按名称选择列，并且这些名称是TABLE_MASTER中的字符串.

I think I understand you want to select a column by name, and the names are strings in your TABLE_MASTER.

您无法在单个SQL查询中执行此操作，因为SQL无法使用字符串表达式选择列.字符串和标识符之间有区别.例如，这将从标识符的列中选择数据:

You can't do this in a single SQL query, because SQL cannot select a column by using a string expression. There's a difference between a string and an identifier. For example, this selects data from a column by identifier:

SELECT header01 ...

但是下面是一个字符串表达式(一个简单的表达式，它只是一个常量值).它仅返回固定的字符串"header01"，而不返回该名称的列中的数据:

But the following is a string expression (a simple one, which is just a constant value). It returns only a fixed string 'header01', NOT the data from a column of that name:

SELECT 'header01' ...

同样，在选择列表中使用任何其他表达式只会选择该表达式的值，而不是选择存储在该表达式的字符串值所命名的列中的数据.

Likewise, using any other expression in a select-list only selects the value of that expression, NOT the data stored in a column named by the string value of the expression.

因此，如果要查询返回由其他变量或表达式命名的动态列，则不能在读取该表达式的同一查询中执行此操作.您必须根据读取的值来格式化新的SQL查询.这称为动态SQL语句(spencer7593已经提到过，在我编写自己的答案时，他曾发布了答案).

Therefore if you want a query to return a dynamic column named by some other variable or expression, you can't do it in the same query where you read that expression. You have to format a new SQL query from the values you read. This is called a dynamic SQL statement (already mentioned by spencer7593, who posted an answer while I was writing my own answer).

您可以使用TABLE_MASTER设置动态SQL语句的格式，以获取列并重新定义其别名:

You could use your TABLE_MASTER to format a dynamic SQL statement to fetch columns and redefine their alias:

SELECT CONCAT(
  'SELECT ', 
   GROUP_CONCAT(CONCAT(ORIGIN, ' AS ', TARGET)), ', ', 
   QUOTE(MAX(NAME)), ' AS NAME ',
  'FROM TABLE_EXAMPLE'
) INTO @sql
FROM TABLE_MASTER;

其结果是形成另一个SELECT语句的字符串，该语句根据需要重命名列:

The result of this is a string that forms another SELECT statement, this one renames the columns as you want:

SELECT header01 AS header_master01,header02 AS header_master02, 'Paul' AS NAME FROM TABLE_EXAMPLE  

然后，您可以将存储在@sql中的字符串用作动态SQL查询.

Then you can use the string stored in @sql as a dynamic SQL query.

这是执行此操作的过程:

Here's the procedure that does this:

DELIMITER ;;

CREATE PROCEDURE MyProc()
BEGIN
    SELECT CONCAT(
      'SELECT ', 
       GROUP_CONCAT(CONCAT(ORIGIN, ' AS ', TARGET)), ', ', 
       QUOTE(MAX(NAME)), ' AS NAME ',
      'FROM TABLE_EXAMPLE'
    ) INTO @sql
    FROM TABLE_MASTER;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

DELIMITER ;

调用该过程，并获得结果:

Call the procedure, and get the result:

CALL MyProc();

+-----------------+-----------------+------+
| header_master01 | header_master02 | NAME |
+-----------------+-----------------+------+
| data01          | data02          | Paul |
| data11          | data12          | Paul |
+-----------------+-----------------+------+

我不得不评论说这很麻烦.我宁愿获取数据库中的数据，然后在我的应用程序代码中将其重新格式化. 这样，我就不必使用任何动态SQL来格式化列.

I have to comment that this is a lot of trouble to go through. I would rather fetch the data as it is in the database, and reformat it in my application code. Then I wouldn't have to use any dynamic SQL to format the columns.

这篇关于根据另一个表中定义的映射重命名表列的列-使用MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！