删除具有 blob 文本/中文本 mysql 的重复行 [英] delete duplicate rows that have blob text / mediumtext mysql

问题描述

我已经看到很多关于使用 sql 命令删除行的帖子，但我需要过滤掉具有 mediumtext 的行.

我不断收到错误 Error Code: 1170. BLOB/TEXT column used in key specification without a key length 来自解决方案，例如:

I have seen lots of posts on deleting rows using sql commands but i need to filter out rows which have mediumtext.

I keep getting an error Error Code: 1170. BLOB/TEXT column used in key specification without a key length from solution such as:

ALTER IGNORE TABLE foobar ADD UNIQUE (title, SID)

我的表很简单，我需要检查 mytext 中的重复项，id 是唯一的，它们是 AUTO_INCREMENT.
请注意，该表有大约一百万行，所有尝试都会超时.我需要一个分批执行操作的解决方案，例如 WHERE id>0 AND id<100
另外我在亚马逊上使用 MySQL Workbench RDS

My table is simple, i need to check for duplicates in mytext, id is unique and they are AUTO_INCREMENT.
As a note, the table has about a million rows, and all attempts keep timing out. I would need a solution that performs actions in batches such as WHERE id>0 AND id<100
Also I am using MySQL Workbench on amazons RDS

来自这样的表格

+---+-----+-----+------+-------+
|id |fname|lname|mytext|morevar|
|---|-----|-----|------|-------|
| 1 | joe | min | abc  | 123   |
| 2 | joe | min | abc  | 123   |
| 3 | mar | kam | def  | 789   |
| 4 | kel | smi | ghi  | 456   |
+------------------------------+ 

我想要一张这样的桌子

+---+-----+-----+------+-------+
|id |fname|lname|mytext|morevar|
|---|-----|-----|------|-------|
| 1 | joe | min | abc  | 123   |
| 3 | mar | kam | def  | 789   |
| 4 | kel | smi | ghi  | 456   |
+------------------------------+    

update 忘了提及这是在 amazon RDS 上使用 mysql workbench
我的表非常大，我不断收到错误 Error Code: 1205. Lock wait timeout exceeded 从这个 sql 命令:

update forgot to mention this is on amazon RDS using mysql workbench
my table is very large and i keep getting an error Error Code: 1205. Lock wait timeout exceeded from this sql command:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

另外，如果其他人遇到 MySQL workbench 超时问题，修复是
转到首选项 ->SQL 编辑器并将此参数设置为更大的值:DBMS 连接读取超时(以秒为单位)

Also, if anyone else is having issues with MySQL workbench timing out the fix is
Go to Preferences -> SQL Editor and set to a bigger value this parameter: DBMS connection read time out (in seconds)

推荐答案

OPTION #1: 删除所有重复记录，留下一个(例如具有 max(id) 的记录)

OPTION #1: Delete all duplicates records leaving one of each (e.g. the one with max(id))

DELETE
FROM yourTable
WHERE id NOT IN
(
   SELECT MAX(id)
   FROM yourTable
   GROUP BY mytext
)

您可能更喜欢使用 min(id).

You could prefer using min(id).

根据所使用的引擎，这将不起作用，并且会为您提供错误代码:1093.您不能在 FROM 子句中指定目标表yourTable"进行更新.为什么?因为删除一条记录可能会导致某些事情发生，从而使 WHERE 条件为 FALSE，即 max(id) 更改了值.

Depending on the engine used, this won't work and, as it did, give you the Error Code: 1093. You can't specify target table 'yourTable' for update in FROM clause. Why? Because deleting one record may cause something to happen which made the WHERE condition FALSE, i.e. max(id) changes the value.

在这种情况下，您可以尝试使用另一个子查询作为临时表:

In this case, you could try using another subquery as a temporary table:

DELETE
FROM yourTable
WHERE id NOT IN
(
    SELECT MAXID FROM
    (
        SELECT MAX(id) as MAXID
        FROM yourTable
        GROUP BY mytext
    ) as temp_table
)

选项 #2: 使用类似于 这个 示例或:

OPTION #2: Use a temporary table like in this example or:

首先，创建一个具有最大 id 的临时表:

First, create a temp table with the max ids:

   SELECT MAX(id) AS MAXID
   INTO tmpTable
   FROM yourTable
   GROUP BY mytext;

然后执行删除:

DELETE
FROM yourTable
WHERE id NOT IN
(
    SELECT MAXID FROM tmpTable
);

这篇关于删除具有 blob 文本/中文本 mysql 的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！