Implementing a c/c++ style union as a column in MySQL

Question

Friends,

I have a strange need and cannot think my way through the problem. The great and mighty Google is of little help due to keyword recycling (as you'll see). Can you help?

What I want to do is store data of multiple types in a single column in MySQL.

This is the database equivalent to a C union (and if you search for MySQL and Union, you obviously get a whole bunch of stuff on the UNION keyword in SQL).

[Contrived and simplified case follows] So, let us say that we have people - who have names - and STORMTROOPERS - who have TK numbers. You cannot have BOTH a NAME and a TK number. You're either BOB SMITH -or- TK409.

In C I could express this as a union, like so:

union {
        char * name;
        int tkNo;
      } EmperialPersonnelRecord;

This makes it so that I am either storing a pointer to a char array or an ID in the type EmperialPersonnelRecord, but not both.

I am looking for a MySQL equivalent on a column. My column would store either an int, double, or varchar(255) (or whatever combination). But would only take up the space of the largest element.

Is this possible?

(of course anything is possible given enough time, money and will - I mean is it possible if I am poor, lazy and on a deadline... aka "out of the box")

Answer 1

As a1ex07 said, you CAN do it by storing string representation. But if you are worried about space, storing real values in several NULLable columns will probably save more space.

Alternately, create ancillary tables and normalize, e.g.

Your want:

TABLE1
|id|name_or_TK#|

Your can do:

TABLE1
|id|name|TK|

or you can do

TABLE1
|id|ST_or_human_flag|other columns common to humans and stormtroopers

TABLE2 - Names_of_humans
|id|name|

TABLE3 - TKs_of_STs
|id|TK|

Answer 2

No, there is no 'union' column type. But you can create a column that is big enough to hold the largest element and another column that works as type indicator. I.e.

... data VARCHAR(15), data_type enum('int','double','char')...