Mysql，检查每一列的所有行是否具有相同的值

Question

我的一个 friend 想要检查表中每一列的所有行是否具有相同的值。如果他们这样做，则打印该值。否则只打印一个空字符串或 null 或其他东西。

想象一下这个表:

+--------+----------+-----+
|  Name  | Lastname | Age |
+--------+----------+-----+
| Peter  | White    | 30  |
| Marry  | Jane     | 30  |
| John   | Doe      | 30  |
+--------+----------+-----+

所需查询的结果将输出以下内容:

+--------+----------+-----+
|  Name  | Lastname | Age |
+--------+----------+-----+
|  NULL  |   NULL   | 30  |
+--------+----------+-----+

我尝试创建一个函数，在该函数中我将获取给定表的列，遍历每个列名并执行查询。但是由于我不熟悉 Mysql，我显然遗漏了一些东西，我无法弄清楚如何实现我在这里想要做的事情。

DROP PROCEDURE IF EXISTS test;

DELIMITER //

create procedure test()
begin
    declare i int(11);
    declare col_name varchar(50);
    declare num_rows int(11);
    DECLARE col_names CURSOR FOR
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name='name_of_my_table' and table_schema='name_of_db'; 

    select FOUND_ROWS() as num_rows;
    set i = 1;
    open col_names;
        the_loop: LOOP

           IF i > num_rows THEN
                CLOSE col_names;
                LEAVE the_loop;
            END IF;

            FETCH col_names 
            INTO col_name;     

            -- Here I would like to perform a query for each column
            select count(*), col_name from name_of_my_table group by col_name;
            -- Then I was thinking of making an if/ else condition to check 
            -- if I get more than 1 result per column, implying that                 
            -- not all rows have the same value for this column.

            SET i = i + 1;  
        END LOOP the_loop;

    CLOSE col_names;
END//

DELIMITER ;

call test;

输出的是找到的最后一列的计数和列名，这确实有意义。我不确定我尝试做的事情是否仅适用于 Mysql，我可以在 PHP 中轻松地做到这一点，但我想知道我是否也可以通过单个查询来做到这一点。

Answer 1

尝试下面的查询。

选择 if(count(distinct(Name))=1,Name,null), if(count(distinct(Lastname))=1,Lastname,null), if(count(distinct(Age)) =1，年龄，空)
来自你的表；