时间戳

首页 > 解决方案 > MySQL，使用图像图片将数据从行移动到列

mysql - MySQL，使用图像图片将数据从行移动到列

问题描述

表格1

id  id_customer name_photo1   photo_1      name_photo2  photo_2 
1.  1           pic.png       *sdhsadj     jsjs.png     *ssksksks
2.  2           pic2.png      *sadjsad     sdds.png     *ssddsdsdw

到表2

id.  id_customer  namefoto    picture
1.   1            pic.png     *sdhsadj 
2    1            jsjs.png    *ssksksks
3.   2            pic2.png    *sadjsad 
4.   2            sdds.png    *ssddsdsdw

由于我可以通过查询将数据从表移动到表 2，因此 photo_1 和 photo_2 列是 longblob。

谢谢

标签： mysql

解决方案

您需要“反透视”才能将列转换为记录。
您可以在一个查询INSERT INTO .. SELECT中结合使用UNION ALL

INSERT INTO 
 table2
(
    table2.id_customer 
  , table2.namefoto
  , table2.picture
)
SELECT 
   records.id_customer 
 , records.namefoto
 , records.picture
FROM (
  SELECT 
     table1.id_customer AS id_customer 
   , table1.name_photo1 AS namefoto
   , table1.photo_1 AS picture  
  FROM 
   table1

  UNION ALL 

  SELECT 
     table1.id_customer AS id_customer 
   , table1.name_photo2 AS namefoto
   , table1.photo_2 AS picture  
  FROM 
   table1
) AS records
ORDER BY
  records.customer_id ASC

推荐阅读