实施 ac/c++样式联合作为 MySQL 中的列

发布于 2024-08-25 08:34:55 字数 708 浏览 3 评论 0原文

朋友们，

我有一个奇怪的需求，无法思考解决问题的方法。由于关键词的循环利用，伟大而强大的谷歌几乎没有什么帮助（正如你将看到的）。你能帮忙吗？

我想要做的是将多种类型的数据存储在MySQL的单个列中。

这是相当于 C union 的数据库（如果你搜索 MySQL 和 Union，你显然会得到一大堆有关 SQL 中 UNION 关键字的内容）。

[以下是人为和简化的情况] 因此，让我们说我们有一些人 - 有名字 - 和突击队员 - 有 TK 号码。您不能同时拥有姓名和 TK 号码。你要么是鲍勃·史密斯，要么是 TK409。

在 CI 中可以将其表示为联合，如下所示：

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

这使得我可以在 EmperialPersonnelRecord 类型中存储指向 char 数组的指针或 ID，但不能同时存储两者。

我正在寻找一个列上的 MySQL 等效项。我的列将存储 int、double 或 varchar(255)（或任何组合）。但只会占用最大元素的空间。

这可能吗？

（当然，只要有足够的时间、金钱和意愿，一切皆有可能——我的意思是，如果我很穷、很懒并且在截止日期前完成，这是否可能......又名“开箱即用”）

原文

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")

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海的爱人是光 2024-09-01 08:34:55

正如 a1ex07 所说，您可以通过存储字符串表示来做到这一点。但如果您担心空间问题，则将实际值存储在多个可为 NULL 的列中可能会节省更多空间。

或者，创建辅助表并标准化，例如

您想要：

TABLE1
|id|name_or_TK#|

您可以做：

TABLE1
|id|name|TK|

或者您可以做

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|

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|

回复收藏 0 原文

反差帅 2024-09-01 08:34:55

不，没有“联合”列类型。但是您可以创建一个足够大的列来容纳最大的元素，并创建另一个用作类型指示器的列。 IE

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

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')...

回复收藏 0 原文

红颜悴 2024-09-01 08:34:55

C 联合是处理这个问题的一种相当笨拙的方法。

你所拥有的是多态数据类型。因此，解决该问题的一种方法是改用面向对象的数据库，或者动态类型的数据库，例如某些“NoSQL”数据库。

如果您必须继续使用当前的关系数据库，您可以执行标准操作，即构建某种 ORM（对象关系映射器）来进行转换。一种方法是将公共（“基类”）字段与类型列一起放入主表中，然后使用类型列来选择哪个“叶类”表保存附加字段。例如：

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

也就是说，*Employee 表通过员工 ID 绑定回基本 Employee 表。

C unions are a pretty bit-twiddly way of handling this problem.

What you have there is a polymorphic data type. So, one way to solve the problem is to switch to an object-oriented DB, or one that is dynamically typed, like some of the "NoSQL" ones.

If you have to stay with the current relational DB, you can do the standard thing which is to build some kind of ORM -- object-relational mapper -- to do the translation. One way is to put the common ("base class") fields in the main table, along with a type column, then use the type column to select which "leaf class" table holds the additional fields. For instance:

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, the *Employee tables are tied back to the base Employee table by employee ID.

回复收藏 0 原文