I was wondering whether or not storing an array as a JSON string in a mysql text field is good practice.
我想知道在mysql文本字段中将数组存储为JSON字符串是否是个好习惯。
I am creating an invoice which allows the user to add an unlimited number of products to the invoice. When the form is submitted, it takes out all the blank items and such, but I will typically be left with 2-5 items depending. Each item has a sku, price, name, and description.
我正在创建一个允许用户向发票中添加无限数量产品的发票。当表单被提交时，它会取出所有的空白项等等，但是我通常只剩下2-5个项。每个项目都有sku、价格、名称和描述。
My options for this situation are (1) to create a new product table, add each item as a new row, link it with the invoice table, and call both tables when accessing data. Or (2) store all the product data as a single JSON text field in the invoice table, and then I am not creating or accessing another table.
对于这种情况，我的选项是(1)创建一个新产品表，将每个项添加为新行，将其与invoice表链接，并在访问数据时调用这两个表。或者(2)将所有的产品数据作为一个JSON文本字段存储在invoice表中，然后我不再创建或访问另一个表。
Since I am pretty rigid with MySQL programming, I get the feeling that using JSON in MySQL would be frowned upon. Am I right? Can someone shed some light on this?
因为我对MySQL编程非常严格，所以我觉得在MySQL中使用JSON会让人不悦。我说的对吗?有人能解释一下吗?
4 个解决方案
#1
14
If all you need is just to store - then it is not a bad practice.
如果你所需要的只是储存，那么这不是一个坏习惯。
But if you need to perform any sort of processing, sorting or something similar - you need to normalize it.
但是，如果您需要执行任何类型的处理、排序或类似的操作，您需要将其规范化。
#2
6
Because others have answered your question more directly, I'm going to take a fringe approach and address future maintainability instead.
因为其他人更直接地回答了您的问题，所以我将采取一种附加的方法，并解决未来的可维护性。
Storing a variable number of items that are just begging to be a database entity (SKU, Price, Name, Description) as JSON may be fine now, but it's going to lead to a ton of data duplication.
将数量可变的项目存储为一个数据库实体(SKU、Price、Name、Description)作为JSON可能没问题，但这将导致大量数据重复。
Instead, do what you said and create a table for all the products. Then create another table for invoices_have_products. You can then pull every row from invoices_have_products where the invoice ID matches, and then pull every row from products where the product ID matches the rows you pulled from invoices_have_products.
相反，按照您说的做，为所有产品创建一个表。然后为invoices_have_products创建另一个表。然后，您可以从invoices_have_products中提取发票ID匹配的每一行，然后从产品ID匹配从invoices_have_products中提取的每一行。
It might get a little tedious right now, but when all your data is in neat tables and easily queryable, you'll be much happier. Think about the nightmare of running reports on millions of text fields with JSON. Absolutely horrifying.
现在可能会有点乏味，但是当所有数据都在整洁的表中并且很容易查询时，您将会更快乐。考虑一下使用JSON在数百万文本字段上运行报表的噩梦。绝对恐怖。
To answer a part of your question: No, I don't think this is good practice and it looks a little bit like bad practice, to be honest.
回答你问题的一部分:不，我不认为这是一个好的实践，说实话，它看起来有点像一个坏的实践。
#3
0
I'm no MySQL expert, but I think it depends on what you want to accomplish. Do you want the products which you have saved to an invoice to be searchable? If so, you are better off going with a relational database structure.
我不是MySQL专家，但我认为这取决于你想要完成什么。您是否希望将保存到发票中的产品进行搜索?如果是，最好使用关系数据库结构。
If you don't have the need to search, you could store the data as a JSON string, or simply as a serialized array, and save yourself from using JSON at all.
如果您不需要搜索，您可以将数据存储为JSON字符串，或者简单地将其作为序列化的数组存储，并避免使用JSON。
Depends on your needs really.
这完全取决于你的需要。
#4
0
If you store it in JSON you won't be able to query it using SQL. For example I cannot find out all the invoices which bought more than 3 of Product A.
如果您将它存储在JSON中，您将无法使用SQL查询它。例如，我找不到所有购买超过3个A产品的发票。
#1
14
If all you need is just to store - then it is not a bad practice.
如果你所需要的只是储存，那么这不是一个坏习惯。
But if you need to perform any sort of processing, sorting or something similar - you need to normalize it.
但是，如果您需要执行任何类型的处理、排序或类似的操作，您需要将其规范化。
#2
6
Because others have answered your question more directly, I'm going to take a fringe approach and address future maintainability instead.
因为其他人更直接地回答了您的问题，所以我将采取一种附加的方法，并解决未来的可维护性。
Storing a variable number of items that are just begging to be a database entity (SKU, Price, Name, Description) as JSON may be fine now, but it's going to lead to a ton of data duplication.
将数量可变的项目存储为一个数据库实体(SKU、Price、Name、Description)作为JSON可能没问题，但这将导致大量数据重复。
Instead, do what you said and create a table for all the products. Then create another table for invoices_have_products. You can then pull every row from invoices_have_products where the invoice ID matches, and then pull every row from products where the product ID matches the rows you pulled from invoices_have_products.
相反，按照您说的做，为所有产品创建一个表。然后为invoices_have_products创建另一个表。然后，您可以从invoices_have_products中提取发票ID匹配的每一行，然后从产品ID匹配从invoices_have_products中提取的每一行。
It might get a little tedious right now, but when all your data is in neat tables and easily queryable, you'll be much happier. Think about the nightmare of running reports on millions of text fields with JSON. Absolutely horrifying.
现在可能会有点乏味，但是当所有数据都在整洁的表中并且很容易查询时，您将会更快乐。考虑一下使用JSON在数百万文本字段上运行报表的噩梦。绝对恐怖。
To answer a part of your question: No, I don't think this is good practice and it looks a little bit like bad practice, to be honest.
回答你问题的一部分:不，我不认为这是一个好的实践，说实话，它看起来有点像一个坏的实践。
#3
0
I'm no MySQL expert, but I think it depends on what you want to accomplish. Do you want the products which you have saved to an invoice to be searchable? If so, you are better off going with a relational database structure.
我不是MySQL专家，但我认为这取决于你想要完成什么。您是否希望将保存到发票中的产品进行搜索?如果是，最好使用关系数据库结构。
If you don't have the need to search, you could store the data as a JSON string, or simply as a serialized array, and save yourself from using JSON at all.
如果您不需要搜索，您可以将数据存储为JSON字符串，或者简单地将其作为序列化的数组存储，并避免使用JSON。
Depends on your needs really.
这完全取决于你的需要。
#4
0
If you store it in JSON you won't be able to query it using SQL. For example I cannot find out all the invoices which bought more than 3 of Product A.
如果您将它存储在JSON中，您将无法使用SQL查询它。例如，我找不到所有购买超过3个A产品的发票。