今天朋友有个需求,要将A表合并到B表,表结构相同,但是自增主键有重复,所以需要实现insert into t_dest selecct * exclude col_a from t_src.
最后解决办法,通过group_concat从information_schema中取t_dest的所列,排除col_a。具体做法是这样的:
SET @sql= (SELECT CONCAT('INSERT INTO ','test_b (',GROUP_CONCAT(COLUMN_NAME),') select ',GROUP_CONCAT(COLUMN_NAME),' from ', TABLE_NAME,' ;') FROM information_schema.COLUMNS WHERE table_name = 'test_a' AND TABLE_SCHEMA = 'test' and COLUMN_NAME != 'id');
PREPARE stmt from @sql;
EXECUTE stmt;
思路就是从column表中拿到test_a表中排除id之后的所有字段,然后组合一个sql语句,最后执行sql语句