[R] How to store an array in MySQL

[Chris Stubben]

> Hi all, does somebody know how to store an array in MySQL with the
> package RMySQL. Thanks in advance.
> 

A similar question was asked last month. 

http://finzi.psych.upenn.edu/R/Rhelp02a/archive/81429.html

In a normalized database, you should store the index and value in separate
columns.  Try this...


a<-array(1:3, c(2, 4))
     [,1] [,2] [,3] [,4]
[1,]    1    3    2    1
[2,]    2    1    3    2


d <-dim(a)


z<-cbind(expand.grid(r=1:d[1],c=1:d[2]),x=as.vector(a))
  r c x
1 1 1 1
2 2 1 2
3 1 2 3
4 2 2 1
5 1 3 2
6 2 3 3
7 1 4 1
8 2 4 2


library(RMySQL)

con<-dbConnect(MySQL(), dbname="test")

dbWriteTable(con, "array", z, row.names=FALSE)
[1] TRUE


---

Now in Mysql 

select * from array;
+------+------+------+
| r    | c    | x    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    2 |
|    1 |    2 |    3 |
|    2 |    2 |    1 |
|    1 |    3 |    2 |
|    2 |    3 |    3 |
|    1 |    4 |    1 |
|    2 |    4 |    2 |
+------+------+------+



select group_concat(x order by c separator ' ' ) as a from array group by r;
+---------+
| a       |
+---------+
| 1 3 2 1 |
| 2 1 3 2 |
+---------+


Chris Stubben