C++ 如何在Sqlite3的一列中存储数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3005231/
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
How to store array in one column in Sqlite3?
提问by SPK
Is there any way to store an array of integers in one column of table? I want o/p like this:
有没有办法在表的一列中存储整数数组？我想要这样的 o/p：
ident | value | count
----------------+------------------------------------------------------------------------------------------------------------------------+-------
563 | [0:10]={"(0,0)","(1,100)","(2,200)","(3,300)","(4,400)","(5,500)"} | 6
This I have already acheieved through postgres but i want same o/p from sqlite also. Here column value store an array. I tried it through BLOB but it is not working. Somebody told me about serialized way but i am not sure how to do that.
我已经通过 postgres 实现了这一点，但我也希望从 sqlite 获得相同的 o/p。这里列值存储一个数组。我通过 BLOB 尝试过，但它不起作用。有人告诉我有关序列化的方式，但我不知道该怎么做。
回答by Gianni
SQLite3 does not support arrays directly. See herethe type it supports. Basically, it only does Ints, Floats and Text.
SQLite3 不直接支持数组。在这里查看它支持的类型。基本上，它只处理整数、浮点数和文本。
To accomplish what you need, you have to use a custom encoding, or use an FK, i.e. create another table, where each item in the array is stored as a row.
要完成您需要的操作，您必须使用自定义编码，或使用 FK，即创建另一个表，其中数组中的每个项目都存储为一行。
回答by Fornoreason1000
Sorry to Necro, just came across the problem myself and found a solution.
对 Necro 感到抱歉，我自己刚刚遇到了这个问题并找到了解决方案。
As stated already SQLite has no support for arrays, so you can't store them as such. Try this, I had the same problem;
如前所述，SQLite 不支持数组，所以你不能这样存储它们。试试这个，我遇到了同样的问题；
Instead of storing array elements individually, you can store them as a large string and use a string function or Regular expression to parse them back into their types. An C# example
您可以将数组元素存储为一个大字符串，并使用字符串函数或正则表达式将它们解析回它们的类型，而不是单独存储数组元素。一个 C# 示例
int[] myArray = new int[] {8,4,345,378,34456,7};
string Arraystring = myArray[0].ToString();
for(int i = 1; i < myArray.Length; i++) {
Arraystring += "," + myArray[i].ToString();
}
This will turn the array into a single string, now we take the string and insert it into the table as a string, when you read the string use this code to get the array back. Another C# example
这会将数组转换为单个字符串，现在我们将字符串作为字符串插入表中，当您读取字符串时，使用此代码将数组取回。另一个 C# 示例
string value; //assign this via Reader
string[] tokens = values.Split(',');
int[] myItems = Array.ConvertAll<string, int>(tokens, int.Parse);
this will only work with single dimensional arrays, multi-dimensional can get tricky when it comes to parsing the strings.
这仅适用于一维数组，多维在解析字符串时会变得棘手。
回答by nurettin
This is one way of serializing and deserializing data:
这是序列化和反序列化数据的一种方式：
#include <string>
#include <vector>
#include <sstream>
#include <iostream>
std::vector<std::string> deserialize_array(std::string const &csv)
{
std::istringstream parse(csv);
std::vector<std::string> ret;
for(std::string token; std::getline(parse, token, ','); ret.push_back(token));
return ret;
}
std::string serialize_array(std::string* array_ptr, std::size_t N)
{
std::ostringstream cat;
for(std::size_t index= 0; index< N; ++ index)
cat<< array_ptr[index]<< ',';
std::string ret= cat.str();
return ret.substr(0, ret.size()-1);
}
int main()
{
std::string data= "1,2,3";
std::cout<< "Data: "<< data<< std::endl;
std::vector<std::string> deserialized= deserialize_array(data);
std::string serialized= serialize_array(deserialized.data(), deserialized.size());
std::cout<< "Serialized + Deserialized: "<< serialized<< std::endl;
}
Instead of spending time parsing parentheses and extra commas, you can serialize as csv and read two by two when processing the deserialized data.
无需花时间解析括号和额外的逗号，您可以在处理反序列化数据时将其序列化为 csv 并两两读取。
回答by Wolfpack'08
This is what I envision, though it may be incorrect:
这是我的设想，虽然它可能不正确：
<table>
<citation>
<citation ID>
<citation content>
<citation publication date>
CREATE TABLE citation
(
citation_ID INTEGER PRIMARY KEY AUTOINCREMENT,
citation VARCHAR(255)
published datetime
)
<table>
<source doc>
<source doc ID>
<source doc content>
CREATE TABLE source
(
source_ID INTEGER PRIMARY KEY AUTOINCREMENT,
source VARCHAR(5000)
)
<citation_to_source table> //table in question
<relationship>
<relationship ID>
<citation ID>
<source doc ID>
CREATE TABLE citation_to_source //table in question
(
relationship_id INTEGER,
citation_ID INTEGER,
source_ID INTEGER,
FOREIGN KEY(citation_ID) REFERENCES citation(citation_ID)
FOREIGN KEY(source_ID) REFERENCES source(source_ID)
)
Output format:
输出格式：
<content>
<relationship ID>
<unique source document content>
<enumerate citation IDs>
