MySql主键> 900/1000 字节？

发布于 2024-07-22 15:00:24 字数 795 浏览 6 评论 0原文

我有一个复合主键，它加起来相当大（~2000 字节）。我没有考虑性能，我只是想要一个主键来强制唯一性。

MySql 不喜欢长主键。有没有解决的办法？也许只强制唯一性，而不建立索引？

我不想使用 ASCII 而不是 UTF8 只是为了启用主键（UTF8 字符占用 3 个字节）。

我的表定义如下：

CREATE TABLE `configuration` (
  `Section` varchar(200) NOT NULL,
  `StoredKey` VARCHAR(200) NOT NULL,
  `ServiceName` VARCHAR(300) NOT NULL,
  `ServiceMajorVersion` int unsigned NOT NULL,
  `ServiceMinorVersion` int unsigned NOT NULL,
  `ServiceInstanceID` VARCHAR(100) NOT NULL,
  `StoredValue` VARCHAR(1024)

 , PRIMARY KEY (`Section`, `StoredKey`, `ServiceName`, `ServiceMajorVersion`, `ServiceMinorVersion`, `ServiceID`)   
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

原文

I have a composite primary key, which together turns out to be rather large (~2000 bytes). I have no performance considerations, I just would like a primary key to enforce uniqueness.

MySql doesn't like long primary keys. Is there a way around this? Perhaps to only enforce uniqueness, without building an index?

I wouldn't want to use ASCII instead of UTF8 just to enable a primary key (UTF8 character takes 3 bytes).

My table is defined as follows:

CREATE TABLE `configuration` (
  `Section` varchar(200) NOT NULL,
  `StoredKey` VARCHAR(200) NOT NULL,
  `ServiceName` VARCHAR(300) NOT NULL,
  `ServiceMajorVersion` int unsigned NOT NULL,
  `ServiceMinorVersion` int unsigned NOT NULL,
  `ServiceInstanceID` VARCHAR(100) NOT NULL,
  `StoredValue` VARCHAR(1024)

 , PRIMARY KEY (`Section`, `StoredKey`, `ServiceName`, `ServiceMajorVersion`, `ServiceMinorVersion`, `ServiceID`)   
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

分享到QQ

分享到微博

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

发布评论

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

鸢与 2024-07-29 15:00:24

你应该读一些关于数据库结构设计的书，那么你就不会有带有这样主键的表。或者雇用我为您创建数据库结构（原型）的人。
这只是友好的建议。

回复收藏 0 原文

为你拒绝所有暧昧 2024-07-29 15:00:24

@porneL 对于这种情况有正确的答案，但是 @Cade Roux 和 @noonex 也是正确的：数据库不应该被使用就像 Excel 一样。

您应该有辅助表：

CREATE TABLE ServiceInstance (
    ID int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Hash binary(16) NOT NULL,
    ServiceInstanceID varchar(100) NOT NULL,
    UNIQUE(Hash)
);

对于每个表，除了每个配置行唯一的数据之外。

当您插入时，执行以下操作：

INSERT INTO ServiceInstance (Hash, ServiceInstanceID) VALUES (unhex(md5('whatever')), 'whatever');

然后，您的主表将变为：

CREATE TABLE `configuration` (
    `Section_ID`          int unsigned NOT NULL,
    `StoredKey_ID`        int unsigned NOT NULL,
    `ServiceName_ID`      int unsigned NOT NULL,
    `ServiceMajorVersion` int unsigned NOT NULL,
    `ServiceMinorVersion` int unsigned NOT NULL,
    `ServiceInstanceID`   int unsigned NOT NULL,
    `StoredValue`         VARCHAR(1024),
    UNIQUE (`Section_ID`, `StoredKey_ID`, `ServiceName_ID`, `ServiceMajorVersion`, `ServiceMinorVersion`, `ServiceInstanceID`)   
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

使用 UNIQUE 键，因为当您始终通过主键访问行时，通常会使用 PRIMARY KEY。如果只是一个约束，请改用 UNIQUE。

@porneL has the correct answer for this case, however, @Cade Roux and @noonex are also correct: Databases are not meant to be used like Excel.

You should have secondary tables:

CREATE TABLE ServiceInstance (
    ID int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Hash binary(16) NOT NULL,
    ServiceInstanceID varchar(100) NOT NULL,
    UNIQUE(Hash)
);

For each table except data which will be unique per configuration line.

When you insert, do:

INSERT INTO ServiceInstance (Hash, ServiceInstanceID) VALUES (unhex(md5('whatever')), 'whatever');

Then, your primary table becomes:

CREATE TABLE `configuration` (
    `Section_ID`          int unsigned NOT NULL,
    `StoredKey_ID`        int unsigned NOT NULL,
    `ServiceName_ID`      int unsigned NOT NULL,
    `ServiceMajorVersion` int unsigned NOT NULL,
    `ServiceMinorVersion` int unsigned NOT NULL,
    `ServiceInstanceID`   int unsigned NOT NULL,
    `StoredValue`         VARCHAR(1024),
    UNIQUE (`Section_ID`, `StoredKey_ID`, `ServiceName_ID`, `ServiceMajorVersion`, `ServiceMinorVersion`, `ServiceInstanceID`)   
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Use the UNIQUE key instead, as PRIMARY KEYs are typically used when you will be accessing the rows by the primary key always. If it's just a constraint, use UNIQUE instead.

回复收藏 0 原文