Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there any array data type in MySQL like in PostgreSQL?

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.

like image 895
Francisco R Avatar asked Apr 04 '11 16:04

Francisco R


People also ask

Does MySQL have array data type?

MySQL doesn't have an array data type. This is a fundamental problem in architectures where storing denormalized rows is a requirement, for example, where MySQL is (also) used for data warehousing.

Is array a data type in PostgreSQL?

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

Is PostgreSQL similar to MySQL?

MySQL is a simpler database that's fast, reliable, well understood, and easy to set up and manage. PostgreSQL is an object-relational database (ORDBMS) with features like table inheritance and function overloading, whereas MySQL is a pure relational database (RDBMS).

Is it good to use array in PostgreSQL?

PostgreSQL arrays are very powerful, and GIN indexing support makes them efficient to work with. Nonetheless, it's still not so efficient that you would replace a lookup table with an array in situations where you do a lot of lookups, though.


2 Answers

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

like image 139
theomega Avatar answered Sep 21 '22 07:09

theomega


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.

like image 31
Chethan N Avatar answered Sep 21 '22 07:09

Chethan N