php - 批量插入 EAV 表示例

Question

请帮助我将数据插入到此 EAV 实现中。

我完全意识到 EAV 表的缺点、性能缺点以及更难维护 sql 查询，但当前系统已经使用它并且更改它不是一个选项。

系统使用 EAV 表来存储来自各种源的临时元数据，这些源“填充”实体的属性和值，当实体完成时，它会导出到适当的表中。

我很想为此使用 NoSQL 数据库，但正如我已经提到的，更改现有系统不是一个选项:(

但我实际上想问的是，是否有任何关于如何将属性插入到 EAV 表中的示例，我在网上找不到任何示例(我看到的都是关于 EAV 有多邪恶的文章，或者 EAV 是如果使用得当或有关 Magento 的文章不是邪恶的)。

我将给出一个简化的示例，您可以复制并粘贴我尝试插入这些表的方式。

注意:数据大多是一遍又一遍相同的字符串，但每个“实体”有许多不同的组合，它还使用 php 进行实际插入

注释 2:问题变得很有趣，因为要保存的大量信息通常非常大，并且插入必须高效。

准备复制的代码:

<?php
try {
    $user = 'root';
    $pass = '000000';
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
    exit('Failed to open database: '.$e->getMessage() );
}

#the entity
$dbh->exec('
CREATE TEMPORARY TABLE IF NOT EXISTS `tbl_entity` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100),
`created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
');

#the value "type" or attribute
$dbh->exec('
CREATE TEMPORARY TABLE IF NOT EXISTS `tbl_attribute` (
`attr_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) UNIQUE,
`type` TINYINT(3),
PRIMARY KEY(`attr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
');

#the table that holds the values, there is a "mostly" limited set of values and storing each one again and again makes no sense.
$dbh->exec('
CREATE TEMPORARY TABLE IF NOT EXISTS `tbl_value` (
`val_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`value` VARCHAR(255) UNIQUE,
PRIMARY KEY(`val_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
');

#the table that holds the references of the values and their attributes
$dbh->exec('
CREATE TEMPORARY TABLE IF NOT EXISTS `tbl_ref` (
`ent_id` INT(11) UNSIGNED NOT NULL,
`attr_id` INT(11) UNSIGNED NOT NULL,
`val_id` INT(11) UNSIGNED NOT NULL,
PRIMARY KEY(`ent_id`,`attr_id`,`val_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
');

$input_rows = array(
    array(
        'item'=>'Item 1',
        'attr'=>'title',
        'val'=>'Item 1 title',
    ),
    array(
        'item'=>'Item 1',
        'attr'=>'code',
        'val'=>'983474',
    ),
    array(
        'item'=>'Item 1',
        'attr'=>'tag',
        'val'=>'Tag A',
    ),
    array(
        'item'=>'Item 1',
        'attr'=>'tag',
        'val'=>'Tag B',
    ),
    array(
        'item'=>'Item 2',
        'attr'=>'tag',
        'val'=>'Tag B',
    ),
    //... and so forth
);

//Build queries for values and attributes
$attr_values = array();
$attr_params = array();
$val_values = array();
$val_params = array();
foreach($input_rows as $row){
    $attr_values[] = '?';
    $attr_params[] = $row['attr'];
    $val_values[] = '?';
    $val_params[] = $row['val'];
}

//Insert the attributes and values, many might already exist in the db
if(!empty($attr_values)){
    $sql = 'INSERT IGNORE INTO `tbl_attribute` (`name`) VALUES ('.implode('),(',$attr_values).')';
    $stmt = $dbh->prepare($sql);
    $stmt->execute($attr_params);
}
if(!empty($val_values)){
    $sql = 'INSERT IGNORE INTO `tbl_value` (`value`) VALUES ('.implode('),(',$val_values).')';
    $stmt = $dbh->prepare($sql);
    $stmt->execute($val_params);
}

//Select the ID's of the values and attributes
$attr_ids = array();
$stmt = $dbh->prepare('SELECT `attr_id`,`name` FROM `tbl_attribute` WHERE `name` IN('.implode(',',$attr_values).')');
$stmt->execute($attr_params);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $attr_ids[$row['name']] = $row['attr_id'];
}
$val_ids = array();
$stmt = $dbh->prepare('SELECT `val_id`,`value` FROM `tbl_value` WHERE `value` IN('.implode(',',$val_values).')');
$stmt->execute($val_params);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $val_ids[$row['value']] = $row['val_id'];
}

//Insert the entities and references
$dbh->beginTransaction();
foreach($input_rows as $row){
    //One of my gripes with this approach is that I have to "replace into" instead of "insert into" so that the lastInsertId returns an id even if there is some constraint
    $sql = 'REPLACE INTO `tbl_entity` (`name`) VALUES (?)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($row['item']));
    $id = $dbh->lastInsertId();

    $sql = 'INSERT IGNORE INTO `tbl_ref` (`ent_id`,`attr_id`,`val_id`) VALUES (?,?,?)';
    $stmt = $dbh->prepare($sql);
    $val_id = $val_ids[$row['val']];
    $attr_id = $attr_ids[$row['attr']];
    $stmt->execute(array($id,$attr_id,$val_id));
}
$dbh->commit();

//Select data (selecting all columns only for this example)
$data = array();
$stmt = $dbh->prepare('SELECT `id`,`name`,`created` FROM `tbl_entity`');
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $data[$row['id']] = $row;
}
$stmt = $dbh->prepare('
SELECT 
x.`ent_id` AS `item_id`,
a.`name` AS `attr`,
v.`value` AS `val`
FROM `tbl_ref` x
INNER JOIN `tbl_attribute` a USING(`attr_id`)
INNER JOIN `tbl_value` v USING(`val_id`)
');
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $data[$row['item_id']]['meta'][$row['attr']][] = $row['val'];
}
echo '<plaintext>';
print_r($data);

如果您发现任何其他问题，请尽可能提及。我也很高兴听到您关于如何正确解决此问题的意见，即使我现在无法实现它，它以后可能对我或其他人有用。

附注我不确定这是否更适合 stackexchange 或 stackoverflow 的代码审查分支，我现在是 50%/50%，所以在这里发帖，因为有更多人，如果我错了，那么我该如何移动/迁移这个问题？

P.P.S。我花了很多时间研究这个问题，花了3个小时才写这个问题，请回答!

Answer 1

通常(咳)，EAV 表通常看起来更像这样。

create table eav (
  entity varchar(100) not null,
  attribute varchar(100) not null,
  value varchar(255) not null,
  primary key (entity, attribute, value)
);

这种结构有助于简单有效地使用 MySQL's bulk loader 。我希望输入是 CSV 文件。

"Item 1", "title", "Item 1 title"
"Item 1", "code", "983474"
"Item 1", "tag", "Tag A"
"Item 1", "tag", "Tag B"
"Item 2", "tag", "Tag B"

但是使用 SQL 语句插入数据同样简单。

insert into eav values ('Item 1', 'title', 'Item 1 title');
insert into eav values ('Item 1', 'code', '983474');
...

如果您打算对所有这些值使用代理 ID 号，那么您的工作就会困难得多，效率也会低得多。您只有几个选择。

• 在导入之前将值转换为 ID 号。
• 将其导入临时表，确定 ID 号，然后插入数据。