mysql常用脚本

1.添加一列默认值0

ALTER TABLE 表名 ADD Sort int(11) DEFAULT 0 NULL COMMENT '排序';

2.MySql删除重复数据，保留最早创建的

　　思路：新建一个临时表temp_ID存储upload_file表里的重复Id，upload_file根据temp_ID里的Id删除重复数据，最后删除临时表temp_ID。

　　注：如果新建临时表

-- 手动新建一个含有Id字段的临时表temp_ID，插入数据（因为正式环境执行不了创建并插入所以手动建立了）
INSERT INTO temp_ID (Id)
( SELECT Id
FROM upload_file
WHERE FileCreateTime > '2024-05-01 16:57:26'
AND FileName IN (
SELECT FileName
FROM upload_file
WHERE FileCreateTime > '2024-05-01 16:57:26'
GROUP BY FileName
HAVING COUNT(*) > 1
)
AND FileCreateTime NOT IN (
SELECT MIN(FileCreateTime)
FROM archive_upload_file
WHERE FileCreateTime > '2024-05-01 16:57:26'
GROUP BY FileName
HAVING COUNT(*) > 1
)
);

-- 删除重复数据

DELETE FROM upload_file WHERE Id IN (SELECT Id FROM temp_ID);

-- 删除临时表

DROP TABLE IF EXISTS temp_ID;

3.MySql查找所有重复数据（单字段，显示重复数据的其中一列）

　　SELECT * FROM upload_file GROUP BY FileName HAVING COUNT(FileName)>1;

4.MySql查找所有重复的（单字段，显示所有数据）

　　SELECT * FROM upload_file WHERE FileName IN

　　(SELECT FileName FROM upload_file GROUP BY FileName HAVING COUNT(*) > 1) 

　　ORDER BY FileName;

5.MySql查找所有重复的（多字段，显示所有数据）

　　SELECT * FROM upload_file WHERE (FileName,FileSize) IN

　　(SELECT FileName,FileSize FROM upload_file GROUP BY FileName,FileSize HAVING COUNT(*) > 1) 

　　ORDER BY FileName,FileSize;

6.MySql查找所有重复的（多字段，不包括最早创建的一条）

　　SELECT * FROM upload_file WHERE (FileName,FileSize) IN

　　(SELECT FileName,FileSize FROM upload_file GROUP BY FileName,FileSize HAVING COUNT(*) > 1) 

　　AND UploadTime NOT IN (SELECT Min(UploadTime) FROM upload_file GROUP BY FileName,FileSize HAVING COUNT(*) > 1)
　　ORDER BYFileName,FileSize;