mysql - MySQL查询将两列转换为一行(选择默认值)

我想从任何表中选择默认值。
我想要make方法从任何表中获取所有默认值，以在自动生成的表格等中使用。

因此，我创建了以下查询：

SELECT `COLUMN_NAME`,`COLUMN_DEFAULT` FROM information_schema.`COLUMNS` WHERE `TABLE_SCHEMA`='databasename' AND `TABLE_NAME`='tablename' LIMIT 1

结果很简单

| SELECT  | COLUMN_DEFAULT |
+---------+----------------+
| id      | null           |
| name    | 'defname'      |
| content | 'defcontent'   |

我需要将其转换为这样

| id   | name    | content   |
+------+---------+-----------+
| null | defname | decontent |

谢谢。

最佳答案

由于不同表的数字列可能会有所不同，因此无法在此处实现单个简单的SQL语句。

我建议定义一个存储过程，以标识所讨论表的定义列，并根据这些名称和值生成数据透视表。

遵循stored procedure可以帮助您执行相同操作。

delimiter //
drop procedure if exists default_values //

create procedure default_values( in db varchar(255), in tbl varchar(255) )
begin
  declare c_name varchar(255) default '';
  declare all_names_read boolean default false;

  declare c_names cursor for
    select column_name from information_schema.columns
      where table_schema=db and table_name=tbl;
  declare continue handler for not found
  -- declare continue handler for SQLSTATE '02000'
    set all_names_read = true;

  set @sql_query = '\nselect';
  set @rowNumber = 0;

  open c_names;
  repeat
    fetch c_names into c_name;

    set @sql_query =
          concat( @sql_query,
                  if( all_names_read, '\n', if( @rowNumber < 1, '\n', ',\n' ) )
                 );

    if( ! all_names_read ) then
      set @rowNumber = @rowNumber + 1;

      set @sql_query =
            concat( @sql_query, '    max( case column_name when \'',
                    c_name, '\' then column_default end ) as \'',
                    c_name, '\'' );
    end if;

    until all_names_read
  end repeat;

  close c_names;

  set @sql_query =
        concat( @sql_query,
                'from information_schema.columns\n',
                'where table_schema=? and table_name=?' );
  set @db_name = db;
  set @tbl_name = tbl;

  prepare stmt from @sql_query;
  execute stmt using @db_name, @tbl_name;
  -- execute stmt;
  deallocate prepare stmt;
end;
//

delimiter ;

以下是对定义的存储过程的测试。

mysql> create table test.tide
    -> ( t timestamp, i int default 9, d date, e enum( 'yes', 'no' ) default 'no' );

mysql> desc test.tide;
+-------+------------------+------+-----+-------------------+------...
| Field | Type             | Null | Key | Default           | Extra...
+-------+------------------+------+-----+-------------------+------...
| t     | timestamp        | NO   |     | CURRENT_TIMESTAMP | on up...
| i     | int(11)          | YES  |     | 9                 |
| d     | date             | YES  |     | NULL              |
| e     | enum('yes','no') | YES  |     | no                |
+-------+------------------+------+-----+-------------------+------...
4 rows in set (0.02 sec)

mysql> call default_values( 'test', 'tide' );
+-------------------+------+------+------+
| t                 | i    | d    | e    |
+-------------------+------+------+------+
| CURRENT_TIMESTAMP | 9    | NULL | no   |
+-------------------+------+------+------+

以上结果是通过存储过程执行生成的查询。
生成的查询是这样的：

mysql> select @sql_query\G
*************************** 1. row ***************************
@sql_query:
select
    max( case column_name when 't' then column_default end ) as 't',
    max( case column_name when 'i' then column_default end ) as 'i',
    max( case column_name when 'd' then column_default end ) as 'd',
    max( case column_name when 'e' then column_default end ) as 'e'
from information_schema.columns
where table_schema=? and table_name=?