将数组本身保存在数据库中 [英] saving array itself in database.
问题描述
我想在数据库中保存数组（mysql），我的数组包含50个值，例如
a [0,1 ，0] = 2.3;
a [0,2,0] = 8;
a [0,2,1] = 6;
。
。
。
a [0,50,1] = 4.5
现在我想保存这个数组数据库，另一种方法是读取数组的每个元素的值，并将它们合并为字符串并保存在数据库中，但我不想这样做。我直接想把数组保存在数据库中作为表元组。
关于
Hi,
I want to save array in database(mysql), My array contain 50 values e.g
a[0,1,0]= 2.3;
a[0,2,0]=8;
a[0,2,1]= 6;
.
.
.
a[0,50,1]=4.5
now I want to save this array in database, another way is to read value of each element of array and connate them to string and save in database but I do not want to do in that way. I directly want to save array in database as table tuple.
regards
推荐答案
你可以做到，只需在数据库中使用VARBINARY格式列，在C＃代码中使用BinaryFormatter将多维数组的双精度转换为平面字节数组：
You can do it, just uses a VARBINARY format column in your database, and a BinaryFormatter in your C# code to convert the multidimensional array of doubles to a "flat" array of bytes:
double[, ,] ar = new double[2, 3, 4];
ar[0, 1, 0] = 2.3;
ar[0, 2, 0] = 8;
ar[0, 2, 1] = 6;
...
ar[1, 2, 1] = 4.5;
// Convert to bytes
BinaryFormatter bf = new BinaryFormatter();
MemoryStream ms = new MemoryStream();
bf.Serialize(ms, ar);
byte[] data = ms.GetBuffer();
// Save bytes to DB
然后当你把它们作为字节读回来时：
Then when you read them back as bytes:
// Convert from bytes
BinaryFormatter bf = new BinaryFormatter();
MemoryStream ms = new MemoryStream(data);
double[, ,] ar2 = (double[,,])bf.Deserialize(ms);
我相信（但可能是错误的）关系数据库术语中的'元组'意味着'包含表的列组'，或多或少。
根据该定义，您似乎希望为数组中的每个元素创建一个一列的表的。希望，我误解了你，因为那不是一个健壮的架构......当数组增长时，你需要添加新的列（当在关系数据库中添加行是适当的响应时，这很容易）。
所以，这是我认为可行的解决方案。
你的阵列是三维的，带有每个维度都由int索引。标识数组中特定值的索引集（元组）必须是包含数组值的数据库表的主键;该表将包含另一个包含该值的列，如果您需要跟踪更改时间和更改值，则可能包含其他列。
这是一个表定义，将完成此任务：
I believe (but could be wrong) that 'tuple' in relational database terms means 'the group of columns that comprises a table', more or less.
On the basis of that definition, it would appear that you want to have a table with one column for each element in your array. Hopefully, I've misinterpreted you, because that is not a robust architecture ... when the array grows you'd need to add new columns (when, in a relational database, adding rows is the appropriate response, which is easy).
So, here's a solution that I think would work.
Your array is three dimensional, with each dimension being indexed by an int. The index set (tuple) that identifies a specific value in the array must be the Primary Key of the database table that holds the array values; the table will have one other column that contains the value, and may have others if you need to track when and by whom values were changed.
Here's a table definition that would accomplish this:
CREATE TABLE GiveMeAMeaningfulName
(
Index1 INT NOT NULL, -- Give me a better name!
Index2 INT NOT NULL, -- And me!
Index3 INT NOT NULL, -- Me too
Value FLOAT NOT NULL, -- This column's name should probably relate to the table name; its type might be better as NUMERIC(xx,yy); if you were using a sparse array, it could be NULL, but you're not, so it is NOT NULL
-- Optional change tracking
-- CreatedBy INT NOT NULL, -- Data type as needed for your user table primary key
-- CreatedOn DATETIME NOT NULL,
-- AmendedBy INT NULL,
-- AmendedOn DATETIME NULL,
-- Necessary primary key
PRIMARY KEY (Index1, Index2, Index3)
)
GO
注 - 我使用Microsoft SqlServer;我的SQL方言可能无法与MySql完美配合。这个想法应该是有效的。
使用像这样的表格设计，你可以自由地保存阵列的所有元素，或者只是那些已经改变的元素。与集合必须保存到数据库/从数据库加载的所有其他时间一样，遗憾的是，您必须遍历集合以保存/加载每个元素。可能有一些方法可以使用一个批量处理的命令来完成它;如果有其他人可以提供给他们（我也可以学习！）。
Note - I work with Microsoft SqlServer; my SQL dialect may not work perfectly with MySql. The idea should nevertheless be valid.
With a table design like that you are free to save all elements of your array, or just those that have changed. As with all other times when a collection must be saved to / loaded from a database, unfortunately you have to iterate through the collection to save/load each element. There may be ways to do it with a single command that deals in bulk; if there are maybe others here will offer them (and I can learn, too!).
这篇关于将数组本身保存在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
