Can you store arrays in mysql

During the MySQL 5. JSON provides SQL a convenient method to store complex data types like arrays, sets, maps, dictionaries, and many more.

This method is essentially an array type being stored in a single column. This is an effective way to store an array within SQL. If you really want to or need to store arrays, here are some ways that you can do it. Firstly we create customer and order tables. I would suggest that the answer to this question is no - there is a better choice on all 3 levels for the treatment of arrays in database servers.

MySQL doesn't really have an array datatype - the closest thing they have is the SET datatype , the functionality of which is very limited. As you note in your question, a search leads to lots of links to PHP code which implements array functionality in the app rather than the db. You can of course implement some "multi-element" functionality by using a classic Associative Entity approach.

Associative entities are otherwise known as joining , bridging , many-to-many or m-to-n tables. This strategy is outlined here with an example using students enrolling in university courses. In favour of the classic approach, Erwin Brandtstetter a guy well worth listening to about databases!

JSON is not a panacea and judgement must be exercised in its use. For my money, if your arrays are relatively simple, stick with the classic approach, otherwise go with JSONB. SQL wasn't designed for and is not good at manipulating comma-separated lists of values.

In any case they are a breach of Rule 2 of Codd's Rules which states that Each and every datum atomic value in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

Your mongodb will be better choice for that. In mysql there will be a lag for reading operation. Go with mongodb it's management is easy and faster. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. Ask Question. Asked 2 years ago. Active 1 year, 4 months ago. Viewed 34k times. About Worklog MySQL Worklogs are design specifications for changes that may define past work, or be considered for future development.

MySQL 4. Oracle10g do provide array support. It defines the type of data which the array will contain. It defines the maximum cardinality of the array, rather than its exact size. Note that the inner set of brackets are mandatory when defining an array with a specific size, e. If omitted, the maximum cardinality of the array defaults to an implementation-defined default value.