英文:
In Java, mutiple select queries or join, which one would be a better practice?
问题
我在MySQL数据库中有五个表，每个表与存储用户名和密码的User表具有一对一关系，在User表中有一个user_id字段，作为所有其他表的外键。
如果我需要使用Java实现一个“登录”功能，以一次性从这五个表中获取某个用户的所有数据，是否使用JOIN一次性查询这五个表是一个好的做法？
我还有另一个问题，将这五个表合并为一个单独的表，是否是一个更好的选择，因为它们之间是一对一的关系？
英文:
I have five tables in MySQL database, each of them has the one-to-one relationship with the User table that stores the username and password, there is a user_id field in User table as the foreign key for all the other tables.
If I need to implement a "Login" function with Java, to get all data of a certain user from all these five tables, would it be a good practice to use JOIN to query these five tables at once?
I also have another question, would it be a better choice to combine these five tables together as one single table as they have one-to-one relationship?
答案1
得分: 0
作为一般原则，1对1的关系应该在同一张表中。
例外情况可以包括：
- 权限分离 - MySQL用户具有不同的表级访问权限。
- 锁定分离 - 一个表作为一个特别高更新字段，而另一个表需要与高用户ID更新分开。
- 性能 - 行大小 - 一个表具有大量不经常需要的列，因此将这些列保持在不同的表中，以避免进入InnoDB缓冲池。
英文:
As general principle, 1 to 1 relationships should be in the same table.
Exceptions can include:
- privilege separation - mysql users have different table level access.
- locking separation, one table as a particularly high updated field than for a userid that needs to be separate from another table of
high userid updates. - performance - row size - one table has large number of columns that isn't frequently needed, so keep it out of the innodb buffer pool by
keeping those columns in a different table.
答案2
得分: 0
创建具有外键引用的多个表的背后思想是支持一对多的关系。由于您的表数据具有一对一的关系，如果包括所有具有一对一关系的表在内的列数少于MySQL表列数限制，那么创建多个表就没有意义。
如果您的业务需求不允许更改当前模式，您可以进行连接操作，通过单个网络调用获取所有数据，而不是进行多次网络调用来获取数据，因为网络调用延迟是不可预测的。
英文:
Idea behind creating multiple tables with foreign key reference is to support one-to-many relationship. Since your table data has one-to-one relationship, there is no point creating multiple table if number of columns including all tables, having one-to-one relationship, is less than Mysql table column count limit.
Incase your business requirement doesn't allow to change the current schema, you can go for join to get all the data in single network call rather than making multiple network call to get data as network call latency is not predictable.
专注分享java语言的经验与见解，让所有开发者获益！
评论