如何在mysql表的列中插入向量？

Question

在R中，我有一个字符串向量“myVector”，我想将其插入到mysql表“myTable”的列“myColumn”中。我知道可以编写SQL查询，并使用dbSendQuery在R中运行它。因此，让我们先确定SQL查询。以下是一个示例：

myVector = c("hi","I", "am")

让我们将myVector插入到myTable的myColumn列中，行号为3到5，请看下面的SQL查询语句，除了最后一行我不知道该怎么做：

UPDATE myTable t JOIN
       (SELECT id
        FROM myTable tt
        LIMIT 3, 3
       ) tt
       ON tt.id = t.id
    SET myColumn = myVector;

谢谢

- Mohammad

---

请注意，没有ORDER BY的LIMIT是相当无意义的。 - Strawberry

你是想插入新的记录，还是更新已有的记录？ - grizzthedj

不清楚。请展示表模式（SHOW CREATE TABLE）和操作的期望结果。 - Rick James

id在myTable中是AUTO INCREMENT PRIMARY KEY吗？你的更新查询显示它不是，或者你有其他默认排序表的字段。 - user2560539

Answer 1

假设我正确理解了您的问题，我脑海中有两种可能的解决方案： 1. 每个元素一列： 如果您的向量都具有相等数量的元素，则可以将每个向量存储在单独的列中。以您上面的示例为基础，表格可能如下所示。（列的大小和是否允许空值取决于您的数据）

CREATE TABLE `myTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `element1` varchar(255) DEFAULT NULL,
  `element2` varchar(255) DEFAULT NULL,
  `element3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入上述向量的语句如下：

INSERT INTO `myTable` (`id`, `element1`, `element2`, `element3`)
VALUES (1, 'hi', 'I', 'am');

根据向量中元素的数量，这种方法可能更或者不太适用。

2. 将向量存储为blob: 另一种方法是将向量存储为blob。Blob（二进制大对象）是一种数据类型，用于存储可变数量的（二进制）数据（参见：https://dev.mysql.com/doc/refman/5.7/en/blob.html）。这个想法来自于这篇文章：http://jfaganuk.github.io/2015/01/12/storing-r-objects-in-sqlite-tables/

可以使用以下语句创建表：

CREATE TABLE `myTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `myVector` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

当插入向量时，您需要将变量绑定到查询中。由于我不是R专家，因此我建议参考本文以获取实现细节。

- Ferdi

Answer 2

我不确定MySQL是否支持Vector数据类型，但您可以设计一个解决方案，将Vector存储在不同的表中，并与myTable建立关联，关系为1-M。这有助于您轻松管理和检索详细信息。因此，假设myTable是您的表，它的现有设计如下：

myTable
-------
id
col1
vectorCol

所以，你的主表可以是：

CREATE TABLE myTable (
    id        INT NOT NULL AUTO_INCREMENT,
    col1  varchar(50),
    PRIMARY KEY (id)
);

和存储向量的表格。

CREATE TABLE vectorTab ( 
    id    INT NOT NULL AUTO_INCREMENT, -- in case ordering matter
    parent_id        INT NOT NULL, 
    value     TEXT,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES myTable (id) ON DELETE CASCADE ON UPDATE CASCADE
);

- Ravi

---

请详细解释一下表格myTable中col1列的内容以及vectorTab中value列的内容。谢谢。 - undefined

Answer 3

你需要做的是使用 toJSON() 函数将你的 R 向量导出为 JSON 格式，例如：

myJSONVector = toJSON(c("hi","I", "am"))

请创建或修改myTable，以便myColumn具有适当的JSON数据类型。

如果值是有效的JSON值，则尝试将值插入JSON列会成功，否则会失败：

示例

CREATE TABLE `myTable` (`myColumn` JSON);
INSERT INTO `myTable` VALUES(myJSONVector); // will fail if myJSONVector is not valid JSON
// update query would be
UPDATE `myTable` SET `myColumn` = myJSONVector
WHERE `id` IN (3,4,5);

此外，你可以使用函数fromJSON()将JSON转换为一个R向量。

- user2560539