在MySQL数据库中存储XML数据的最佳方式，有一些具体要求 [英] Best way to store XML data in a MySQL database, with some specific requirements

问题描述

我从服务接收XML数据。我收到的测试数据有大约300个XML节点，明显太多，无法在MySQL数据库中创建单独的行。

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字符串。

The problem is that we ideally need to store all the 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.

在MySQL数据库中存储这些数据的最佳方法是什么？

What's the best way of storing this data in a MySQL database?

我预测在几个月内，如果我们将原始XML数据存储在 TEXT 格式中，数据库可以增长到大约500MB。

I have forecast that at the predicted rate within a few months, if we were to store the raw XML data in TEXT format, the database could grow to around 500MB. In the long run this feels impractical.

推荐答案

您可以创建一个 blob 列（即 mediumtext 列）。

You could create a blob column (i.e. mediumtext column). Instead of inserting XML purely as strings in the DB, you could zip the XML, then store in MySQL.

当您从MySQL读取时，您可以再次解压缩XML。由于XML是文本，你将获得非常高的压缩率（接近80％的压缩率）。思想过程是，磁盘IO比压缩/解压缩（其主要是处理器绑定）需要更长的时间。

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.

缺点是，您将无法再查询或使用SQL ...进行全文搜索。

The downside being you will no longer be able to query or do full text search using SQL....

这篇关于在MySQL数据库中存储XML数据的最佳方式，有一些具体要求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！