I need to store arrays of integers in a MySQL database. In there something equivalent to this in MySQL?
CREATE TABLE tictactoe (
squares integer[3][3]
);
I want to store matrices with dimension 20x6. I don't feel like creating a table with 120 columns. There is no need to query on this field, just need to store and retrieve full matrices.
If it matters, i use Perl.
No, there is no such thing. There is an open worklog for that, but no progress has been made on implementing this feature.
You have to emulate this somehow, using either multiple fields (9 in your case) or pack the integers together into a larger datatype (blob for example).
blob seems to be a valid workaround for my needs.
Store them in a text format using proper delimiters. ex:- If you want to store numbers from 1 to 9, store them in text format as 1-2-3-4-5-6-7-8-9 where '-' is a delimiter. Explode the string and get the desired numbers.
There is no array datatype in MySQL. But you may store array in JSON array form. Of course the SQL text will be more complex in this case.
A sample:
-- create JSON column which will store an array
CREATE TABLE test (id INT, value JSON);
-- insert 2-dimension array
INSERT INTO test (id, value) VALUES
(1, '[[1,2,3], [4,5,6], [7,8,9]]'),
(11, '[[11,12,13], [14,15,16], [17,18,19]]');
-- see the data
SELECT id, CAST(value AS CHAR) FROM test;
| id | CAST(value AS CHAR) |
|---|---|
| 1 | [[1, 2, 3], [4, 5, 6], [7, 8, 9]] |
| 11 | [[11, 12, 13], [14, 15, 16], [17, 18, 19]] |
-- retrieve the values for an element array(1,2) which are 6 and 16
-- remember that the elements enumeration starts from zero
SELECT id, value->'$[1][2]' AS `array(1,2)` FROM test;
| id | array(1,2) |
|---|---|
| 1 | 6 |
| 11 | 16 |
-- update the value for this element to 10 for the row with id=1
UPDATE test
SET value = JSON_REPLACE(value, '$[1][2]', 10)
WHERE id = 1;
-- view the result
SELECT id, CAST(value AS CHAR) FROM test;
| id | CAST(value AS CHAR) |
|---|---|
| 1 | [[1, 2, 3], [4, 5, 10], [7, 8, 9]] |
| 11 | [[11, 12, 13], [14, 15, 16], [17, 18, 19]] |
-- update the value for the whole row to [20,21,22] for the row with id=11
UPDATE test
SET value = JSON_REPLACE(value, '$[1]', JSON_ARRAY(20,21,22))
WHERE id = 11;
-- view the result
SELECT id, CAST(value AS CHAR) FROM test;
| id | CAST(value AS CHAR) |
|---|---|
| 1 | [[1, 2, 3], [4, 5, 10], [7, 8, 9]] |
| 11 | [[11, 12, 13], [20, 21, 22], [17, 18, 19]] |
-- retrieve the whole array as a table
SELECT test.id, jsontable.num, jsontable.`1`, jsontable.`2`, jsontable.`3`
FROM test
CROSS JOIN JSON_TABLE(test.value,
'$[*]' COLUMNS (num FOR ORDINALITY,
`1` INT PATH '$[0]',
`2` INT PATH '$[1]',
`3` INT PATH '$[2]')) jsontable;
| id | num | 1 | 2 | 3 |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 3 |
| 1 | 2 | 4 | 5 | 10 |
| 1 | 3 | 7 | 8 | 9 |
| 11 | 1 | 11 | 12 | 13 |
| 11 | 2 | 20 | 21 | 22 |
| 11 | 3 | 17 | 18 | 19 |
PS. JSON is binary datatype, CAST(value AS CHAR) needed for to convert it to string.
No there is not. Short answer but without knowing what you have do that is the answer.
