mysql - 通过 MySQL 查询复制值 (phpMyAdmin)

Question

我有 Wordpress + Woocommerce 网站。我需要为其他网站导出 .xml。

在 post_meta 表中有字段:_skuwoocommerce_xml_disabledwoocommerce_xml_EAN

我需要构建这个查询:

if '_sku' not empty
copy value from '_sku' to 'woocommerce_xml_EAN'
if '_sku' is empty
set 'woocommerce_xml_disabled' value to '1'.

客户使用 SKU 字段放置 EAN 代码，有些产品具有相同的 EAN。此字段不接受重复项，因此在某些情况下，值类似于“9001616391101 (1)”、“9001616391101 (2)”。如果查询可以在复制后删除那些 (1) 数字，那就太好了。 'woocommerce_xml_EAN' 可以有重复的 EAN。

这会节省我很多工作。

这是一个小的 SQL 示例:

(181195, 14947, 'cmsms_breadcrumbs', 'default'),  
(181196, 14947, 'cmsms_custom_breadcrumbs', 'a:1:{i:0;a:2:{i:0;s:0:"";i:1;s:0:"";}}'),  
(181198, 14947, 'woocommerce_xml_disabled', ''),  
(181199, 14947, 'woocommerce_xml_alternative_name', ''),  
(181200, 14947, 'woocommerce_xml_alternative_desc', ''),  
(181201, 14947, 'woocommerce_xml_group', ''),  
(181202, 14947, '_woocommerce_gpf_data', 'a:0:{}'),  
(181217, 14946, '_thumbnail_id', '9874'),  
(181218, 14946, 'total_sales', '0'),

单品样本数据:

CREATE TABLE IF NOT EXISTS `wp_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=185004 ;

INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(173877, 14819, '_visibility', 'visible'),
(173878, 14819, '_stock_status', 'instock'),
(173879, 14819, '_downloadable', 'no'),
(173880, 14819, '_virtual', 'no'),
(173881, 14819, '_regular_price', '13.9'),
(173882, 14819, '_sale_price', ''),
(173883, 14819, '_purchase_note', ''),
(173884, 14819, '_featured', 'no'),
(173889, 14819, '_sku', '9001616391101 (5)'),
(173893, 14819, '_price', '13.9'),
(173919, 14819, 'woocommerce_xml_disabled', '1'),
(173923, 14819, '_woocommerce_gpf_data', 'a:0:{}'),
(173938, 14819, '_thumbnail_id', '10851'),
(173939, 14819, 'total_sales', '0'),
(182118, 14819, 'woocommerce_xml_EAN', '9001616391101')

Answer 1

您在这里有复杂的要求，wp_postmeta 表的键/值结构使它变得更加困难。让我们分两步解决它。第一个是将 woocommerce_xml_EAN 字段已存在的行更新为相应的 _sku 值，同时删除括号中的 (1) 值using SUBSTRING_INDEX() .在此步骤中，如果 _sku 为空，woocommerce_xml_disabled 也会设置为 1。

这一切都将通过将 wp_postmeta 加入自身来完成。在连接的一侧，使用 _sku，在另一侧，它与 post_id 匹配以更新 woocommerce_xml_EAN、woocommerce_xml_disabled 字段。

UPDATE
  wp_postmeta sku
  INNER JOIN wp_postmeta ean 
    ON sku.post_id = ean.post_id
    -- Join conditions ensure only the needed keys are modified
    AND sku.meta_key = '_sku'
    AND ean.meta_key IN ('woocommerce_xml_EAN', 'woocommerce_xml_disabled')
 SET ean.meta_value = CASE
   -- the key/value store is crazy here :-)
   -- First strip the (1) from the _sku and set the 
   -- resultant value into meta_value for the woocommerce_xml_EAN key
   WHEN ean.meta_key = 'woocommerce_xml_EAN'
     THEN TRIM(SUBSTRING_INDEX(sku.meta_value, '(', 1)) 
   -- Then set '1' for the disabled field if _sku is empty
   WHEN ean.meta_key = 'woocommerce_xml_disabled' AND sku.meta_value = ''
     THEN '1'
   -- But make sure to use the original value if sku wasn't empty!
   WHEN ean.meta_key = 'woocommerce_xml_disabled' AND sku.meta_value <> ''
     THEN ean.meta_value
   END
 WHERE sku.meta_key = '_sku'
   AND ean.meta_key IN ('woocommerce_xml_EAN', 'woocommerce_xml_disabled');

现在是第二阶段，因为某些 _sku 可能没有关联的 woocommerce_xml_EAN，因此需要添加一个 INSERT。我们将使用 INSERT INTO...SELECT 和 null LEFT JOIN 来查找那些尚不存在的内容，并插入它们。它使用相同的 SUBSTRING_INDEX() 技巧。

 INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
   SELECT
     -- Insert a new row with the post_id,
     sku.post_id,
     -- literal string for the EAN
     'woocommerce_xml_EAN',
     -- And the modified _sku value
     TRIM(SUBSTRING_INDEX(sku.meta_value, '(', 1))
   FROM
     wp_postmeta sku
     LEFT JOIN wp_postmeta ean
     ON sku.post_id = ean.post_id
      AND sku.meta_key = '_sku'
      AND ean.meta_key = 'woocommerce_xml_EAN'
    WHERE
      sku.meta_key = '_sku'
      -- NULL on the ean side of the join means it doesn't exist already
      AND ean.meta_id IS NULL;

这里有一个演示来证明这一切确实有效。 http://sqlfiddle.com/#!9/30b84/2

在演示中，我添加了两个额外的 post_id，它们没有 EAN 的先前值，以验证是否为它们创建了 woocommerce_xml_EAN。它们的 post_id 是 14820, 14821，您会在结果集的底部找到它们。