mysql:按数字引用列 [英] mysql: referring to columns by numbers

问题描述

我想给列别名一个别名，但不知道它们的名称，而只知道表中的编号.

I want to give columns aliases without knowing their names, but only their number in the table.

类似这样的东西:

select firstColumn as myId, secondColumn as myName, thirdColumn as myLastName

我不知道列的实际名称

(我知道需求听起来很奇怪.是的，我可以知道各列的名称.这是一个技术性问题，如果您知道技术性答案，无论动机如何，都请回答.谢谢！)

(I understand that the need sounds strange. And yes I can know the names of the columns. This is a technical question, please answer if you know the technical answer, regardless of the motivation. Thank you!)

推荐答案

您能做的最接近的操作是使用INFORMATION_SCHEMA.COLUMNS从顺序位置查找列名称.我知道这不是您要的，但我认为它可能会尽可能地接近您.例如，您可以构建具有第一，第二和第五列的select语句，如下所示:

The closest you could do is use INFORMATION_SCHEMA.COLUMNS to find the column name from the ordinal position. I realize this isn't what you asked for, but I think it may be as close as you can get. For instance, you could build a select statement having the 1st, 2nd and 5th columns as follows:

SELECT CONCAT("SELECT ",
   GROUP_CONCAT(column_name SEPARATOR ", "),
   " FROM ", table_name)
FROM information_schema.columns
WHERE table_schema = database() 
    AND table_name = 'my_table' 
    AND ordinal_position IN (1,2,5) 
GROUP BY table_name 
ORDER BY ordinal_position;

这篇关于mysql:按数字引用列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！