首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >基于长度较短的唯一索引的列删除重复项

问基于长度较短的唯一索引的列删除重复项
EN

Stack Overflow用户
提问于 2022-06-19 12:19:49
回答 1查看 35关注 0票数 0

我有下表:

代码语言:javascript
复制
CREATE TABLE `RU_sg1lib_MASTER` (
 `ID` int(15) unsigned NOT NULL AUTO_INCREMENT,
 `Title` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT '',
 `VolumeInfo` varchar(100) CHARACTER SET utf8 DEFAULT '',
 `Series` varchar(300) CHARACTER SET utf8 DEFAULT '',
 `Periodical` varchar(200) CHARACTER SET utf8 DEFAULT '',
 `Author` varchar(1000) CHARACTER SET utf8 DEFAULT '',
 `Year` varchar(14) CHARACTER SET utf8 DEFAULT '',
 `Edition` varchar(60) CHARACTER SET utf8 DEFAULT '',
 `Publisher` varchar(400) CHARACTER SET utf8 DEFAULT '',
 `City` varchar(100) CHARACTER SET utf8 DEFAULT '',
 `Pages` varchar(100) CHARACTER SET utf8 DEFAULT '',
 `PagesInFile` int(10) unsigned NOT NULL DEFAULT '0',
 `Language` varchar(150) CHARACTER SET utf8 DEFAULT '',
 `Topic` varchar(500) CHARACTER SET utf8 DEFAULT '',
 `Library` varchar(50) CHARACTER SET utf8 DEFAULT '',
 `Issue` varchar(100) CHARACTER SET utf8 DEFAULT '',
 `Identifier` varchar(300) CHARACTER SET utf8 DEFAULT '',
 `ISSN` varchar(9) CHARACTER SET utf8 DEFAULT '',
 `ASIN` varchar(200) CHARACTER SET utf8 DEFAULT '',
 `UDC` varchar(200) CHARACTER SET utf8 DEFAULT '',
 `LBC` varchar(200) CHARACTER SET utf8 DEFAULT '',
 `DDC` varchar(45) CHARACTER SET utf8 DEFAULT '',
 `LCC` varchar(45) CHARACTER SET utf8 DEFAULT '',
 `Doi` varchar(45) CHARACTER SET utf8 DEFAULT '',
 `Googlebookid` varchar(45) CHARACTER SET utf8 DEFAULT '',
 `OpenLibraryID` varchar(200) CHARACTER SET utf8 DEFAULT '',
 `Commentary` varchar(10000) CHARACTER SET utf8 DEFAULT '',
 `DPI` int(6) unsigned DEFAULT '0',
 `Color` varchar(1) CHARACTER SET utf8 DEFAULT '',
 `Cleaned` varchar(1) CHARACTER SET utf8 DEFAULT '',
 `Orientation` varchar(1) CHARACTER SET utf8 DEFAULT '',
 `Paginated` varchar(1) CHARACTER SET utf8 DEFAULT '',
 `Scanned` varchar(1) CHARACTER SET utf8 DEFAULT '',
 `Bookmarked` varchar(1) CHARACTER SET utf8 DEFAULT '',
 `Searchable` varchar(1) CHARACTER SET utf8 DEFAULT '',
 `Filesize` bigint(20) unsigned NOT NULL DEFAULT '0',
 `Extension` varchar(50) CHARACTER SET utf8 DEFAULT '',
 `MD5` char(32) CHARACTER SET utf8 DEFAULT '',
 `Generic` char(32) CHARACTER SET utf8 DEFAULT '',
 `Visible` char(3) CHARACTER SET utf8 DEFAULT '',
 `Locator` varchar(733) CHARACTER SET utf8 DEFAULT '',
 `Local` int(10) unsigned DEFAULT '0',
 `TimeAdded` timestamp NOT NULL DEFAULT '2000-01-01 13:00:00',
 `TimeLastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `Coverurl` varchar(200) CHARACTER SET utf8 DEFAULT '',
 `Tags` varchar(500) CHARACTER SET utf8 DEFAULT '',
 `IdentifierWODash` varchar(300) CHARACTER SET utf8 DEFAULT '',
 `Zstatus_retrieve_TOC` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `Zstatus_retrieve_classifybyoclc` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我从对数据的手动检查中了解到,Title列包含许多重复项。于是,我着手移除这些复制品。我是这样做的:

步骤1:使用temp将带有数据的表复制到另一个表( PHPMyadmin )

步骤2:截断表RU_sg1lib_MASTER

步骤3:在ID列上有一个主键索引。因此,我删除了它,并在Title上创建了一个主键(长度256)。由此产生的索引是:

主键(Title(256)),密钥classifybyoclc_respcode (classifybyoclc_respcode(256)),密钥classifybyoclc_calln_lcc (classifybyoclc_calln_lcc),

步骤4:将忽略插入到RU_sg1lib_MASTER中,选择*来自temp

当时,我对索引(特别是具有指定长度的索引)还没有完全的了解。最近,我突然意识到,由于Title索引指定了256个长度,它可能会导致一些不完全相同但具有相同初始字符的值被误认为是完全重复的,因此会被删除。

我说对了吗?

如果是,如何准确删除包含3+百万条记录的表的副本?

EN

回答 1

Stack Overflow用户

发布于 2022-06-19 14:28:59

是的,您是正确的:如果您指定了PRIMARY KEY (Title(256)),那么Title列在前256个字符中必须是唯一的,并且您将删除其Title列在前256个字符中相同但在后续字符位置上不同的行。顺便说一句,没有必要改变你的主键。您可以尝试将列ID保留为主键,而不是为列title定义唯一索引,而不指定显式长度。但是,由于对您可以拥有的最大索引大小的限制,这可能会失败,默认情况下,MySQL的索引大小为767字节(而不是字符),即使对于MySQL 8也是如此。

最大索引长度为767字节(对于使用动态或压缩行格式的InnoDB表,则为3072字节)。列Title指定了utf8mb4_unicode_ci的排序规则,这意味着一个字符最多需要4个字节来存储,因此您可以拥有的最大索引是utf8mb4_unicode_ci(767/4)或191个字符(对于使用动态或压缩行格式的InnoDB表,有768个字符)。因此,如果有任何长度大于191 (或768)的Title列值,则不能为整列指定索引。否则,您可以重新定义列的长度为191 (或768),您的问题就解决了。

因此,假设Title列的长度大于可以拥有的最大索引大小的行,则需要另一种方法来确保唯一性,因为指定唯一键不再是一个选项。您可以按照Akina的建议发出DELETE语句。

但是,问题仍然是确保将来不存在重复的Title列。这将要求您首先查询表,以便在进行插入或修改Title列值的更新时,查看是否已经存在带有给定Title值的行。还可以通过定义“后”、“插入”和“更新后”触发器来自动完成此操作。但是,由于Title列是取消索引的,所以必须读取每一行以确定是否存在唯一性。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/72676825

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档