mysql - 从 wp_usermeta 表插入数据

Question

当 wp_usermeta 包含 3 列中的所有数据时，如何将 wp_usermeta 下面的数据插入到公司表中:

wp_usermeta的结构为:ID - user_id - meta_key - meta_value

Below user_id + meta_key + meta_value must insert into table company

meta_key = 'gender' -> gender
meta_key = 'first_name' -> first_name
meta_key = 'last_name'  -> last_name
meta_key = 'street_address' -> address
meta_key = 'zipcode' -> zipcode
meta_key = 'city' -> city
meta_key = 'country' -> country
meta_key = 'telephone' -> company_phone

不知道如何解决这个问题。

CREATE TABLE IF NOT EXISTS `wp_usermeta` (
  `umeta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`umeta_id`),
  KEY `user_id` (`user_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6026 ;

CREATE TABLE IF NOT EXISTS `wp_agent_companion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(15) NOT NULL,
  `commerce_number` varchar(32) DEFAULT NULL,
  `vat_number` varchar(32) DEFAULT NULL,
  `company_name` varchar(64) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `first_name` varchar(12) NOT NULL,
  `last_name` varchar(34) NOT NULL,
  `service` varchar(64) DEFAULT NULL,
  `address` varchar(64) DEFAULT NULL,
  `zipcode` varchar(12) DEFAULT NULL,
  `city` varchar(64) DEFAULT NULL,
  `country` varchar(64) DEFAULT NULL,
  `company_phone` varchar(24) DEFAULT NULL
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=93 ;

Answer 1

这个大查询是否会使您想要的表成为临时表？

INSERT INTO wp_agent_companion
(user_id, gender,
 first_name, last_name, address, zipcode,
 city, country, company_phone)
SELECT
    t1.user_id as user_id, 
    t1.meta_value as gender,
    t2.meta_value as first_name,
    t3.meta_value as last_name,
    t4.meta_value as address,
    t5.meta_value as zipcode,
    t6.meta_value as city,
    t7.meta_value as country,
    t8.meta_value as company_phone
 FROM
  (SELECT * FROM wp_usermeta) as t1
LEFT JOIN (SELECT * FROM wp_usermeta) as t2
ON t2.user_id = t1.user_id
LEFT JOIN (SELECT * FROM wp_usermeta) as t3
ON t3.user_id = t1.user_id
LEFT JOIN (SELECT * FROM wp_usermeta) as t4
ON t4.user_id = t1.user_id
LEFT JOIN (SELECT * FROM wp_usermeta) as t5
ON t5.user_id = t1.user_id
LEFT JOIN (SELECT * FROM wp_usermeta) as t6
ON t6.user_id = t1.user_id
LEFT JOIN (SELECT * FROM wp_usermeta) as t7
ON t7.user_id = t1.user_id
LEFT JOIN (SELECT * FROM wp_usermeta) as t8
ON t8.user_id = t1.user_id
WHERE
    t1.meta_key = 'gender' AND
    t2.meta_key = 'first_name' AND
    t3.meta_key = 'last_name' AND
    t4.meta_key = 'street_address' AND
    t5.meta_key = 'zipcode' AND
    t6.meta_key = 'city' AND
    t7.meta_key = 'country' AND
    t8.meta_key = 'telephone'

您只需使用您熟悉的 INSERT INTO SELECT (..) 将它们插入到 company 表中即可。

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

About INSERT SELECT