MySQL skills

Database

1. 表间复制数据，但是排除某列

需求描述

有两张表：dev.t和pge.t，其中，pge.t中有一些batchid = 'hello'的数据，想把这些数据都写入到dev.t表中，但是__pk_key这一列除外。

在表间复制全部columns的SQL是：

insert into t1
  select * from t2 where x = 'a'; 

在表间复制部分columns的SQL是：

insert into t1 (c1, c2, c3) 
  select c1, c2, c3 from t2 where x = 'a'; 

因此，首先解决怎样得到排除某column后的其他column names.

SET @allcolumns = (SELECT GROUP_CONCAT(COLUMN_NAME) 
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_NAME = 't' AND TABLE_SCHEMA = 'pge') ;
select @allcolumns;   -- 显示全部columns
SET @columns = (replace(@allcolumns, '__pk_key,', ''));
select @columns; -- 显示除了 __pk_key 之外的其他columns

其他，@allcolumns和@columns实际上都是字符串，select @allcolumns from t 是不能被解释成 select * from t 的，需要通过prepared statment进行转换：

SET @s = CONCAT(
   'insert into dev.t (', @columns, ') ', 
   'select ', 
   @columns, 
   ' from pge.t where batchid = \'', 
   @batchUUID, 
   '\''); 
prepare stmt from @s;
execute stmt; 

大功告成。