MySQL/JDBC 中的动态 ENUM

发布于 2024-11-04 02:14:13 字数 341 浏览 1 评论 0原文

我即将为日志系统设计一个数据库。

许多字符串列的值数量有限，但事先并不知道：例如发送消息的模块的名称或源主机名。

我想将它们存储为 MySQL 枚举以节省空间。因此，我们的想法是，枚举会随着遇到新值而增长。

我将从这样的列开始：

host ENUM('localhost')

然后，在 Java 中，我会在启动时一次加载为主机名定义的枚举值（我如何使用 MySQL/JDBC 做到这一点？？），并且每当我遇到新主人。

您认为这是可行的/好主意吗？你曾经做过类似的事情吗？

预先感谢您的建议。

拉斐尔

原文

I am about to design a database for a logging system.

Many of the String columns will have a limited amount of values, but not known in advance:
Like the name of the modules sending the messages, or the source hostnames.

I would like to store them as MySQL Enums to save space.
So the idea would be that the Enums grow as they encounter new values.

I would start with a column like :

host ENUM('localhost')

Then, in Java, I would load on startup the enum values defined for the hostnames at a time (how do I do that with MySQL/JDBC ??), and I would alter the Enum whenever I encounter a new host.

Do you think it is feasible / a good idea ?
Have you ever done something like that ?

Thanks in advance for your advice.

Raphael

分享到QQ

分享到微博

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

发布评论

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

つ低調成傷 2024-11-11 02:14:13

这不是一个好主意。 ENUM 不是为此而设计的。
您可以创建单独的表（host_id，host_name）并在主表中使用引用。示例：

CREATE TABLE `host` (
    `host_id` INT(10) NOT NULL AUTO_INCREMENT,
    `host_name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`host_id`)
)

CREATE TABLE `log` (
    `log_id` INT(10) NOT NULL AUTO_INCREMENT,
    `host_id` INT(10) NULL DEFAULT NULL,
    ...
    PRIMARY KEY (`log_id`),
    INDEX `FK__host` (`host_id`),
    CONSTRAINT `FK__host` FOREIGN KEY (`host_id`) REFERENCES `host` (`host_id`) ON UPDATE CASCADE ON DELETE CASCADE
)

UPD：

我认为存储主机的最佳方法是 varchar/text 字段。这是最简单、最快的方法。我想你不用担心空间问题。

尽管如此。

对主机使用第二个表会减少大小，但会使写入日志变得复杂。使用 ENUM 会使写入变得复杂并显着降低性能。

This is a not good idea. ENUM designed not for that.
You can just create separate table (host_id, host_name) and use refference in main table. Example:

CREATE TABLE `host` (
    `host_id` INT(10) NOT NULL AUTO_INCREMENT,
    `host_name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`host_id`)
)

CREATE TABLE `log` (
    `log_id` INT(10) NOT NULL AUTO_INCREMENT,
    `host_id` INT(10) NULL DEFAULT NULL,
    ...
    PRIMARY KEY (`log_id`),
    INDEX `FK__host` (`host_id`),
    CONSTRAINT `FK__host` FOREIGN KEY (`host_id`) REFERENCES `host` (`host_id`) ON UPDATE CASCADE ON DELETE CASCADE
)

UPD:

I think the best way to storing host is varchar/text field. It is easiest and fastest way. I think you need not worry about the space.

Nonetheless.

Using the second table for hosts will reduce the size, but will complicate writing logs. Using ENUM complicate writing and significantly reduce the performance.

回复收藏 0 原文

~没有更多了~