MySQL：删除重复记录、插入不重复记录

以表test为例

删除重复记录

查询link字段值相同的重复记录

SELECT *
FROM test
WHERE link IN (
		SELECT link FROM test
		GROUP BY link 
		HAVING count(link)>1
		)
1
2
3
4
5
6
7

删除重复记录

# 删除重复记录并保留id最小的记录
DELETE 
FROM test
WHERE link IN (
	SELECT * FROM (
		SELECT link FROM test
		GROUP BY link 
		HAVING count(link)>1) a
	)
AND id NOT IN (
	SELECT * FROM (
		SELECT min(id) FROM test
		GROUP BY link
		HAVING count(link)>1) a
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

错误处理：
1/ 错误1093
Error Code: 1093. You can’t specify target table ‘test’ for update in FROM clause 0.000 sec
删除重复记录时，遇到错误：You can’t specify target table ‘表名’ for update in FROM clause。
原因是，在mySQL中，不能查询引用表的同时更新该表。

这样写会出错 1093

DELETE
FROM test
WHERE link IN (
		SELECT link FROM test
		GROUP BY link 
		HAVING count(link)>1
		)
1
2
3
4
5
6
7

2/ 出现错误 1175
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec
没有主键或unique index 唯一约束，会出现这种情况。
按照提示，关掉safe mode 就行。
workbench 中，关闭safe mode的步骤如下：
EDIT - Preference - SQL Editor - Safe Updates选项关掉（不勾选）
修改完后重新连接数据库使之生效。

参考1：https://www.cnblogs.com/jiangxiaobo/p/6589541.html
参考2：mysql 语法参考 https://www.w3schools.cn/mysql/

插入记录（不重复）

先检查新记录是否和已有数据重复，再决定是否插入。

插入记录
检查link的值是否和之前的记录重复，再确定是否插入新值。

INSERT INTO temp (link, title) 
	SELECT "http://abc.cn", "test"
	FROM dual
	WHERE NOT EXISTS (SELECT * FROM test
                 WHERE link="http://abc.cn");
1
2
3
4
5

1、对于字段有唯一性约束的情形

字段title设置unique index约束，保证记录唯一。

Unique index和主键的区别。都是唯一，差别在于主键必须非空，Unique index的记录可以是空值，只是不能重复。

插入新值时

INSERT INTO temp (link, title) VALUES("abc.net","tom");
1

如果已有tom，则提示出错。

此时可用

INSERT IGNORE INTO temp (link, name) VALUES("abc.net","tom");
1

不提示出错，也不插入这个重复的值。

适合多个值输入的情况。

插入多个值

INSERT IGNORE INTO temp (link, title) VALUES("abc.net","tom"),
											("abc.net","alex");
1
2

2、对于字段没有唯一性约束的情形
通常做主键的字段和存储数据的字段分来，不混用。因此，通常也不把存储URL的字段，用来做主键。
此外，Longtext 类型的长度不固定，不能用来做主键。而URL一般用Longtext来存储，这也是URL不能用作主键的原因。
对于URL另一种方式，就是新建字段存储URL的HASH值，例如URL_Hash=md5(URL). HASH后是固定字段，可以来作为主键。

test表的例子中，假若 title、url都没有唯一性约束。
ID是auto increment，如果没有其他插入值有唯一性约束，则INSERT IGNORE INTO 无法起作用。

这里换一种方式：

# 检查link的值是否和之前的记录重复，再确定是否插入新值。ignore 是用主键判断的时候起作用，此处可以不用。
INSERT INTO temp (link, title) 
	SELECT "http://abc.cn", "test"
	FROM dual
	WHERE NOT EXISTS (SELECT * FROM test
                 WHERE link="http://abc.cn");
1
2
3
4
5
6

3、插入值为参数引入的情况；另一个例子

更新后：判断重复后插入
注意，select后不能加括号
str1 是新值之title；str2是新值之link
python中，构建sql语句; 其中’'是连接多行字符串用。

sql="INSERT IGNORE INTO test (title, link) \
	SELECT \"%s\",\"%s\" \
	FROM dual \
	WHERE NOT EXISTS (SELECT * FROM test \
	    WHERE link=\"%s\")" % (str1, str2, str2)
1
2
3
4
5