如何有效地将动态列表存储到MySQL列中? [英] How to store a dynamic List into MySQL column efficiently?

问题描述

我想将数字列表以及其他一些字段存储到MySQL中.列表中的元素数量是动态的(有时它可以容纳大约60个元素)

I want to store a list of numbers along with some other fields into MySQL. The number of elements in the list is dynamic (some time it could hold about 60 elements)

当前，我将列表存储到varchar类型的列中，并完成了以下操作.

Currently I'm storing the list into a column of varchar type and the following operations are done.

e.g. aList = [1234122433,1352435632,2346433334,1234122464]

在存储时，aList被隐藏为以下字符串

At storing time, aList is coverted to string as below

aListStr = str(aList)

，并在读取时将字符串转换回列表，如下所示.

and at reading time the string is converted back to list as below.

aList = eval(aListStr)

大约有1000万行，并且由于我以字符串形式存储，所以它占用了大量空间.最有效的方法是什么?

There are about 10 million rows, and since I'm storing as strings, it occupies lot space. What is the most efficient way to do this?

还有，存储字符串列表而不是数字的有效方法应该是什么?

Also what should be the efficient way for storing list of strings instead of numbers?

推荐答案

由于您希望存储整数，一种有效的方法是将它们存储在INT/DECIMAL列中.

Since you wish to store integers, an effective way would be to store them in an INT/DECIMAL column.

创建一个包含这些数字的附加表，并添加一个ID列以将记录与其他表相关.

Create an additional table that will hold these numbers and add an ID column to relate the records to other table(s).

还有什么应该是有效的方法 用于存储字符串列表而不是 数字?

Also what should be the efficient way for storing list of strings instead of numbers?

除了我所说的，您还可以将它们转换为十六进制代码，这将非常容易&占用更少的空间.

Beside what I said, you can convert them to HEX code which will be very easy & take less space.

请注意，较大的VARCHAR可能会对性能产生严重影响. 当完成诸如sotring之类的操作时，VARCHAR(2)和VARCHAR(50)确实很重要，因为MySQL根据VARCHAR最大大小为其分配了固定大小的内存片. 当这些切片太大而无法存储在内存中时，MySQL会将它们存储在磁盘上.

Note that a big VARCHAR may influence badly on the performance. VARCHAR(2) and VARCHAR(50) does matter when actions like sotring are done, since MySQL allocates fixed-size memory slices for them, according to the VARCHAR maximum size. When those slices are too large to store in memory, MySQL will store them on disk.

这篇关于如何有效地将动态列表存储到MySQL列中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！