将PivotTable与左外部联接一起使用
将PivotTable与左外部联接一起使用
提问于 2020-08-18 22:33:47
我对MySQL还是个新手。简而言之，我希望将每个"meta_key“值转换为结果表的一列。
我有两个具有以下模式的表：
CREATE TABLE `frmt_form_entry` (
`entry_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`entry_type` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`form_id` bigint(20) unsigned NOT NULL,
`is_spam` tinyint(1) NOT NULL DEFAULT '0',
`date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`entry_id`),
KEY `entry_is_spam` (`is_spam`),
KEY `entry_type` (`entry_type`),
KEY `entry_form_id` (`form_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
CREATE TABLE `frmt_form_entry_meta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`entry_id` bigint(20) unsigned NOT NULL,
`meta_key` varchar(191) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_520_ci,
`date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`meta_id`),
KEY `meta_key` (`meta_key`),
KEY `meta_entry_id` (`entry_id`),
KEY `meta_key_object` (`entry_id`,`meta_key`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;每个“表单条目”都有多个“元条目”：
INSERT INTO `frmt_form_entry` (`entry_id`, `entry_type`, `form_id`, `is_spam`, `date_created`)
VALUES
(1, 'custom-forms', 3744, 0, '2020-08-14 13:00:32');
INSERT INTO `frmt_form_entry_meta` (`meta_id`, `entry_id`, `meta_key`, `meta_value`, `date_created`, `date_updated`)
VALUES
(1, 1, 'text-7', 'Foreign Legal Form', '2020-08-14 13:00:32', '0000-00-00 00:00:00'),
(2, 1, 'name-1', 'Test Name', '2020-08-14 13:00:32', '0000-00-00 00:00:00'),
(3, 1, 'address-1', 'a:6:{s:7:\"country\";s:0:\"\";s:4:\"city\";s:0:\"\";s:5:\"state\";s:0:\"\";s:3:\"zip\";s:0:\"\";s:14:\"street_address\";s:0:\"\";s:12:\"address_line\";s:0:\"\";}', '2020-08-14 13:00:32', '0000-00-00 00:00:00'),
(4, 1, 'address-2', 'a:6:{s:7:\"country\";s:0:\"\";s:4:\"city\";s:0:\"\";s:5:\"state\";s:0:\"\";s:3:\"zip\";s:0:\"\";s:14:\"street_address\";s:0:\"\";s:12:\"address_line\";s:0:\"\";}', '2020-08-14 13:00:32', '0000-00-00 00:00:00'),
(5, 1, 'address-3', 'a:6:{s:7:\"country\";s:0:\"\";s:4:\"city\";s:0:\"\";s:5:\"state\";s:0:\"\";s:3:\"zip\";s:0:\"\";s:14:\"street_address\";s:0:\"\";s:12:\"address_line\";s:0:\"\";}', '2020-08-14 13:00:32', '0000-00-00 00:00:00'),
(6, 1, '_forminator_user_ip', '8.8.8.8', '2020-08-14 13:00:32', '0000-00-00 00:00:00'),
(7, 1, 'stripe-1', 'a:6:{s:4:\"mode\";s:4:\"test\";s:6:\"status\";s:7:\"success\";s:6:\"amount\";s:2:\"0\";s:8:\"currency\";s:3:\"\";s:14:\"transaction_id\";s:27:\"\";s:16:\"transaction_link\";s:70:\"\";}', '2020-08-14 13:00:32', '0000-00-00 00:00:00');我想创建一个透视表，其行数与frmt_form_entry表中的行数相同(在本例中为1)。
我希望每个元键都是该数据透视表的一个列名。因为每个元键在每个entry_id中只能存在一次，所以我不需要为相同的元键连接多个字段的元值。
这是预期的输出：
entry_id entry_type form_id is_spam date_created text-7 name-1 address-1 address-2 address-3. _forminator_user_ip stripe-1
1 custom-forms 3744 0 2020-08-14 13:00:32 Foreign Legal Form Test Name [...]到目前为止，我已经尝试了：
SELECT entries.*,
CASE WHEN meta.`meta_key`='stripe-1'
THEN meta.`meta_value`
ELSE NULL
END
AS stripe,
CASE WHEN meta.`meta_key`='text-7'
THEN meta.`meta_value`
ELSE NULL
END
AS text7,
CASE WHEN meta.`meta_key`='_forminator_user_ip'
THEN meta.`meta_value`
ELSE NULL
END
AS user_ip,
CASE WHEN meta.`meta_key`='address-1'
THEN meta.`meta_value`
ELSE NULL
END
AS address1,
CASE WHEN meta.`meta_key`='address-2'
THEN meta.`meta_value`
ELSE NULL
END
AS address2,
CASE WHEN meta.`meta_key`='address-3'
THEN meta.`meta_value`
ELSE NULL
END
AS address3,
CASE WHEN meta.`meta_key`='name-1'
THEN meta.`meta_value`
ELSE NULL
END
AS name1
FROM frmt_form_entry entries
LEFT OUTER JOIN frmt_form_entry_meta meta
ON entries.entry_id = meta.entry_id然而，这给人的感觉是错误和混乱的，它没有给出预期的结果(它为每个元值创建一个条目)。
有没有更简单、更好的方法将每个"meta_key“值转换为结果表中的一列？
回答 1
Stack Overflow用户
发布于 2020-08-19 00:57:51
抱歉，我要直截了当地说。"Entry-Attribute-Value“对于教科书来说是一个很好的模式模式，但在现实世界中就不是了。
对于存在于所有项的列，EAV尤其不合适。如果“每个”位置都有地址、城市等，则将它们列起来。更简单。效率更高。(在address_1和可选address_2的情况下，使用NULL表示可选性。)您是否曾经需要地址的各个部分来进行搜索或过滤？如果不是，那么只需为整个“地址”设置一个单独的列。
为通常不存在的属性保留"meta“。作为单独的讨论，我们可以讨论使用单个JSON列的优点/缺点。或者如何使用FULLTEXT进行搜索。
您真的需要date_created和date_updated吗
您在meta表上拥有的4个索引效率非常低。有关改进它的提示，请参阅以下内容：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63470880
复制相关文章
相似问题
腾讯云开发者
