MySQL自动插入一行到table2中，插入到table1中 [英] MySQL auto insert a row into table2 on an insert to table1

问题描述

在MySQL中是否可以在将行插入到table1中时自动将行插入到table2中?

Is it possible in MySQL to insert a row into table2 automatically when a row is inserted into table1?

说我具有以下基本结构:

Say I have the following basic structure:

用户表

id           int(8)       - primary key       - auto increment
username     varchar(20)

用户隐私表-userprivacy.userId是user.id

id           int(8)       - primary key       - auto increment
userId       int(8)       - foreign key

在运行诸如INSERT INTO user (username) VALUES ('Bob');

除了触发器之外，还有其他方法吗?还是最好在用户插入user时只运行两个查询?

Is there a way other than triggers or would it be best to just run two queries whenever a user is inserted into user?

第二，如果它为每个用户存储值，那么拥有一个单独的userprivacy表会浪费存储空间吗?可能更有效地将其放置在用户表中?但我正在尝试使它尽可能轻..

Secondly is it a waste of storage to have a separate userprivacy table if it's storing a value for every user? could be more efficient to place it in the user table? but I'm trying to make that as light as possible ..

user表中有更多列，在此示例中，我只是为了使其简单

there are more columns in the user table I'm just keeping it simple for this example

推荐答案

您需要触发器才能自动"将记录插入单独的表中.

You would require triggers in order to "automatically" insert the record into a separate table.

使用单独的查询将记录插入UserPrivacy将是实现此目的的最常见方法.

Inserting the record into UserPrivacy with a separate query would be the most common way of implementing this.

如果是一对一关系，则将其放在同一表中可以进行更简单的查询(无需连接).

If it's a one-to-one relationship, putting it in the same table allows more simple queries (no join required).

决定，决定...

对于一对零(零对一)的关系，还有更多的因素需要考虑.

With a one-to-(zero to one) relationship, there are more factors to consider.

如果UserPrivacy表很大，则可以将其放在单独的表中以节省空间.这也将更加规范化.

If the UserPrivacy table is large, then it might make sense to put it in a separate table to save space. This would also be more normalized.

如果您经常查询类似给我所有没有PrivacyData的用户"之类的信息，则可以将其放在单独的表中.由于索引不包含NULL值(默认情况下)，因此对单独的表执行JOIN操作会更快.当然，解决方法是使用非NULL的值表示无隐私设置"，但是低基数也会对性能产生负面影响.在这种情况下，最好使用单独的表.

If you were often querying something like "Give me all the Users that don't have PrivacyData", then it might make sense to put it in a separate table. Since indexes don't include NULL values (by default), it would be faster to do a JOIN to a separate table. Of course, the work around is to use a value other than NULL to represent "no privacy settings", but low cardinality also negatively affects performance. Separate tables would be best for this case.

此外，如果隐私数据(而不是用户数据)经常更新，则单独的表将防止在用户表上进行行锁定，并且在较小的表上更新速度更快，这可能会提高性能.

Also, if the privacy data was updated frequently, but not the User data, separate tables would prevent row locks on the User table, and updates are faster on smaller tables, which might improve performance.

如果您经常需要不带UserData的UserPrivacy数据，反之亦然，则可能需要将它们分开.

If you often needed the UserPrivacy data without the UserData, or vice versa, you might want to separate them.

仍然，这可能是过早的优化.如果它们与您的模型更好地匹配，您可能只想将它们分开.考虑将其保存在同一张表中的简单性以及性能，大小和可读性等问题.

Still, this might be premature optimization. You might just want to separate them if they better match your models. Consider the simplicity of keeping it in the same table versus the issues of performance, size, and readability.

如果关系是一对一(零对多)，显然您需要一个单独的表，但是对于一对一(零对一)，它是可选的.

If the relationship was one-to-(zero to many), you would obviously want a separate table, but for one-to-(zero to one), it's optional.

最后...

只要有理由，不要害怕将它们分开.

Don't be afraid to separate them, as long as there's a reason.

这篇关于MySQL自动插入一行到table2中，插入到table1中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！