CODEWITHSUNDEEP
mysqldatabase
user3542112
user3542112

Reputation: 253

Storing Array of Integers in mysql

I have a table in which I want to store a list of Days chosen (Sun-Sat) But to keep it simple i want to store as ints 1 to 7.

Doing some research I've found 2 ways to handle this "correctly".

creating boolean columns for each day... "Monday" = "False" if not chosen etc.

I really don't like this... too many columns

Another method is to have a table that only holds a composite key

For example day_event

dayID eventID

So if the

event 1 had day 1 2 3

and event 2 had day 2 5 it would be something like this

day : event
1 : 1
2 : 1
2 : 2
3 : 1
5 : 2

then i would be able to select * from day_event where eventID = 2 and so on to get my list of days.... But i really don't like this either... all that just to store a few days?

To me it seems more simple, and practical to have a string column like 'days' like "1,2,3" and "2,5" respectively, as wrong as it may be.

Is there another solution?

Upvotes: 2

Views: 3766

Answers (1)

Mihai Stancu
Mihai Stancu

Reputation: 16107

You can store it as a SET column defined as: CREATE TABLE table_name (

    /* ... */

    `column_name`SET(
        'Monday',
        'Tuesday',
        'Wednesday',
        'Thursday',
        'Friday',
        'Saturday',
        'Sunday'
    ),

    /* ... */
);

A set is like a bitmask so (for example) both Monday and Wednesday could be active simultaneously in the column value.

Sets are limited to a total of 64bits (meaning 64 values) -- which in your case is not a problem.

In your case the SET type is very adequate because you can even filter by this column with very little overhead (compared to the other options the string ad the table this is 0 almost overhead).

SELECT *
    FROM `table_name`
    WHERE `column_name` = 'Monday,Wednesday'

Upvotes: 2

Related Questions