MySQL:如何使表中多行的数据唯一?
我将如何在 phpMyAdmin 中执行以下操作(仅对如何执行 UNIQUE(标题,描述)感兴趣)?:
CREATE TABLE `myData` (
id serial primary key,
title varchar(255) not null,
description varchar(255) not null,
UNIQUE (title, description)
);
顺便说一句,为了不提出另一个问题,我想知道我可以将哪些其他类型用于长字符串吗?因为 225 个字符对于我们的目的来说太小了。尽管我知道从 MySQL 5.0.3 开始,varchar 最多可以接受 65,535 个字符。但是我可以将这些字段指定为 MEDIUMTEXT (为了兼容性,我的客户希望这些字段如此)吗?当我这样做时,我收到一个错误,因为“BLOB/TEXT 列...在没有密钥长度的密钥规范中使用”。那我该怎么办?我需要这两个字段是唯一的 - 这样即使描述不同,我也能够存储类似的标题,但是当标题和描述与传入数据的标题和描述相匹配时,我不希望存储此传入数据。
How would I do the following (interested only in how to do UNIQUE (title, description)) in phpMyAdmin?:
CREATE TABLE `myData` (
id serial primary key,
title varchar(255) not null,
description varchar(255) not null,
UNIQUE (title, description)
);
And by the way, so as not to open another question on SO, I would like to know what other types I can use for long strings? Because 225 characters are too small for our purposes. Though I know that varchar can accept up to 65,535 characters as of MySQL 5.0.3. But could I specify those fields as MEDIUMTEXT (my client wants those to be as such for compatibility sake)? When I do so I get an error because "BLOB/TEXT column ... used in key specification without a key length". What should I do then? I need those two fields to be unique - so that I would be able to store similar titles even when descriptions are different, but when there are titles and descriptions that match titles and descriptions of incoming data, I wish not to store this incoming data then.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
对于
MyISAM
,默认最大密钥长度为1000
字节;对于InnoDB
,默认最大密钥长度为3072
字节(另外要求中的每一列密钥不应超过767
字节)。如果您的表是单字节编码的
InnoDB
或MyISAM
,那么您可以创建约束。如果不是,那么您可以创建一个前缀索引,但请注意该索引只会保持前缀内的唯一性。
然而,最好对标题和描述进行哈希处理,并将哈希值存储在唯一的列中。
Default maximum key length is
1000
bytes forMyISAM
and3072
bytes forInnoDB
(with additional requirement that each column in the key should not exceed767
bytes).If your table is
InnoDB
orMyISAM
with a single-byte encoding, then you can create the constraint alright.If it's not, then you can create a prefixed index, but be aware that the index will only maintain uniqueness within the prefix.
It will be much better, however, that you hash your title and description and store the hash in a unique column instead.
MySQL 将存储标题和描述的完整信息,但它只会索引这些字段中的前 255 个字符。这意味着您输入 2 个包含 255 个相同字符的标题,然后输入不同的内容 - 这会给您带来双重错误。
MySQL will store full information from title and description, but it will index only 255 first characters from those fields. Which means that of you type 2 titles of 255 same characters and then something different - it will give you an error of doubling.
要使用 PhpMyAdmin 创建 UNIQUE(我希望这是您的问题;)),请执行以下操作:
导航到表并选择“结构”,然后添加超过 2 行的索引(左下角),选择两个字段和“indextyp:独一无二”,你就完成了。
编辑:
添加了屏幕截图,它是德语,但对您来说应该看起来一样。我真的希望这能解决问题,如果没有,我根本不理解你的问题和你所做的所有评论。
http://dl.dropbox.com/u/3615626/stackoverflow/ phpmyadmin-step1.png
http://dl.dropbox .com/u/3615626/stackoverflow/phpmyadmin-step2.png
它被归档在“index”下,因为“unique”只是一种特殊类型的索引。
To create the UNIQUE using PhpMyAdmin (i hope this was your question ;) ) do the following:
Navigate to the Table and select "structure", then add an index over 2 rows (bottom left), select the two fields and "indextyp: unique" and your done.
Edit:
Added Screenshots, it's german but it should look the same to you. I really hope that does the trick, if not i didn't understand your question and all the comments you made at all.
http://dl.dropbox.com/u/3615626/stackoverflow/phpmyadmin-step1.png
http://dl.dropbox.com/u/3615626/stackoverflow/phpmyadmin-step2.png
it is filed under "index" because "unique" is just a special type of index.
如果您已经创建了表,请转到 PhpMyAdmin 中的结构,选择左侧带有复选框的两列(标题、描述)。然后单击表说明下控件行中的 U 图标。这将在两个选定字段上添加唯一索引。
Quassnoi 关于索引长度和使用哈希替代方案的评论是正确的。另请注意,UTF-8 文本列在索引中每个字符需要三个字节,因此索引 UTF-8 文本列的开销相当大。
If you have the table created already, go to the structure for it in PhpMyAdmin, select the two columns (title, description) with the checkboxes on the left. then click the U icon in the row of controls under the table description. That will add a unique index over the two selected fields.
Quassnoi's comments about index length and using a hash alternative are spot on. Also note that UTF-8 text columns require three bytes per character in the indexes, so indexing UTF-8 text columns is quite expensive.