MySQL - Array in cell

Question

My scenario is that I have a table that stores classes at a school. Each class is a row in the table. One of the fields in there is the class period that the class is offered in. Some are only offered one period, some are offered multiple periods, some are offered every period.

In the CSV I have that contains the data, the class period data, it is stored as a list of comma separated values with no spaces. For example, Algebra runs periods 1, 2, 3 and 4, so in the CSV its represented as "1,2,3,4".

My issue is when it comes to query for, for example, all classes that run during 3rd period, something like SELECT * FROM `classes` WHERE `period` LIKE '1' won't find it. Adding wildcard % characters doesn't help because it also picks up classes that run during 11th period.

How can I store these multiple values in the cell, and query for them?

EDIT: This is all data I was provided. There are 200+ classes, so reformatting all the data into another table and using JOIN (which would be the proper way to do this), would be incredibly time consuming. I am looking for a way to work with the data in the format I already have it in.

You should be storing your period data along with the class id in a separate table, then joining the tables together to get your data. Trying to store multiple values in a single field is generally a bad idea. — mark_b, Dec 03 '19 at 15:30
You should read [my answer to Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/a/3653574/20860) — Bill Karwin, Dec 03 '19 at 15:52
I am fully aware this isn't the best way to be doing this. I'm just trying to work with the data I already have / was provided with. There's a ton of classes so going through and redoing all this would be incredibly time consuming. Hence why I'm asking if there's a way to work with the data I already have. If there's a way of getting this data into the proper format without typing it all by hand, I'm all for it. — lightbord, Dec 03 '19 at 17:19

lainatnavi · Answer 1 · 2019-12-03T22:31:24.193

2

This is better done by creating a relations table between classes and periods.

create table class_period(
  class_id int,
  period int
)

Then you can query the classes with period 1

select * from classes c join class_period p on c.id = p.class_id where p.period = 1

edited Dec 03 '19 at 22:31

answered Dec 03 '19 at 15:49

lainatnavi

1,453
1
14
22

See my previous comment above, but while yes, I agree this is the better way to do this, I am asking how to deal with the data I was already provided as opposed to re-doing all 200+ classes by hand into a new database. – lightbord Dec 03 '19 at 17:33
Well, without changing the already stored data, If mysql is version 8 I think you can use `regexp_like` for an exact match, or play with mysql's `substring` function if the version is less than 8. – lainatnavi Dec 03 '19 at 18:33

score 0 · Answer 2 · answered Dec 03 '19 at 17:53

If you insist to do this with your actual db structure there are some fancy methods. Here is one.

Store the data as e.g. "|1|3|4|11|" in column period. now you have unique strings to search for. ("|1|" != "|11|")

Example

SELECT * FROM `classes` WHERE `period` LIKE '%|1|%';

If you use a little program you can derive the also mention reference table with little effort. The "join" proposal, is really the best way.

MySQL - Array in cell

2 Answers2