如何从其他表填充表的外键 [英] How to populate a table's foreign keys from other tables

问题描述

我有以下表格，其中翻译为空，我要填写：

 翻译{
 id 
翻译
 language_id 
 template_id 
} 
 
语言{
 id 
 langname 
 langcode 
} 
 
模板{
 id 
 tplname 
源
 domain 
 total 
} 
  

要填写的源数据 / code>是我从外部CSV文件填充的临时表格：

  tmp_table {
 id 
 translated 
 langname 
 tplname 
 source 
 domain 
} 
  

我想要做的是用 tmp_table 的值填充翻译 $ c>。 翻译字段可以直接复制，但我不太确定如何获取正确的 language_id （tmp_table。 langname可用于确定language.id）和 template_id （tmp_table.tplname，tmp_table.source，tmp_table.domain一起可用于确定template.id）。

这可能是一个小问题，但我对SQL很陌生，不知道最好的查询应该填充翻译表。有任何想法吗？

解决方案

这可以简化为：

 code> INSERT INTO翻译（id，翻译，language_id，template_id）
 SELECT tmp.id，tmp.translated，l.id，t.id 
 FROM tmp_table tmp 
 JOIN语言l USING（langname）
 JOIN template t USING（tplname，source，domain）
 ORDER BY tmp.id 
  

我添加了一个 ORDER BY 子句，你不是严格需要，但是某些查询可能会获益，如果你插入你的数据聚集的（或一些其他）方式。

如果您想避免丢失在语言中找不到匹配行的行或模板，使 LEFT JOIN 而不是 JOIN 两个表（前提是 language_id 和 template_id 可以 NULL

除了我已在 prequel问题：如果INSERT是巨大的，并构成目标表的大部分，则可能更快地在目标表上 DROP所有索引，然后重新创建它们。

唯一索引还用作约束，因此您可以考虑是否稍后执行规则或将其留在原地。

I've got the following tables, of which translation is empty and I'm trying to fill:

translation {
    id
    translated
    language_id
    template_id
}

language {
    id
    langname
    langcode
}

template {
    id
    tplname
    source
    domain
    total
}

The source data to fill translation is a temporary table that I've populated from an external CSV file:

tmp_table {
    id
    translated
    langname
    tplname
    source
    domain
}

What I'd like to do is to fill translation with the values from tmp_table. The translated field can be copied directly, but I'm not quite sure how to fetch the right language_id (tmp_table.langname could be used to determine language.id) and template_id (tmp_table.tplname, tmp_table.source, tmp_table.domain together can be used to determine template.id).

It might be a trivial question, but I'm quite new to SQL and not sure what the best query should be to populate the translation table. Any ideas?

解决方案

This can be simplified to:

INSERT INTO translation (id, translated, language_id, template_id)
SELECT tmp.id, tmp.translated, l.id, t.id
FROM   tmp_table tmp
JOIN   language l USING (langname)
JOIN   template t USING (tplname, source, domain)
ORDER  BY tmp.id

I added an ORDER BY clause that you don't strictly need, but certain queries may profit if you insert your data clustered that (or some other) way.

If you want to avoid losing rows where you can't find a matching row in language or template, make it LEFT JOIN instead of JOIN for both tables (provided that language_id and template_id can be NULL.

In addition to what I already listed under the prequel question: If the INSERT is huge and constitutes a large proportion of the target table, it is probably faster to DROP all indexes on the target table and recreate them afterwards. Creating indexes from scratch is a lot faster then updating them incrementally for every row.

Unique indexes additionally serve as constraints, so you'll have to consider whether to enforce the rules later or leave them in place.

这篇关于如何从其他表填充表的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！