如何在mySQL中动态选择列名

Question

我想选择列名，但是我不知道表结构，并且它可能会改变，所以我不能硬编码带有列名的选择语句。我也不想选择每一列。有没有简单的方法来做到这一点？

我认为这可能是这两个查询的某种组合，但我的SQL水平并不那么好。

SHOW COLUMNS FROM table_name;
SELECT * FROM table_name; 

我尝试使用子查询，但它没有起作用。似乎什么也没有发生，我没有收到错误提示，只是没有结果。

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

也许我需要进行联接？... 无论如何，任何帮助都将是极好的，谢谢。

- TheSnooker

---

表格结构不断变化似乎很奇怪，听起来像是设计缺陷。请问您能否发布当前的表格结构或提供更多关于表格的上下文信息？ - Taryn

它并非不断变化。然而表的最终结构还没有确定，因此我正在尝试制作一个聪明到足以适应未来任何更改的系统。我事先不知道列名。我可以用两个单独的 PHP 查询来完成这个任务，但我认为我也可以在数据库层面上完成。我在考虑获取列名，然后使用该数据查询表格，基本上构建一个动态选择语句。 - TheSnooker

你可以使用动态SQL来实现这个功能（实际上需要两个查询），但是如果你不想选择所有的列，你需要在查询中编写代码，从元数据中选择列并确定包含列的条件（以及任何特定的排序方式）。但是你不能直接将元数据查询的结果转换为选择查询的选择列表。 - Cade Roux

@Cade Roux 我对列名的标准是在列名前加上一个前缀。我想我可以根据这个前缀来排除或包含列。"SHOW COLUMNS FROM table_name WHERE Field LIKE %prefix%" - TheSnooker

Answer 1

试试这个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。

在联接和其他方面，你能做的有限。

你将验证移动到运行时，这样更容易被测试遗漏。

你希望能够轻松处理模式更改。 这种技术只能处理你能预见的某种类型的模式更改，而其他类型的更改你可能仍需要更改此代码。

- Cade Roux

---

是的，我明白你的意思。我本来希望能够创建一种智能查询系统，根据我可以定义和更改的一组规则仅获取某些列。我越深入研究这种方法，就越意识到它可能会产生比它所值得的更多的复杂性。不过，我从未想过使用 CONCAT，聪明的想法。 - TheSnooker

@TheSnooker 代码生成和动态SQL在使系统更易于维护方面有一定的作用。事实上，我有一个完整的演示文稿，介绍如何使用元数据进行代码生成和数据库代码健康监测。但我认为它在未来保护这个特定场景方面并没有特别好的地方。 - Cade Roux