0

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 wont 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 if I was 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?

  • 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 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). – a_horse_with_no_name Nov 5 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) – a_horse_with_no_name Nov 5 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 at 13:19
  • I really don't use mysql, Is it really worth it to switch to mysql and learn mysql for something that seems complicated (join tables)? I pretty much brushed up on mangodb's core concepts last night in a few hours with remembering almost nothing for four years ago. Not a brag, just saying it, I think it would take me a little "bit" longer to learn mysql and then do some hacky thing to try to make it work (I don't know if "joining tables" is hacky just assuming so because I haven't seen other answers about joining tables when searching this topic). Thanks everyone @Vérace – nooby Nov 5 at 15:14
0

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

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.

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

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

p.s. - oh, yes, I forgot - welcome to the forum! :-)

  • 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 at 1:40
-3

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.

  • Why will there be a lag? Here's a reason not to use Mongodb. – Vérace Nov 5 at 13:21
  • contd... If you do answer a question, then it's a good idea to back up what you say with some references and maybe some quotes from URL's or readily available texts. As it is, you've just expressed an opinion with little to back it up - Mongodb is specifically for documents, but a joining table strategy outlined in my comment to the question is also an efficient way to deal with this situation. p.s. welcome to the forum! :-) – Vérace Nov 5 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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