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

Question

Friends,

I have a strange need, and I can’t decide my way. The great and powerful Google helps a little because of the recycling of keywords (as you will see). You can help?

What I want to do is store data of several types in one column in MySQL.

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

[The manifested and simplified case follows] So, let's say we have people who have names - and STORMTROOPERS - who have TK numbers. You cannot have a NAME and TK number. You are either BOB SMITH or TK409.

In C, I could express this as a union, for example:

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

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

I am looking for the MySQL equivalent in a column. Int, double or varchar (255) (or any combination) will be stored in my column. But it will occupy only the space of the largest element.

Is it possible?

(of course, everything is possible, given enough time, money and will - I mean, is it possible that I am poor, lazy and in the deadline ... otherwise "out of the box")

Answer 1

As a1ex07 said, you can do this by storing the string representation. But if you're worried about space, storing real values ​​in multiple NULLable columns will probably save more space.

Alternatively, create helper tables and normalize, for example.

Your wish:

  TABLE1
 | id | name_or_TK # |

Your way:

  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, the column type "union" is missing. But you can create a column that is large enough to contain the largest element and another column that works as a type indicator. I.e.

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

Answer 3

C connections are a fairly simple way to solve this problem.

You have a polymorphic data type. Thus, one way to solve the problem is to switch to an object-oriented database or dynamically typed, for example, some from "NoSQL".

If you need to stay with an existing relational database, you can do the standard thing, which is to create some kind of ORM - an object-relational cartographer - for translation. One way is to put common ("base class") fields in the main table with the type column, and then use the type column to select which sheet-to-sheet table contains additional fields. For example:

 table Employee field id int field emp_type enum('human', 'stormtrooper') field salary int field division_id int field manager_id int table HumanEmployee field emp_id int field name string table StormtrooperEmployee field emp_id int field tk_number int 

That is, * Employee tables are mapped to the Employee base table by the employee ID.

Answer 4

I think you should have 2 different columns and store the data accordingly, when you extract, you can create and add them together as col1 + col2 as full_name