如何在MySQL中动态选择列名 [英] How to select column names dynamically in mySQL

问题描述

我想选择列名，但我不知道表结构是否会提前更改，因此它可能会发生变化，因此我不能只对带有列名的select语句进行硬编码.我也不想选择每一列.有没有简单的方法可以做到这一点?

I want to select column names but I don't know the table structure ahead of time and it may change so I can't just hard code the select statement with column names. I also do NOT want to select every column. Is there and easy way to do this?

我认为这是这两个查询的某种组合，但是我的SQL不太好.

My thoughts are it's some kind of combination of these two queries but my SQL is not that good.

SHOW COLUMNS FROM table_name;
SELECT * FROM table_name; 

我尝试使用子选择，但是没有用.似乎什么都没发生，我没有收到错误，只是没有结果

I tried using a sub select but it didn't work. Nothing seems to happen, I don't get an error I just get no results

SELECT (SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name ='table_name') 
FROM table_name;

也许我需要加入? 无论如何，任何帮助都会很棒，谢谢

Maybe I need to do a join?.. Anyway any help would be great, thanks

推荐答案

尝试使用此 SQLFiddle :

CREATE TABLE atable (
  prefix1 VARCHAR(10)
  ,prefix2 VARCHAR(10)
  ,notprefix3 INT
  ,notprefix4 INT
);

INSERT INTO atable VALUES ('qwer qwer', 'qwerqwer', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'asdfaasd', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'qrt vbb', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'sdfg sdg', 1, 1);

SELECT CONCAT('SELECT ', GROUP_CONCAT(c.COLUMN_NAME), ' FROM atable;')
INTO @query
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'atable'
  AND c.COLUMN_NAME LIKE 'prefix%'
ORDER BY c.ORDINAL_POSITION;

PREPARE stmt FROM @query;

EXECUTE stmt;

一些问题:

您可能希望在结果集上使用某种ORDER BY.

You will likely want some kind of ORDER BY on your result set.

在连接和事物方面，您只能做些限制.

There's a limit to what you can do in terms of joins and things.

您将验证移至运行时，在测试中很可能会错过它.

You move validation to runtime where it's more likely to be missed by testing.

您希望能够轻松处理架构更改.这种技术只能处理您可以预见的某种类型的模式更改，而其他任何情况下您都可能不得不更改此代码.

You are hoping to be able to handle schema changes easily. This technique will only handle schema changes of a certain type you can foresee, and others you will probably have to change this code anyway.

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