有没有一种好的方法可以将MySQL中的子元素作为数组获取?

huangapple go评论72阅读模式
英文:

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 在这里提到了代码异味,我只想澄清一下这段代码的总体意图。我将逐步介绍它的高级功能。

  1. 从列表中选择所有公司
  2. 获取每个公司的所有员工
  3. 向用户显示公司列表,并在每个公司旁边显示一个可滚动的员工列表

我理解,如 @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.

  1. Select all companies from a list
  2. Get all employees of each company
  3. 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

这里有几个微妙不同的问题。

第一个问题是:

  1. 有没有一种好的方法将MySQL中的子项作为数组获取?

答案:可能有一种方法,但不能说是“好”的。

第二个问题是:

  1. 是否有任何可以查询一份企业列表,并将每个企业的员工作为一个字段附加的方式?

答案:在规范化的环境中,企业列表中附带“员工数组字段”是不可想象的。

第三个问题可能是:

  1. 在规范化的环境中,我如何将企业列表导入服务器,并为每个给定的企业获取该企业的所有员工列表?

答案:这是一个简单的练习,通常使用JOIN操作。如需进一步帮助,请参阅https://stackoverflow.com/help/minimal-reproducible-example。

英文:

There are several subtly different questions here.

The first is:

  1. 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:

  1. 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:

  1. 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.

huangapple
  • 本文由 发表于 2021年7月24日 04:00:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/68504512.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定