php 使用通配符选择以 XXX 开头的所有列？

Selecting all columns that start with XXX using a wildcard?

提问by Chris

I have several columns in my databases with similar names. How do I select those based on the word they start with? Here's an example table layout:

我的数据库中有几列名称相似。我如何根据它们开头的单词选择它们？这是一个示例表格布局：

I tried selecting all info for a particular thing (food kind in this example) using

我尝试使用

$Food = "Vegetable"; 
mysql_query("SELECT `" . $Food . " %` FROM `Foods`");

but it didn't seem to work.

但它似乎没有用。

Any help would be appreciated :-)

任何帮助，将不胜感激 ：-）

EDIT: Apparently this wasn't clear from my example, but I already know all column's first words. The columns are always the same and no 'food kinds' are ever added or deleted. The PHP variable is only there to determine which one of a couple of set kinds I need.

编辑：显然，这在我的例子中并不清楚，但我已经知道所有专栏的第一句话。列始终相同，并且不会添加或删除“食物种类”。PHP 变量仅用于确定我需要的几种集合类型中的哪一种。

采纳答案by Chad Birch

There's no way to do exactly what you're trying to. You could do another query first to fetch all the column names, then process them in PHP and build the second query, but that's probably more complex than just writing out the names that you want.

没有办法完全按照您的意愿去做。您可以先执行另一个查询以获取所有列名，然后在 PHP 中处理它们并构建第二个查询，但这可能比仅仅写出您想要的名称更复杂。

Or is there a reason this query needs to be dynamic? Will the table's structure change often?

或者这个查询需要动态的原因是什么？表的结构会经常改变吗？

回答by Joe Stefanelli

You'd have to build the SQL dynamically. As a starting point, this would get you the column names you're seeking.

您必须动态构建 SQL。作为起点，这将为您提供您正在寻找的列名称。

SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'Foods'
        AND table_schema = 'YourDB'
        AND column_name LIKE 'Vegetable%'

回答by Cato Minor

Here's a way I did it purely with MySQL:

这是我完全使用 MySQL 完成的一种方法：

SET SESSION group_concat_max_len = 2048;
SELECT GROUP_CONCAT(CONCAT(" ",CAST(column_name as CHAR(50)))) FROM information_schema.columns WHERE table_name='real_big_table' AND column_name LIKE 'prefix%' INTO @sub;
SET @x = CONCAT("SELECT ",@sub," FROM my_db.real_big_table WHERE my_db.real_big_table.country_id='US'");
PREPARE stmt FROM @x;
EXECUTE stmt;

My answer is inspired by this answer.

我的回答受到了这个答案的启发。

Note: My 'inner-DBA' (in the form of a small angel on my shoulder) tells me that needing to do this is probably a sign of bad DB structure, but my 'inner-hacker' (in the form of a small devil on my other shoulder) says "just get it done!"

注意：我的“内部 DBA”（以我肩膀上的小天使的形式）告诉我，需要这样做可能是数据库结构不好的迹象，但我的“内部黑客”（以小天使的形式）我另一只肩膀上的恶魔）说“完成它！”

回答by GB_

Convert your database to one with three columns:

将您的数据库转换为具有三列的数据库：

Product_type (vegetable, fruit, etc) 

Name (apple, carrot, etc) 

color (orange, yellow, etc)

Now you can use your wildcard to obtain only a certain type, because it now is IN a columns, not in the header.

现在您可以使用通配符仅获取特定类型，因为它现在位于列中，而不是在标题中。

回答by The_Black_Smurf

How about creating the query in 2 steps?

如何分两步创建查询？

1- Get the column's name from the db schema (or elsewhere)

1-从数据库模式（或其他地方）获取列的名称

2- Generate an sql query with the column's name that match your filter condition.

2- 使用与您的过滤条件匹配的列名称生成 sql 查询。