Mysql如何只从一列中进行选择，如果该列存在

2021-12-12 00:00:00 if-statement select mysql exists

我需要能够检查列是否存在，如果存在，那么我想从中选择.

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.

这是我最近的尝试:

SELECT
IF (EXISTS (SELECT `Period` AS `Period` FROM myview), `PERIOD`,
IF (EXISTS (SELECT `Country` AS `COUNTRY` FROM myview),`COUNTRY` FROM myview ;

有什么想法吗?

编辑

我在这里看到了另一个问题:MySQL，用SQL检查表中是否存在列

I had seen the other question on here: MySQL, Check if a column exists in a table with SQL

但我仍然在为 if 语句而苦恼.我可以使用上述问题中的答案检查该列是否存在.但我的问题是 - 如果发现结果为真，如何从该列执行选择语句.

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.

编辑 2

下面的答案表明我应该使用 BEGIN 和 END 语句，这是有道理的.但是，我的查询在第一行抱怨.它说意外的 IF" - 任何人都可以确认这是否是 MYSQL 的正确语法?

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?

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

提前致谢.

推荐答案

此查询将告诉您列是否存在.

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 条件为真，那么您将执行 BEGIN 和 END 中的任何内容.

If the IF condition is true, then you will execute anything inside the BEGIN and END.

相关文章