数据库查询重复行shell脚本

2014-01-09

有时候表里会存在重复行。

1、oracle查询：
cat get_repeat_id.sh
#!/bin/bash
echo -e "select user_name from customer group by user_name having count(user_name) > 1;"|sqlplus -s 'user01/p123456'

根据查询得到的结果删除：

delete from customer a where a.user_name in "结果" and a.rowid not in (select min(b.rowid) from customer b group by b.user_name having count(user_name) > 1)

2、mysql实例：
#!/bin/bash

file=$1

if [ -z "$1" ];then
echo "Usage: $0 file"
exit 1;
fi

for id in `cat ${file}`;do
mysql -uroot -p123456 -Dmd5 -e "insert into md5 (str,value) values ('${id}',md5('${id}'));"
done

mysql -uroot -p123456 -Dmd5 -e 'select b.id from md5 b group by b.value having count(b.id) > 1' | tail -n +2 > repeat.txt

#将重复的id剔除
for id1 in `cat repeat.txt`;do
mysql -uroot -p123456 -Dmd5 -e "delete from md5 where id='${id1}'"
done

#将重复的id重新insert一次
for id2 in `cat repeat.txt`;do
mysql -uroot -p123456 -Dmd5 -e "insert into md5 (str,value) values ('${id2}',md5('${id2}'));"
done

分类：数据库 | 标签： |

相关日志

评论被关闭！