MySQL中是否有一种方法可以影响用户定义的数据类型？

Question

我有一个数据库，其中存储了以下信息（除其他内容外）：

- 硬件ID BIGINT - 存储容量 BIGINT - 硬件名称 VARCHAR - 全球唯一端口名 VARCHAR 我想要更精细地定义这些数据类型。例如，硬件ID没有数值意义，因此在显示时不需要考虑其格式。但是存储容量是基数数字，如果用户请求，我希望以千位分隔符和小数点呈现，例如123,456.789。因此，我想将BIGINT细化为ID_NUMBER和CARDINAL。

同样的，硬件名称是简单的文本，而WWPNs是十六进制字符串，例如24:68:AC:E0。因此，我想将VARCHAR细化为ENGLISH_WORD和HEXSTRING。

我想把所有这些信息放在一个地方，并想知道是否有好的方法在MySQL表定义中保存它们。我可以使用表定义的注释字段，但我认为那样做有问题。

一种方法是在其他地方定义数据结构，然后使用该定义来生成我的CREATE TABLE，但这将是我目前拥有的代码的重大改造，因此我正在寻找替代方案。

有什么建议吗？如果有的话，使用的应用程序语言是Perl。

- Dancrumb

Answer 1

一个好的方法是使用视图。例如，要在基数中插入逗号，您可以使用：

mysql> create table foo (id int);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into foo (id) values ( 123456789);
Query OK, 1 row affected (0.00 sec)

mysql> create view v_foo as select format(id, 0) as id from foo;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from v_foo;
+---------------+
| id            |
+---------------+
| 123,456,789   |
+---------------+
1 row in set (0.02 sec)

您可以使用其他字符串函数格式化其他字段，并将它们存储在视图定义中。

- friedo

Answer 2

我将提出一个回答来质疑这个问题。

数据库建模人员喜欢反复强调的口头禅之一是分离表示层（格式化）和数据，我相信like中相关部分的意思是：

“你不应该在数据库中存储格式化数据，也不应该歧视任何格式选择。你应该使用本地支持的数据类型存储数据。你的应用程序应该提供表示层并格式化你的列。”

好吧，friedo的回答并没有直接违反这个原则——数据只通过视图呈现，存储仍然是本地的。

但是，这取决于你如何定义表示层——如果视图和服务器设置被认为是表示层的一部分，那么一切都很好，否则可能会有潜在的麻烦，因为作为你系统的可能用户，我将无法指定我的千位分隔符是单引号（至少在我目前居住的地方是这样的）。

另外，一旦你走上这条路，你认为需要多长时间才能处理从文本重新解析数据回到数字的请求，并可能最终陷入模棱两可的情况（例如DD/MM/YY与MM/DD/YY）？

以上抱怨仅涉及格式，确定小数位数定义了您的数据域，这是一件好事，因为它限制了不一致的数据进入数据库的可能性。

编辑：（进一步考虑纯粹主义观点，关于数字基数） 说十六进制数字数据在其他基数中没有意义通常是错误的陈述。 数字值没有基数，可以用任何基数表示。它们的域（允许的值集）相同。

选择十六进制作为MAC地址的方式是由于历史原因和易于以该格式读取供应商部分等原因而自然发生的。 IPv4地址的“有趣”格式的选择是一个具有历史意义的选择，可能有轶事的原因。

但两者都只是一种选择，在内部，一个好的系统将无偏见地存储它们（例如将IPv4存储为文本不是一件好事）。当RDBMS向您呈现查询结果（在屏幕上）时，它已经扮演了一个应用程序的角色，并以某种方式格式化结果。

这并不重要，你在应用程序中使用的格式不应影响你存储存储容量或其他实体属性的方式。

所以我想说的是，这是应用程序配置数据（核心日期的元数据），当然可以/应该存储在数据库中，但是使用MySQL（定义自定义类型不太丰富）无法适合表定义，应该简单地存储在另一个表中，应用程序将读取并应用于您的列，以便在向用户呈现数据时，而不是以某种不可移植的方式。

例如，视图的想法很好，但是您能轻松查询视图以获取应用于字段的格式吗？或者说，假设您想更改所有使用它的查询中字段WWPN的格式，在所有出现的情况下都可以更改格式（十六进制字符串也听起来已经错误了），那么这是否容易？或者如果有其他查询转换数据并将其写入另一个表中，您会将其写入应用格式还是不带格式（重新解析）？等等...

现在，如果您有一个存储应用程序配置数据的表，例如 FieldFormatting： 表，字段，格式，检查规则，长格式（或在您的情况下最有意义的内容） 那么上述问题就变得更容易处理，并且您可以为应用程序和业务逻辑选择额外选项。

如果你真的（真的，真的）必须直接访问数据库，并且本机类型会使数据对用户不可读，而你必须预格式化，那么你甚至可以使用上面的表格来半自动地生成和更新视图/查询。

注意：我在这里采取了一种纯粹主义的观点，因为我感觉你在做设计决策，而不是追求性能或方便（例如应用程序数据类型和数据库数据类型之间）当实际问题比建模指南和规则更重要时。但是最后一段的问题仍然存在。

- Unreason

---

也许我误解了你在这里说的话，但我认为我们是意见一致的。我的问题是MySQL提供的数据类型太广泛了。例如，VARCHAR类型存在“别名”元素。我需要表示文本和十六进制数字。这些十六进制数字在任何其他数值基础上都没有意义（类似于网络MAC地址）。我所尝试做的是将数据的呈现与数据库分离，但是在数据库中提供足够的信息以允许正确的格式选择。 - Dancrumb

我们追求的目标是一样的。请记住，即使我持有一个观点，我认为Friedo的答案在它所涉及的层次上是一个好答案——对于一个简单情境的简单解决方案，并且意识到它的限制不会造成任何伤害。我只是试图提供更大的画面，这可能或可能不适用于特定情况。 - Unreason

@goran；感谢您的回答和跟进回复。我认为这是一个有趣的问题...但我希望SO社区能有更多的回应。话虽如此，我非常感谢您在这里的贡献。 - Dancrumb