MySQL Mysql如何仅从列中进行选择，如果该列存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24194223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql How to only select from a column if the column exists
提问by Linda Keating
I need to be able to check if a column exists and if it does then I want to SELECT from it.
我需要能够检查列是否存在，如果存在，那么我想从中选择。
I am trying lots of different variations but I'm not even sure if this is possible.
我正在尝试许多不同的变化，但我什至不确定这是否可能。
Here's my latest attempt:
这是我最近的尝试：
SELECT
IF (EXISTS (SELECT `Period` AS `Period` FROM myview), `PERIOD`,
IF (EXISTS (SELECT `Country` AS `COUNTRY` FROM myview),`COUNTRY` FROM myview ;
Any ideas?
有任何想法吗？
EDIT
编辑
I had seen the other question on here: MySQL, Check if a column exists in a table with SQL
我在这里看到了另一个问题：MySQL, Check if a column exists in a table with SQL
But I still struggle with the if statement. I can check to see if the column exists using the answer in the question above. But my question is - how to execute a select statement from that column if the result is found to be true.
但我仍然在为 if 语句而挣扎。我可以使用上述问题中的答案检查该列是否存在。但我的问题是 - 如果发现结果为真，如何从该列执行 select 语句。
EDIT 2
编辑 2
The answer below indicates that I should use the BEGIN and END statement and this makes sense. However, my query complains at the first line. It says 'unexpected IF' - can anybody confirm if this is the right syntax fro MYSQL?
下面的答案表明我应该使用 BEGIN 和 END 语句，这是有道理的。但是，我的查询在第一行抱怨。它说“意外的 IF” - 任何人都可以确认这是否是 MYSQL 的正确语法？
if( exists (SELECT *
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'db_name'
AND TABLE_NAME = 'view_name'
AND COLUMN_NAME = 'column_name') )
begin
SELECT `column_name` FROM `view_name`
end
Thanks in advance.
提前致谢。
回答by James McDonnell
This query will give you whether a column exists.
此查询将为您提供列是否存在。
SELECT *
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name'
AND TABLE_NAME = 'table_name'
AND COLUMN_NAME = 'column_name'
If you want to check if some columns exist then perform a select statement you need to first check your columns exist. Then perform the select:
如果要检查某些列是否存在然后执行选择语句，您需要首先检查您的列是否存在。然后执行选择：
if (exists (SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'myview' AND COLUMN_NAME = 'Period') and exists (SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'myview' AND COLUMN_NAME = 'Country'))
begin
select `Period`, `Country` from myview
end
If the IF condition is true, then you will execute anything inside the BEGIN and END.
如果 IF 条件为真，那么您将执行 BEGIN 和 END 中的任何内容。
回答by Rehmat
I came across the same situation where I had some product tables created by sheets uploaded by users. Sometimes, the sheets did not have column named "obsolete", so I had to import all products from the sheet but not the obsolete ones.
我遇到了同样的情况，我有一些由用户上传的工作表创建的产品表。有时，工作表没有名为“过时”的列，因此我必须从工作表中导入所有产品，但不能导入过时的产品。
I am not modifying my query based on the original question that was asked, but here is my solution:
我没有根据提出的原始问题修改我的查询，但这是我的解决方案：
SELECT
t2.model,
(
SELECT
COUNT(*) AS _count
FROM db.table t1
WHERE
`obsolete`=1
AND t1.model=t2.model
) AS `obsolete`
FROM (
SELECT
0 AS `obsolete`,
t3.model
FROM db.table t3
) t2
There are 2 most important parts in this query:
此查询中有两个最重要的部分：
- We are selecting 0 AS
obsoleteas dummy to fool MySql which will be used even if column does not exist when selecting COUNT(*). - We have named tables as t1 & t2 to match the column model as t1.model=t2.model.
- 我们选择 0 AS
obsolete作为虚拟对象来欺骗 MySql，即使在选择 COUNT(*) 时列不存在也会使用它。 - 我们将表命名为 t1 和 t2，以匹配 t1.model=t2.model 的列模型。
