I've googled. I keep getting answers that have to do with a PHP function named "serialize". I won't be working with PHP, so that solution won't work for me sadly.

I worked with a NoSQL db (MongoDB I think) before, and I basically define a collection as such

images [
    {"title": "Cute puppy", "file"="cute.png", "tags": ["cute", "puppy", "summer", "pool"]}

I'm wanting to store multiple values in one row for the tags column (honestly I don't know if I said that right, just began searching about MySQL)

| title      | file     | tags                     |
| Cute Puppy | cute.png | cute,puppy,summer,pool   |

The only idea that's coming to me is if I were to use MySQL to read the row, then just split the tags by a comma, that'd work most likely, but I'd be working with many more "tags" possibly in the millions.

My question really is, is MySQL a good choice if you want to store multiple values in one column?

  • 1
    Store multi-attribute in one field in CSV form (or any another similar to - for example, JSON) is not best practice. This type of store produces too much problems while processing the data. Separate table as Tags dictionary and separate relational table is the most common way to store and process those data.
    – Akina
    Nov 5, 2019 at 7:47
  • 1
    If you really want to store relational and non-relation data in a single database (or even tble) you should seriously consider switching to Postgres. Its JSON capabilities are way better than MySQL's (and it supports native, indexable arrays).
    – user1822
    Nov 5, 2019 at 12:47
  • You might be interested in this: databasesoup.com/2015/01/tag-all-things.html (it's for Postgres, but the non-array solutions should work with MySQL as well)
    – user1822
    Nov 5, 2019 at 12:48
  • You want a joining table (also known as a many-to-many, m-to-n, and bridging amongst others) - in technical jargon this is known as an associative entity. The example given here shows how this works. The indexing strategy you should use for such tables is shown in this post.
    – Vérace
    Nov 5, 2019 at 13:19
  • 1
    See also GROUP_CONCAT() and FIND_IN_SET().
    – Rick James
    Nov 5, 2019 at 22:39

2 Answers 2


My question really is, is MySQL a good choice if you want to store multiple values in one column?

Codd's Rules

  • Rule 2: The guaranteed access rule:

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

Just a note: This second rule (actually the 3rd one - numbering starts, in true geek style, from zero) would suggest that arrays have no place in a relational system, however there is no reason that an array cannot be considered "atomic". A string (an array of characters in C for example) can be considered atomic, why not an array of numbers (or other datatype).

It should also be recognised that arrays can be very useful and do correspond to real-life entities, so inclusion of the array type and attendant functionality should come as no surprise to "realists". Codd's rules were derived from his studies of relational calculus and some say that a complete implementation would be too ivory tower for real-world RDBMSs. However, we're drifting out of scope...

The question:

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.

  1. Simple arrays: ==============

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.

PostgreSQL has a an ARRAY datatype with a large range of functions to deal with manipulating, comparing and finding elements in arrays. Of particular interest are the UNNEST() and STRING_TO_ARRAY()/REGEXP_SPLIT_TO_ARRAY() functions which provide convenient methods for moving data to and from arrays to relational constructs.

  1. JSON: =====

Nowadays, arrays have been largely superseded by JSON - hardly surprising since JSON is basially an array++ (but XML-- :-)). This is true for MySQL and for PostgreSQL.

There are a large number of JSON functions available on both systems - MySQL and PostgreSQL - but more on PostgreSQL. I'll use the terms JSON and JSONB (binary - example is for mongodb but applies to binary JSON generally) interchangeably. MySQL JSON is only binary, PostgreSQL has both - but JSONB is generally regarded as much more efficient. See this post for a good discussion of what PostgreSQL "NoSQL types" are good for. The "NoSQL" is a misnomer; you can do SQL JOINs using these types.

With respect to indexing - MySQL essentially implements this by means of a GENERATED column hack whereas PostgreSQL implements 3 sorts of index (Gin, Btree and Hash - see section 8.14.4. jsonb Indexing here) for JSONB.

Joins can be done between JSON and tables in both MySQL and PostgreSQL.

Overall, it is fair to say that JSON has been around longer and is more mature in PostgreSQL than in MySQL.

There are reports that PostgreSQL can actually perform even better than MongoDB with JSON documents - there is a caveat here in that the research was done by companies (EnterpriseDB and Ongres) which are PostgreSQL providers, however the tests that they performed were open-sourced and are therefore open to scrutiny.

Classic SQL:

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.

1 course can have many students and 1 student can attend many courses. The joining table contains the PRIMARY KEY from both the course and student tables and the PK of the joining table itself is the two together - a student can't be in the same course twice!

In favour of the classic approach, Erwin Brandtstetter (a guy well worth listening to about databases!) pointed out in his answer referring to a JSON solution: (Aside: A normalized DB design with basic data types would be way more efficient for this.). JSON is not a panacea and judgement must be exercised in its use. There's been a lot of bandwagon-hopping in recent years with the NoSQL "[Cambrian Explosion][23]" from ~ 2008 - 2015 (Mongodb especially springs to mind). Now virtually all of the NoSQL systems have, or are trying to, put SQL interfaces and instrumentation into their systems. 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.. With .csv lists a further positional parameter is required which SQL doesn't deliver but various systems have "tricks" to get round this. SQL's forté is "slicing and dicing" records made up of small simple atomic datums.

PostgreSQL is the F/LOSS (Free/Libre and Open Source Software) system which comes closest to offering the best for all 3 array methods. MySQL is more widespread and possibly has better inbuilt and 3rd party replication and/or sharding, but PostgreSQL is more standards compliant and has been making strides in those areas recently. It also has superior JSONB functionality.

  • Wow.. thank you very much for your answer detailed answer to my question, I'm installing postgresql right now :). again thank you very informational! p.s you know to much about databases :p
    – nooby
    Nov 6, 2019 at 1:40

If we have one table like that

mysql> select * from user_mail;
| email        |  user | 
| email1@gmail |     1 | 
| email2@gmail |     2 |

and the array table:

mysql> select * from user_mail_array;
| email        |  user | preferences |
| email1@gmail |     1 |           1 |
| email1@gmail |     1 |           2 |
| email1@gmail |     1 |           3 |
| email1@gmail |     1 |           4 |
| email2@gmail |     2 |           5 |
| email2@gmail |     2 |           6 |

We can select the rows of the second table as one array with CONCAT function:

mysql> SELECT t1.*, GROUP_CONCAT(t2.preferences) AS preferences
     FROM user_mail t1,user_mail_array t2
       where t1.email=t2.email and t1.user=t2.user
     GROUP BY t1.email,t1.user;

| email       |  user | preferences |
|email1@gmail |     1 | 1,3,2,4     |
|email2@gmail |     2 | 5,6         |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.