mba777 has asked for the wisdom of the Perl Monks concerning the following question:

Hello guys,

i've a problem with storing a multiple value field in a MySQL database.

First of all i will tell you what i'm goinig to do. I will sync all users and contacts from an active directory to a mysql database. So far i've written a script which is doinig that. The only problem is a multi value field in the active directory, named "memberOf". This field sometimes contains more than one value, but only the first value is written to the database. But what i want to do is to write all values from a user/contact in only one mysql column.

I know that I can get all values like this, but the question is, how to write these values in one mysql column?

my @member = $entry->get_value('memberof');

Some help would be really nice. Thanks Andy

Replies are listed 'Best First'.
Re: Storing multiple value field in a MySQL database
by roboticus (Chancellor) on Nov 25, 2013 at 20:05 UTC


    If you have to store it in a single column, you can turn the list into a string with a suitable delimeter, such as:

    my $members = join("; ", @member);

    That can do the job, but it really limits the utility of the member list information in your database. It's rather difficult to ask the database to give you the groups in common between two people, the list of people in any particular group, etc. As indicated by davido, storing the information in more than one table will let you ask more interesting questions about the data.

    For example, suppose you have these tables:

    001 Bennet Tony 001
    002 Sinatra Frank 001 002
    003 Martin Dean 001 002 003
    004 Davis Sammy 001 002 003
    005 Lewis Jerry 002 003
    006 Jones Spike 004
    007 Hope Bob 001 002 003
    001 Singer
    002 Actor
    003 Comedian
    004 Musician

    The Professions column holds a list of the professions the celebrity is a member of. Unfortunately, this column isn't very easy to use. Sure, you can query it to find a list of all celebrities who are actors:

    select * from celebrities where professions like '% 002 %'

    Hmmm ... that's not quite right[1], if the actor group ID is first or last, it won't have a blank on either side, so you'll *really* need something more like:

    select * from celebrities where professions='002' -- The only item in the list or professions like '002 %' -- it might be first of several or professions like '% 002' -- it could be the last of several or professions like '% 002 %' -- or in the middle

    The code to add the acting profession to a celebrity is a little trickier[2]. (We'll use X to hold the ID of the celebrity we're interested in):

    if 1 > (select count(*) from celebrities where (professions='002' or professions like '002 %' or professions like '% 002' or professions like '% 002 +%') and cel_id=X ) then /* Celebrity isn't an actor, so add to the end */ update celebrities set professions = '002' || case when professions is null then '' else ' '||professions end where cel_id=X; end if;

    More complicated operations get messier very quickly. Imagine how to ask SQL to delete the acting profession from a celebrity.

    Let's add a "many to many" table to join celebrities and professions. It makes the data a little more complex, but the code much simpler:

    001 001
    002 001
    002 002
    003 001
    003 002
    003 003
    004 001
    004 002
    004 003
    005 002
    005 003
    006 004
    007 001
    007 002
    007 003

    Finding all celebrities who are actors is much simpler:

    select * from celebrities where cel_id in ( select cel_id from celebrity_professions where prof_id='002' )

    Adding the acting profession is similarly easy:The code to add the acting profession to a celebrity is a little trickier[1]:

    if 1 > (select count(*) from celebrity_professions where prof_id='002' and cel_id=X ) then insert into celebrity_professions (cel_id, prof_id) values (X, '002 +'); end if;

    Deleting the acting profession for celebrity X is even simpler--We just delete the association, without regard to whether it exists or not:

    delete celebrity_professions where cel_id=X and prof_id='002';

    Now a more complicated question: Given a celebrity, give a list of all celebrities they may have worked on a project with. I wouldn't attempt to do that with the original table layout, but with the current layout, it's pretty simple:

    select * from celebrities where cel_id in ( select cel_id from celebrity_groups where group_id in ( select prof_id from celebrity_groups where cel_id=X ) )

    So the innermost select gives us a list of all the groups the celebrity is in. The next query out gives a list of all celebrity IDs in all of the groups we just retrieved. The outermost query simply gets the information we want from the celebrities table for all the celebrity IDs we have.


    [1] If we ensure that we use a non-digit delimiter and all the profession IDs are exactly the same length, we could get away with '%002%', but I want to stress how ugly a list in a string actually is. Even with the fixed length group ID, deletion and insertion are still ugly.

    [2] The SQL syntax used for the conditionals isn't standard SQL. (In fact, I'm not sure there *is* a standard syntax for things like that.) But each of the dialects of SQL I've used so far give you a similar construction.


    When your only tool is a hammer, all problems look like your thumb.

Re: Storing multiple value field in a MySQL database
by davido (Archbishop) on Nov 25, 2013 at 18:57 UTC

    Wrong solution; If you have a field that needs to hold more than one value, that field usually deserves a new table:

    Table1: userid email age john 45 jerry 22 Table2: userid memberof john admins john wizzards jerry newbies jerry trainees jerry newhires


      Hi Dave, that's clear but i really want all values of the multi value field in one mysql column, is this not possible?

      Thanks Andy


        davido has illustrated the general layout of the tables for the normal solution. An alternative would be:

        Table1: userid email age memberof john 45 admins john 45 wizzards jerry 22 newbies jerry 22 trainees jerry 22 newhires

        which is obviously dumb because of all the repeated information and the slightly more subtle problem that if jerry's age changes (age does tend to change) you have to fix every 'jerry' row. Or maybe you mean something like:

        Table1: userid email age memberof john 45 admins!wizzards jerry 22 newbies!trainees!newhires

        but then there is no efficient way to search for all newhires.

        So, again, why? What is it about davido's solution that you don't like or can't understand?

        True laziness is hard work

        Of course you can create one VARCHAR(512), or whatever field, cram a bunch of comma-separated-values into it, and parse them out later. You can do that. But that's not an effective use of your database. It prevents you from easily searching in-database for all users who are a member of "wizzards". Instead you will have to use "LIKE" clauses, which are less efficient, or slurp all records into your script and do your own processing.

        SQL-style databases usually just use another table whenever a developer is tempted to cram more than one item into a given field. There's good reason for that.


Re: Storing multiple value field in a MySQL database
by boftx (Deacon) on Nov 25, 2013 at 20:23 UTC

    There have been a number of replies now that give a more "ideal" solution. But let's take the OP at face value (as reinforced by a later comment) and accept that he has restrictions that preclude the "better" solutions.

    Doing that yields the simpler (but not "ideal" from a DB perspective) of this:

    my $memberstr = join('|',@member);

    One should probably trim any possible leading and/or trailing whitespace just to be safe. Is this an ideal solution? No. Can it be converted to a better solution later if desired? Yes, and without much hassle.

    So rather than point all the problems that the OP has with this (at great length in some cases) just take the OP at his word that he understands all of this and appears to have restrictions that dictate that the less than ideal solution be used.

    It helps to remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.

      Well said, boftx. I run into columns that have multiple values separated by commas now and then. It works fine and would be easy to refactor if need be.

Re: Storing multiple value field in a MySQL database
by erix (Parson) on Nov 26, 2013 at 20:43 UTC

    ISTM you need an array data type like more advanced databases (than Oracle's MySQL) have. I'm a bit surprised that MySQL doesn't have them (yet).

    According to Oracle's MySQL website:

    MySQL will implement a data type, ARRAY, to store variable-sized arrays, in compliance with Standard SQL (SQL:2003) array functionality.

    So for an array datatype you have to wait, or switch to a more flexible RDBMS. (I'm pretty sure that Oracle too has had Arrays for a long time, since version 7 or so)

Re: Storing multiple value field in a MySQL database
by FloydATC (Deacon) on Nov 26, 2013 at 19:58 UTC
    The whole point of relational databases is to model your data using tables, then join those tables using keys as others have shown. There are so many reasons why you want to do this, here are just a few of them:
    • Sooner or later you will want to define properties for those groups, so you need a table for them anyway
    • Using separate tables you are not limited to asking "what groups is this user member of", you can just as easily ask "what users are member of this group"
    • As the data set grows, storing the group names over and over again is a complete waste
    • What if you want to rename or delete a group at some point in the future
    • What if at some point in the future you want to make the groups hierarchial and then for some reason use the same name in several places
    The list just goes on...

    Learn proper SQL data modeling right from the start, it will save you SO much extra work.

    -- FloydATC

    Time flies when you don't know what you're doing