linux循环查询数据库数据库数据,数据库查询重复行shell脚本

数据库查询重复行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

相关日志