以前架征途时的合区的SQL语句代码备份
程序员文章站
2022-11-21 12:23:58
su - mysql -c “mysqld_safe –skip-name-resolve –open-file...
su - mysql -c “mysqld_safe –skip-name-resolve –open-files-limit=4096&”
cd /usr/local/mysql/bin/
mysqldump -u用户名 -p密码 –databases 库名 >/backdata/ddd07-11-15.sql
mysqldump –databases zt >/data/ok1.sql
cd /usr/local/mysql/bin/
mysql -uroot -pueoadir zt</backdata/kb.sql
mysqldump -uroot -pueoadir –databases zt >/backdata/back1210.sql
su - mysql -c “mysqld_safe –skip-name-resolve –open-files-limit=4096&”
cd /usr/local/mysql/bin/
mysql -uroot -pueoadir zt4</backdata/new.sql
mysql -uroot -pueoadir zt</data/ok.sql
ue处理下负区导入的表
mysql flserver</data/flserver.sql
mysql gmtool</data/gmtool.sql
mysql loginserver</data/loginserver.sql
mysql rolechangeserver</data/rolechangeserver.sql
mysql roleregserver</data/roleregserver.sql
mysql unify00</data/unify00.sql
mysql flserver</data/flserver.sql
mysql gmtool</data/gmtool.sql
mysql loginserver</data/loginserver.sql
mysql rolechangeserver</data/rolechangeserver.sql
mysql roleregserver</data/roleregserver.sql
mysql unify00</data/unify00.sql
mysql flserver</data/flserver.sql
mysql zt</data/zt501.sql
mysql -uroot -pueoadir zt</backdata/kb.sql
查询
select * from zt4.charbase where name in (select name from zt.zharbase)
select * from charbase limit 100
select unionid from charbase limit 100
更新
update zt4.charbase set name=concat('一区',rtrim(name)) where name in (select name from zt.charbase) ‘处理重名
update charbase set name=concat(rtrim(name),'1q')
update charbase set charid=charid+10
插入
insert into zt.charbase select * from zt4.charbase
insert into game.user(name,pass) select name,pass from game2.user2
导入
mysql -uroot -pueoadir zt</backdata/zt2q.sql
mysql -uroot -pueoadir zt</backdata/1q.sql
删除
delete from charbase where round=0 and level < 80 and `lastactivedate`< ‘2007-11-19 00:00:00′
delete from charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-11-19 00:00:00′
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
delete from zt.charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-12-10 00:00:00′
delete from zt.charbase where round=0 and level<80 and `lastactivedate`< ‘2007-11-22 00:00:00′
2.去除gm charid编号重复
select max(charid) from `charbase`
update zt4.charbase set charid=charid+10000 where charid < 100 limit 50
update zt4.charbase set charid=charid+20000 where charid in (select charid from zt.charbase)
3.去除人物重名
update zt4.charbase set name=concat(rtrim(name),'o') where name in (select name from zt.charbase)
4.插入表2数据到表1
insert into zt.charbase select * from zt4.charbase
________________________________________________
delete from zt4.charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-11-22 00:00:00′
delete from zt4.charbase where round=0 and level<96 and `lastactivedate`< ‘2007-12-10 00:00:00′
select max(charid) from zt4.charbase
select * from zt4.charbase limit 100
update zt4.charbase set charid=charid+10000 where charid in (select charid from zt.charbase)
update zt4.charbase set name=concat(rtrim(name),'oo') where name in (select name from zt.charbase)
insert into zt.charbase select * from zt4.charbase
___________________________________________________________________________________
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
delete from zt.charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-11-22 00:00:00′
delete from zt4.charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-11-22 00:00:00′
delete from zt.charbase where round=0 and level<80 and `lastactivedate`< ‘2007-11-22 00:00:00′
delete from zt4.charbase where round=0 and level<80 and `lastactivedate`< ‘2007-11-22 00:00:00′
2.charbase表里charid处理重复 重复id 前加10000
update zt4.charbase set zt4.charbase.charid=zt4.charbase.charid+10000 where zt4.charbase.charid in (select charid from zt.charbase)
cartoonpet表里cartoonid与主库cartoonpet重复的处理
update zt4.cartoonpet set zt4.cartoonpet.cartoonid=zt4.cartoonpet.cartoonid+10000 where zt4.cartoonpet.cartoonid in (select zt4.cartoonpet.cartoonid from zt.cartoonpet)
3.sept表里septid 处理重复，重复id前加10000
update zt4.sept,zt.sept set zt4.sept.septid= zt4.sept.septid+10000 where zt4.sept.septid in (select septid from zt.sept)
4.unionmember表里unionid处理重复前加10000
update zt4.unionmember set zt4.unionmember.unionid= zt4.unionmember.unionid+10000 where zt4.unionmember.unionid in (select unionid from zt.unionmember)
5.unionmember表里septid 与sept表里的同步
update zt4.unionmember,zt4.sept set zt4.unionmember.septid=zt4.sept.septid where zt4.unionmember.name=zt4.sept.master
6.union表里unionid 与unionmember表里的同步
update zt4.union,zt4.unionmember set zt4.union.unionid=zt4.unionmember.unionid where zt4.union.master=zt4.unionmember.name
7.sept表里unionid 与unionmember表里的同步
update zt4.sept,zt4.unionmember set zt4.sept.unionid=zt4.unionmember.unionid where zt4.sept.master=zt4.unionmember.name
8.charbase表里septid处理与sept表一致
update zt4.charbase,zt4.sept set zt4.charbase.septid=zt4.sept.septid where zt4.charbase.name=zt4.sept.name
9.charbase表里unionid处理与unionmember的unionid表一致
update zt4.charbase,zt4.unionmember set zt4.charbase.unionid=zt4.unionmember.unionid where zt4.charbase.name=zt4.unionmember.name
10.sept表里charid与charbase里的charid一致
update zt4.charbase,zt4.sept set zt4.sept.charid=zt4.charbase.charid where zt4.charbase.name=zt4.sept.master
cartoonpet表里masterid与charbase里的charid一致
update zt4.cartoonpet,zt4.charbase set zt4.cartoonpet.masterid=zt4.charbase.charid where zt4.charbase.name=zt4.cartoonpet.mastername
12.unionmember表里charid与charbase里的charid一致
update zt4.charbase,zt4.unionmember set zt4.unionmember.charid=zt4.charbase.charid where zt4.charbase.name=zt4.unionmember.name
13.schoolmember表里charid与charbase里的charid一致
update zt4.charbase,zt4.schoolmember set zt4.schoolmember.charid=zt4.charbase.charid where zt4.charbase.name=zt4.schoolmember.name
14.charbase表里name与主库charbase名字重复的处理
update zt4.charbase set name=concat(rtrim(name),'oo') where name in (select name from zt.charbase)
15.sept表里name与主库sept名字重复的处理
update zt4.sept set zt4.sept.name=concat(rtrim(zt4.sept.name),'oo') where zt4.sept.name in (select name from zt.sept)
16.union表里name与主库union名字重复的处理
update zt4.union set zt4.union.name=concat(rtrim(zt4.union.name),'oo') where zt4.union.name in (select name from zt.union)
17.schoolmember表里serialid与主库schoolmember名字重复的处理
update zt4.schoolmember set zt4.schoolmember.serialid=zt4.schoolmember.serialid+10000 where zt4.schoolmember.serialid in (select serialid from zt.schoolmember)
18.sept表里master人物名与charebase里的人物名同步
update zt4.charbase,zt4.sept set zt4.sept.master=zt4.charbase.name where zt4.charbase.charid=zt4.sept.charid
19.schoolmember表里name与charbase人物名同步
update zt4.charbase,zt4.schoolmember set zt4.schoolmember.name=zt4.charbase.name where zt4.charbase.charid=zt4.schoolmember.charid
20.union表里master人物名与charebase里的人物名同步
update zt4.charbase,zt4.union set zt4.union.name=zt4.charbase.name where zt4.charbase.charid=zt4.union.charid
22.cartoonpet表里mastername与charebase里的人物名同步
update zt4.charbase,zt4.cartoonpet set zt4.cartoonpet.mastername=zt4.charbase.name where zt4.charbase.charid=zt4.cartoonpet.masterid
21.合并charbase数据库
insert into zt.charbase select * from zt4.charbase
22.合并schoolmember数据库
insert into zt.schoolmember select * from zt4.schoolmember
23.合并sept数据库
insert into zt.sept select * from zt4.sept
24.合并unionmember数据库
insert into zt.unionmember select * from zt4.unionmember
25.合并union
insert into zt.union select * from zt4.union
26.合并cartoonpet
insert into zt.cartoonpet select * from zt4.cartoonpet
26.合并balance
insert into zt.balance select * from zt4.balance
cd /usr/local/mysql/bin/
mysqldump -u用户名 -p密码 –databases 库名 >/backdata/ddd07-11-15.sql
mysqldump –databases zt >/data/ok1.sql
cd /usr/local/mysql/bin/
mysql -uroot -pueoadir zt</backdata/kb.sql
mysqldump -uroot -pueoadir –databases zt >/backdata/back1210.sql
su - mysql -c “mysqld_safe –skip-name-resolve –open-files-limit=4096&”
cd /usr/local/mysql/bin/
mysql -uroot -pueoadir zt4</backdata/new.sql
mysql -uroot -pueoadir zt</data/ok.sql
ue处理下负区导入的表
mysql flserver</data/flserver.sql
mysql gmtool</data/gmtool.sql
mysql loginserver</data/loginserver.sql
mysql rolechangeserver</data/rolechangeserver.sql
mysql roleregserver</data/roleregserver.sql
mysql unify00</data/unify00.sql
mysql flserver</data/flserver.sql
mysql gmtool</data/gmtool.sql
mysql loginserver</data/loginserver.sql
mysql rolechangeserver</data/rolechangeserver.sql
mysql roleregserver</data/roleregserver.sql
mysql unify00</data/unify00.sql
mysql flserver</data/flserver.sql
mysql zt</data/zt501.sql
mysql -uroot -pueoadir zt</backdata/kb.sql
查询
select * from zt4.charbase where name in (select name from zt.zharbase)
select * from charbase limit 100
select unionid from charbase limit 100
更新
update zt4.charbase set name=concat('一区',rtrim(name)) where name in (select name from zt.charbase) ‘处理重名
update charbase set name=concat(rtrim(name),'1q')
update charbase set charid=charid+10
插入
insert into zt.charbase select * from zt4.charbase
insert into game.user(name,pass) select name,pass from game2.user2
导入
mysql -uroot -pueoadir zt</backdata/zt2q.sql
mysql -uroot -pueoadir zt</backdata/1q.sql
删除
delete from charbase where round=0 and level < 80 and `lastactivedate`< ‘2007-11-19 00:00:00′
delete from charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-11-19 00:00:00′
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
delete from zt.charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-12-10 00:00:00′
delete from zt.charbase where round=0 and level<80 and `lastactivedate`< ‘2007-11-22 00:00:00′
2.去除gm charid编号重复
select max(charid) from `charbase`
update zt4.charbase set charid=charid+10000 where charid < 100 limit 50
update zt4.charbase set charid=charid+20000 where charid in (select charid from zt.charbase)
3.去除人物重名
update zt4.charbase set name=concat(rtrim(name),'o') where name in (select name from zt.charbase)
4.插入表2数据到表1
insert into zt.charbase select * from zt4.charbase
________________________________________________
delete from zt4.charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-11-22 00:00:00′
delete from zt4.charbase where round=0 and level<96 and `lastactivedate`< ‘2007-12-10 00:00:00′
select max(charid) from zt4.charbase
select * from zt4.charbase limit 100
update zt4.charbase set charid=charid+10000 where charid in (select charid from zt.charbase)
update zt4.charbase set name=concat(rtrim(name),'oo') where name in (select name from zt.charbase)
insert into zt.charbase select * from zt4.charbase
___________________________________________________________________________________
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
delete from zt.charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-11-22 00:00:00′
delete from zt4.charbase where round=0 and onlinetime<4962 and `lastactivedate`< ‘2007-11-22 00:00:00′
delete from zt.charbase where round=0 and level<80 and `lastactivedate`< ‘2007-11-22 00:00:00′
delete from zt4.charbase where round=0 and level<80 and `lastactivedate`< ‘2007-11-22 00:00:00′
2.charbase表里charid处理重复 重复id 前加10000
update zt4.charbase set zt4.charbase.charid=zt4.charbase.charid+10000 where zt4.charbase.charid in (select charid from zt.charbase)
cartoonpet表里cartoonid与主库cartoonpet重复的处理
update zt4.cartoonpet set zt4.cartoonpet.cartoonid=zt4.cartoonpet.cartoonid+10000 where zt4.cartoonpet.cartoonid in (select zt4.cartoonpet.cartoonid from zt.cartoonpet)
3.sept表里septid 处理重复，重复id前加10000
update zt4.sept,zt.sept set zt4.sept.septid= zt4.sept.septid+10000 where zt4.sept.septid in (select septid from zt.sept)
4.unionmember表里unionid处理重复前加10000
update zt4.unionmember set zt4.unionmember.unionid= zt4.unionmember.unionid+10000 where zt4.unionmember.unionid in (select unionid from zt.unionmember)
5.unionmember表里septid 与sept表里的同步
update zt4.unionmember,zt4.sept set zt4.unionmember.septid=zt4.sept.septid where zt4.unionmember.name=zt4.sept.master
6.union表里unionid 与unionmember表里的同步
update zt4.union,zt4.unionmember set zt4.union.unionid=zt4.unionmember.unionid where zt4.union.master=zt4.unionmember.name
7.sept表里unionid 与unionmember表里的同步
update zt4.sept,zt4.unionmember set zt4.sept.unionid=zt4.unionmember.unionid where zt4.sept.master=zt4.unionmember.name
8.charbase表里septid处理与sept表一致
update zt4.charbase,zt4.sept set zt4.charbase.septid=zt4.sept.septid where zt4.charbase.name=zt4.sept.name
9.charbase表里unionid处理与unionmember的unionid表一致
update zt4.charbase,zt4.unionmember set zt4.charbase.unionid=zt4.unionmember.unionid where zt4.charbase.name=zt4.unionmember.name
10.sept表里charid与charbase里的charid一致
update zt4.charbase,zt4.sept set zt4.sept.charid=zt4.charbase.charid where zt4.charbase.name=zt4.sept.master
cartoonpet表里masterid与charbase里的charid一致
update zt4.cartoonpet,zt4.charbase set zt4.cartoonpet.masterid=zt4.charbase.charid where zt4.charbase.name=zt4.cartoonpet.mastername
12.unionmember表里charid与charbase里的charid一致
update zt4.charbase,zt4.unionmember set zt4.unionmember.charid=zt4.charbase.charid where zt4.charbase.name=zt4.unionmember.name
13.schoolmember表里charid与charbase里的charid一致
update zt4.charbase,zt4.schoolmember set zt4.schoolmember.charid=zt4.charbase.charid where zt4.charbase.name=zt4.schoolmember.name
14.charbase表里name与主库charbase名字重复的处理
update zt4.charbase set name=concat(rtrim(name),'oo') where name in (select name from zt.charbase)
15.sept表里name与主库sept名字重复的处理
update zt4.sept set zt4.sept.name=concat(rtrim(zt4.sept.name),'oo') where zt4.sept.name in (select name from zt.sept)
16.union表里name与主库union名字重复的处理
update zt4.union set zt4.union.name=concat(rtrim(zt4.union.name),'oo') where zt4.union.name in (select name from zt.union)
17.schoolmember表里serialid与主库schoolmember名字重复的处理
update zt4.schoolmember set zt4.schoolmember.serialid=zt4.schoolmember.serialid+10000 where zt4.schoolmember.serialid in (select serialid from zt.schoolmember)
18.sept表里master人物名与charebase里的人物名同步
update zt4.charbase,zt4.sept set zt4.sept.master=zt4.charbase.name where zt4.charbase.charid=zt4.sept.charid
19.schoolmember表里name与charbase人物名同步
update zt4.charbase,zt4.schoolmember set zt4.schoolmember.name=zt4.charbase.name where zt4.charbase.charid=zt4.schoolmember.charid
20.union表里master人物名与charebase里的人物名同步
update zt4.charbase,zt4.union set zt4.union.name=zt4.charbase.name where zt4.charbase.charid=zt4.union.charid
22.cartoonpet表里mastername与charebase里的人物名同步
update zt4.charbase,zt4.cartoonpet set zt4.cartoonpet.mastername=zt4.charbase.name where zt4.charbase.charid=zt4.cartoonpet.masterid
21.合并charbase数据库
insert into zt.charbase select * from zt4.charbase
22.合并schoolmember数据库
insert into zt.schoolmember select * from zt4.schoolmember
23.合并sept数据库
insert into zt.sept select * from zt4.sept
24.合并unionmember数据库
insert into zt.unionmember select * from zt4.unionmember
25.合并union
insert into zt.union select * from zt4.union
26.合并cartoonpet
insert into zt.cartoonpet select * from zt4.cartoonpet
26.合并balance
insert into zt.balance select * from zt4.balance