How to insert a vector into a table column in mysql?

Question

In R, I have a vector "myVector" of rows that I want to insert in the "myColumn" column of the mysql "myTable" table. I understand that I can write a sql query and run it in R using dbSendQuery. So first give the query sql query. Here is an example:

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

Insert myVector into myColumn myTable column, row numbers are from 3 to 5, here is the sql query that works, except for the last row, which I have no idea:

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

thanks

Answer 1

I don't know if MySQL supports the Vector data type, but you can create your table as a workaround where Vector can be stored in another table and will relate to myTable as 1-M .

This will help you easily manage and retrieve data. So, if myTable is your table, and its existing design:

 myTable ------- id col1 vectorCol 

So the main table may be

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

and the table in which your vector will be saved.

 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 ); 

Answer 2

What you need to do is export the R vector as JSON using toJSON() , for example:

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

Also create or modify myTable so that myColumn corresponding JSON Data Type

An attempt to insert a value into a JSON column succeeds if the value is a valid JSON value, but fails if it is not:

Example

 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); 

Alternatively, you can make an R vector from JSON using the fromJSON() function.

Answer 3

Assuming I understand your problem correctly, I have two possible solutions:

1. one column per element: if your vectors have the same number of elements, you can save each of them in a separate column. Based on your example above, the table might look like this. (column size and zero tolerance or independent of your data)

 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; 

The statement to insert your vector on top:

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

Depending on how many elements your vectors have this approach may be more or less applicable.

2. Storing a vector as a blob: Another approach would be to save the vector as a blob . Blob (Binary Large Object) is a data type for storing a variable sum of (binary) data (see https://dev.mysql.com/doc/refman/5.7/en/blob.html ). This idea is taken from this article: http://jfaganuk.imtqy.com/2015/01/12/storing-r-objects-in-sqlite-tables/

A table can be created using the following statement:

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

When you insert your vector, you bind a variable to your query. Since I'm not an R specialist, I would like to refer to this article for implementation details.