如何在mySQL中动态选择列名

2021-11-20 00:00:00 sql metadata mysql dynamic-sql

我想选择列名，但我不知道提前表结构，它可能会改变，所以我不能只用列名硬编码 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.

相关文章