英文:
Is there a good way to get children in MySQL as an array?
问题
首先,如果这是一个重复的问题,我表示抱歉,我不太了解这个问题的最佳术语。
假设我有一个表示的 SQL 模式:
Businesses:
id: int
name: string
Employees:
id: int
business_id: int REFERENCES Businesses(id)
name: string
是否有任何可行的方法可以查询一个包含员工数组字段的企业列表?目前,我将企业列表拉入我的服务器,然后对于数组中的每个企业,获取所有 business_id 等于其 id 的员工。这种方法可以工作,但对于大量的企业,会导致对数据库的大量查询。
我考虑的另一个选项是将所有员工读入服务器内存中,将它们放入一个 [business_id-> [employees]]
的映射中,然后将员工数组发送到服务器上正确的企业的 employees
字段。这似乎是一个更高效的选项,但我想知道 MySQL 中是否有任何内置的功能可以直接实现这一点,因为这似乎是一个非常常见的任务。谢谢!
编辑:
@MatBailie 在这里提到了代码异味,我只想澄清一下这段代码的总体意图。我将逐步介绍它的高级功能。
- 从列表中选择所有公司
- 获取每个公司的所有员工
- 向用户显示公司列表,并在每个公司旁边显示一个可滚动的员工列表
我理解,如 @radocaw 指出的那样,获取一个企业的员工列表是一个简单的连接操作,但我的问题是这将导致多次访问数据库。我将在下面用 Golang 发布解决此问题的当前方法,使用两个简单的 MySQL SELECT 语句和映射的使用。这是给你的,@Tangentially Perpendicular!
employeeMap := make(map[int][]Employee)
employees,err := db.Query("SELECT business_id,f_name,l_name,email FROM employees")
if err != nil {
//处理错误
}
for employees.Next(){
var employee Employee
err = employees.Scan(&employee.BusinessId,&employee.FName,&employee.LName,&employee.Email)
if err != nil {
//处理错误
continue
}
if _,ok := employeeMap[employee.BusinessId];ok{
employeeMap[employee.BusinessId] = append(employeeMap[employee.BusinessId],employee)
}else {
employeeMap[employee.BusinessId] = make([]Employee,1)
employeeMap[employee.BusinessId[0] = employee
}
}
这用于构建一个企业 ID 到员工列表的映射。接下来,我只需从数据库中获取企业,并将员工数组附加到它们的 ID 上。
我不确定这是否是一个好的方法,但它似乎比对数据库进行 n 次连接更优化和直接,其中 n 是企业的数量。感谢大家提供的反馈和高质量的答案/评论!
英文:
First of all, apologies if this is a duplicate, I don't really know the best terminology for this question.
Say I have SQL schema that represents:
Businesses:
id: int
name: string
Employees:
id: int
business_id: int REFERENCES Businesses(id)
name: string
Is there any conceivable way I could query for a list of businesses each with an array of employees attached as a field? Currently I am pulling the list of businesses into my server, and then for each business in the array, getting all employees who have a business_id equal to its id. This works, but for a large number of businesses, it results in a high number of queries to the database.
Another option I considered was reading all employees into memory on my server, putting them into a map of [business_id->[employees]]
, and then sending the employees array to the right businesses employees
field on the server. This seems to be a much more performant option, but I wanted to know if there is anything built into MySQL that would allow me to do this out of the box, as it seems like a pretty common task. Thank you!
EDIT:
@MatBailie made a point about code smells here, and I just wanted to clarify the total intent of this code. I will add a step by step walkthrough of what it would do from a high level.
- Select all companies from a list
- Get all employees of each company
- Display to a user a list of companies, and next to each one, a scrollable list of each employee
I understand, as @radocaw pointed out, that getting a list of employees for a business is a trivial join, but my issue is that this will take numerous trips to the database. I will post my current method of solving this issue in Golang below, using two simple SELECTs from MySQL and the use of maps. This is for you, @Tangentially Perpendicular!
employeeMap := make(map[int][]Employee)
employees,err := db.Query("SELECT business_id,f_name,l_name,email FROM employees")
if err != nil {
//handle error
}
for employees.Next(){
var employee Employee
err = employees.Scan(&employee.BusinessId,&employee.FName,&employee.LName,&employee.Email)
if err != nil {
//handle error
continue
}
if _,ok := employeeMap[employee.BusinessId];ok{
employeeMap[employee.BusinessId] = append(employeeMap[employee.BusinessId],employee)
}else {
employeeMap[employee.BusinessId] = make([]Employee,1)
employeeMap[employee.BusinessId[0] = employee
}
}
This is used to build a map of business ids to lists of employees. Next I would just get the businesses from the database, and for their id, attach that array of employees to them.
I'm not sure if this is even a good way to do this, but it seems more optimized and direct than hitting the database with n joins, where n is the number of businesses. Thanks everyone for the great feedback and high quality answers/comments!
答案1
得分: 1
这里有几个微妙不同的问题。
第一个问题是:
- 有没有一种好的方法将MySQL中的子项作为数组获取?
答案:可能有一种方法,但不能说是“好”的。
第二个问题是:
- 是否有任何可以查询一份企业列表,并将每个企业的员工作为一个字段附加的方式?
答案:在规范化的环境中,企业列表中附带“员工数组字段”是不可想象的。
第三个问题可能是:
- 在规范化的环境中,我如何将企业列表导入服务器,并为每个给定的企业获取该企业的所有员工列表?
答案:这是一个简单的练习,通常使用JOIN操作。如需进一步帮助,请参阅https://stackoverflow.com/help/minimal-reproducible-example。
英文:
There are several subtly different questions here.
The first is:
- Is there a good way to get children in MySQL as an array?
Answer: There may be a way - but it cannot be 'good'.
The second is:
- Is there any conceivable way I could query for a list of businesses each with an array of employees attached as a field?
Answer: In a normalised environment, it's inconceivable that a list of businesses would have 'an array of employees attached as a field'
The third might be:
- In a normalised environment, how might I pull the a list of businesses into my server and then, for each given business, obtain a list of all employees of that business.
Answer: This is a trivial exercise, normally employing JOINs. For further help, see https://stackoverflow.com/help/minimal-reproducible-example
答案2
得分: 0
你可以通过使用group_concat返回一个CSV列表来实现类似的效果:
SELECT b.id, b.name, group_concat(e.id) employee_ids, group_concat(e.name) employee_names
FROM businesses b
LEFT JOIN employees e ON b.id = e.business_id
GROUP BY b.id, b.name
然后在代码中将这些值拆分为数组。
英文:
You can achieve something close by returning a CSV list using group_concat:
SELECT b.id,b.name,group_concat(e.id) employee_ids, group_concat(e.name) employee_names
FROM businesses b
LEFT JOIN employees e on b.id = e.business_id
GROUP BY b.id,b.name
And then subsequently splitting the values into arrays in code.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论