在 MySQL 数据库中存储 XML 数据的最佳方法，有一些特定要求
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7009538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to store XML data in a MySQL database, with some specific requirements
提问by Hyman
I am receiving XML data from a service. The test data I am receiving back has about 300 XML nodes, clearly far too many to create individual rows for in a MySQL database.
我正在从服务接收 XML 数据。我收到的测试数据有大约 300 个 XML 节点，显然太多了，无法在 MySQL 数据库中创建单独的行。
The problem is that we ideally need to store allthe data, and we will probably need to reference the data again at some point in the future - we can't just process through it once and delete the XML string.
问题是我们理想情况下需要存储所有数据，并且我们可能需要在将来的某个时候再次引用数据 - 我们不能只处理一次并删除 XML 字符串。
What's the best way of storing this data in a MySQL database?
将此数据存储在 MySQL 数据库中的最佳方式是什么？
I have forecast that at the predicted rate within a few months, if we were to store the raw XML data in TEXTformat, the database could grow to around 500MB. In the long run this feels impractical.
我曾预测，在几个月内以预测的速度，如果我们以TEXT格式存储原始 XML 数据，数据库可能会增长到 500MB 左右。从长远来看，这感觉不切实际。
回答by Srikar Appalaraju
You could create a blobcolumn (i.e. mediumtextcolumn). Instead of inserting XML purely as strings in the DB, you could zip the XML, then store in MySQL.
您可以创建一个blob列（即mediumtext列）。您可以压缩 XML，然后将其存储在 MySQL 中，而不是将 XML 纯粹作为字符串插入数据库中。
When you read from MySQL, you unzip it again. Since XML is text you'll get very high compression rates (close to 80% compression). The thought process being, disk IO takes a lot longer time than compression/un-compression which is predominantly Processor bound.
当您从 MySQL 读取时，您再次解压缩它。由于 XML 是文本，您将获得非常高的压缩率（接近 80% 的压缩率）。思考过程是，磁盘 IO 比压缩/解压缩花费的时间要长得多，压缩/解压缩主要受处理器限制。
The downside being you will no longer be able to query or do full text search using SQL....
缺点是您将无法再使用 SQL 查询或进行全文搜索....
回答by John Green
The best way is to not store XML in the DB, but I have history with that particular issue.
最好的方法是不在数据库中存储 XML，但我有这个特定问题的历史。
Just store it as TEXT. 500 MB is nothing for MySql, especially with TEXT datatypes, since those aren't stored in the row buffer.
只需将其存储为文本。500 MB 对 MySql 来说不算什么，尤其是对于 TEXT 数据类型，因为它们没有存储在行缓冲区中。
回答by Devart
回答by symcbean
A lot depends on what you want to do with the data - if you want to search for stuff within the XML, then decomposing it into a treewill give much better query performance.
很大程度上取决于您想对数据做什么 - 如果您想在 XML 中搜索内容，那么将其分解为树将提供更好的查询性能。
500Mb is not a huge amount of data - the issues are all about how you reference it and search it. If it's just for archiving purposes or you never need to search inside the XML, then compressing it then (e.g.) base64 encoding will reduce this down to less than 80Mb
500Mb 并不是一个庞大的数据量 - 问题全在于您如何引用和搜索它。如果它只是用于存档目的或者您永远不需要在 XML 中进行搜索，那么压缩它然后（例如）base64 编码会将其减少到小于 80Mb
