对比2个表mailbox_id字段的差集，并导出。然后利用循环insert插入。
|
1
2
3
4
5
6
7
8
9
|
kkmlmysqlpass=$(
cat
/usr/local/kk-ml/config/custom
.conf |
grep
-w
"pass"
|
awk
-F
" "
'{print $NF}'
)
/usr/local/kk-ml/service/mysql/bin/mysql
-u kkml -p$kkmlmysqlpass kkml -e
"select * from core_mailbox where mailbox_id not in (select mailbox_id from co_user);"
>
/root/chaji
.txt
cat
/root/test
.txt |
awk
-F
" "
'{print $1"\t"$2}'
|
sed
1d >
/root/chaji_id
.txt
cat
/root/chaji_id
.txt |
while
read
LINE
do
mailbox_id=$(
echo
$LINE |
awk
'{print $1}'
)
domain_id=$(
echo
$LINE |
awk
'{print $2}'
)
/usr/local/kk-ml/service/mysql/bin/mysql
-u kkml -p$kkmlmysqlpass kkml -e
"insert into co_user(mailbox_id,domain_id,realname,engname,oabshow,showorder,eenumber,gender,birthday,homepage,tel_mobile,tel_home,tel_work,tel_work_ext,tel_group,im_qq,im_msn,addr_country,addr_state,addr_city,addr_address,addr_zip,remark,last_session,last_login,openid,unionid,wx_id) values('$mailbox_id','$domain_id','testdel2','NULL','1','0','NULL','male','0000-00-00','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','0','0','0');"
done
|
备注（大神方法）：
好吧！这个是可以，但是数据多了的话，想到这个查询的逻辑有点受不住
于是再改为下面的这样：
select cu.mailbox_id,cm.mailbox_id co_user as cu left join core_mailbox as cm on cu.mailbox_id = cm.mailbox_id where cm.mailbox_id NULL;
利用了left join的，然后进行对比，并且利用where进行筛选。
后面也在网上找了这条：
SELECT mailbox_id FROM `co_user` left join (select mailbox_id as i from core_mailbox) as t1 on co_user.mailbox_id= t1.i where t1.i is NULL;
概念上与第二条同理。
好吧！ 回顾了一下left join
SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
本文转自 sailikung 51CTO博客，原文链接：http://blog.51cto.com/net881004/2054820，如需转载请自行联系原作者
