以前架征途时的合区的SQL语句代码备份

su-mysql/' target='_blank'>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–databaseszt>/data/ok1.sql
cd/usr/local/mysql/bin/
mysql-uroot-pueoadirzt</backdata/kb.sql
mysqldump-uroot-pueoadir–databaseszt>/backdata/back1210.sql

su-mysql-c“mysqld_safe–skip-name-resolve–open-files-limit=4096&”
cd/usr/local/mysql/bin/
mysql-uroot-pueoadirzt4</backdata/new.sql
mysql-uroot-pueoadirzt</data/ok.sql
UE处理下负区导入的表
mysqlFLServer</data/FLServer.sql
mysqlGMTool</data/GMTool.sql
mysqlLoginServer</data/LoginServer.sql
mysqlroleChangeServer</data/roleChangeServer.sql
mysqlroleregServer</data/roleregServer.sql
mysqlunify00</data/unify00.sql



mysqlFLServer</data/FLServer.sql
mysqlGMTool</data/GMTool.sql
mysqlLoginServer</data/LoginServer.sql
mysqlroleChangeServer</data/roleChangeServer.sql
mysqlroleregServer</data/roleregServer.sql
mysqlunify00</data/unify00.sql



mysqlFLServer</data/FLServer.sql

mysqlzt</data/zt501.sql
mysql-uroot-pueoadirzt</backdata/kb.sql
查询
select*fromzt4.CHARBASEwherenamein(selectnamefromzt.zHARBASE)

select*fromCHARBASElimit100
selectUNIONIDfromCHARBASElimit100

更新
updatezt4.CHARBASEsetname=CONCAT('一区',rtrim(name))wherenamein(selectnamefromzt.CHARBASE)‘处理重名

updateCHARBASEsetname=CONCAT(rtrim(name),'1q')

updateCHARBASEsetCHARID=CHARID+10

插入
insertintozt.CHARBASESELECT*FROMzt4.CHARBASE
insertintogame.user(name,pass)selectname,passfromgame2.user2

导入
mysql-uroot-pueoadirzt</backdata/zt2q.sql
mysql-uroot-pueoadirzt</backdata/1q.sql

删除
deletefromCHARBASEWHEREROUND=0ANDLEVEL<80and`LASTACTIVEDATE`<‘2007-11-1900:00:00′
deletefromCHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-11-1900:00:00′



1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
deletefromzt.CHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-12-1000:00:00′

deletefromzt.CHARBASEWHEREROUND=0ANDLEVEL<80and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
2.去除GMCHARID编号重复
SELECTmax(CHARID)FROM`CHARBASE`

updatezt4.CHARBASEsetCHARID=CHARID+10000whereCHARID<100LIMIT50
updatezt4.CHARBASEsetCHARID=CHARID+20000whereCHARIDin(selectCHARIDfromzt.CHARBASE)

3.去除人物重名
updatezt4.CHARBASEsetname=CONCAT(rtrim(name),'o')wherenamein(selectnamefromzt.CHARBASE)

4.插入表2数据到表1

insertintozt.CHARBASESELECT*FROMzt4.CHARBASE

________________________________________________
deletefromzt4.CHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
deletefromzt4.CHARBASEWHEREROUND=0ANDLEVEL<96and`LASTACTIVEDATE`<‘2007-12-1000:00:00′
SELECTmax(CHARID)FROMzt4.CHARBASE
select*fromzt4.CHARBASElimit100
updatezt4.CHARBASEsetCHARID=CHARID+10000whereCHARIDin(selectCHARIDfromzt.CHARBASE)
updatezt4.CHARBASEsetname=CONCAT(rtrim(name),'oo')wherenamein(selectnamefromzt.CHARBASE)

insertintozt.CHARBASESELECT*FROMzt4.CHARBASE
___________________________________________________________________________________
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
deletefromzt.CHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
deletefromzt4.CHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
deletefromzt.CHARBASEWHEREROUND=0ANDLEVEL<80and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
deletefromzt4.CHARBASEWHEREROUND=0ANDLEVEL<80and`LASTACTIVEDATE`<‘2007-11-2200:00:00′



2.CHARBASE表里CHARID处理重复重复ID前加10000

updatezt4.CHARBASEsetzt4.CHARBASE.CHARID=zt4.CHARBASE.CHARID+10000wherezt4.CHARBASE.CHARIDin(selectCHARIDfromzt.CHARBASE)
CARTOONPET表里CARTOONID与主库CARTOONPET重复的处理
updatezt4.CARTOONPETsetzt4.CARTOONPET.CARTOONID=zt4.CARTOONPET.CARTOONID+10000wherezt4.CARTOONPET.CARTOONIDin(SELECTzt4.CARTOONPET.CARTOONIDfromzt.CARTOONPET)





3.SEPT表里SEPTID处理重复，重复ID前加10000
updatezt4.SEPT,zt.SEPTsetzt4.SEPT.SEPTID=zt4.SEPT.SEPTID+10000wherezt4.SEPT.SEPTIDin(selectSEPTIDfromzt.SEPT)

4.UNIONMEMBER表里UNIONID处理重复前加10000

updatezt4.UNIONMEMBERsetzt4.UNIONMEMBER.UNIONID=zt4.UNIONMEMBER.UNIONID+10000wherezt4.UNIONMEMBER.UNIONIDin(selectUNIONIDfromzt.UNIONMEMBER)

5.UNIONMEMBER表里SEPTID与SEPT表里的同步
updatezt4.UNIONMEMBER,zt4.SEPTsetzt4.UNIONMEMBER.SEPTID=zt4.SEPT.SEPTIDWHEREzt4.UNIONMEMBER.NAME=zt4.SEPT.MASTER
6.UNION表里UNIONID与UNIONMEMBER表里的同步
updatezt4.UNION,zt4.UNIONMEMBERsetzt4.UNION.UNIONID=zt4.UNIONMEMBER.UNIONIDWHEREzt4.UNION.MASTER=zt4.UNIONMEMBER.NAME

7.SEPT表里UNIONID与UNIONMEMBER表里的同步

updatezt4.SEPT,zt4.UNIONMEMBERsetzt4.SEPT.UNIONID=zt4.UNIONMEMBER.UNIONIDWHEREzt4.SEPT.MASTER=zt4.UNIONMEMBER.NAME

8.CHARBASE表里SEPTID处理与SEPT表一致
updatezt4.CHARBASE,zt4.SEPTsetzt4.CHARBASE.SEPTID=zt4.SEPT.SEPTIDWHEREzt4.CHARBASE.NAME=zt4.SEPT.NAME
9.CHARBASE表里UNIONID处理与UNIONMEMBER的UNIONID表一致
updatezt4.CHARBASE,zt4.UNIONMEMBERsetzt4.CHARBASE.UNIONID=zt4.UNIONMEMBER.UNIONIDWHEREzt4.CHARBASE.NAME=zt4.UNIONMEMBER.NAME
10.SEPT表里CHARID与CHARBASE里的CHARID一致
updatezt4.CHARBASE,zt4.SEPTsetzt4.SEPT.CHARID=zt4.CHARBASE.CHARIDWHEREzt4.CHARBASE.NAME=zt4.SEPT.MASTER
CARTOONPET表里MASTERID与CHARBASE里的CHARID一致
updatezt4.CARTOONPET,zt4.CHARBASEsetzt4.CARTOONPET.MASTERID=zt4.CHARBASE.CHARIDwherezt4.CHARBASE.NAME=zt4.CARTOONPET.MASTERNAME
12.UNIONMEMBER表里CHARID与CHARBASE里的CHARID一致
updatezt4.CHARBASE,zt4.UNIONMEMBERsetzt4.UNIONMEMBER.CHARID=zt4.CHARBASE.CHARIDWHEREzt4.CHARBASE.NAME=zt4.UNIONMEMBER.NAME

13.SCHOOLMEMBER表里CHARID与CHARBASE里的CHARID一致
updatezt4.CHARBASE,zt4.SCHOOLMEMBERsetzt4.SCHOOLMEMBER.CHARID=zt4.CHARBASE.CHARIDWHEREzt4.CHARBASE.NAME=zt4.SCHOOLMEMBER.NAME



14.CHARBASE表里NAME与主库CHARBASE名字重复的处理

updatezt4.CHARBASEsetname=CONCAT(rtrim(name),'oo')wherenamein(selectnamefromzt.CHARBASE)

15.SEPT表里NAME与主库SEPT名字重复的处理

updatezt4.SEPTsetzt4.SEPT.NAME=CONCAT(rtrim(zt4.SEPT.NAME),'oo')wherezt4.SEPT.NAMEin(selectNAMEfromzt.SEPT)

16.UNION表里NAME与主库UNION名字重复的处理
updatezt4.UNIONsetzt4.UNION.NAME=CONCAT(rtrim(zt4.UNION.NAME),'oo')wherezt4.UNION.NAMEin(selectNAMEfromzt.UNION)





17.SCHOOLMEMBER表里SERIALID与主库SCHOOLMEMBER名字重复的处理
updatezt4.SCHOOLMEMBERsetzt4.SCHOOLMEMBER.SERIALID=zt4.SCHOOLMEMBER.SERIALID+10000wherezt4.SCHOOLMEMBER.SERIALIDin(selectSERIALIDfromzt.SCHOOLMEMBER)
18.SEPT表里MASTER人物名与CHAREBASE里的人物名同步
updatezt4.CHARBASE,zt4.SEPTsetzt4.SEPT.MASTER=zt4.CHARBASE.NAMEWHEREzt4.CHARBASE.CHARID=zt4.SEPT.CHARID

19.SCHOOLMEMBER表里NAME与CHARBASE人物名同步
updatezt4.CHARBASE,zt4.SCHOOLMEMBERsetzt4.SCHOOLMEMBER.NAME=zt4.CHARBASE.NAMEWHEREzt4.CHARBASE.CHARID=zt4.SCHOOLMEMBER.CHARID

20.UNION表里MASTER人物名与CHAREBASE里的人物名同步
updatezt4.CHARBASE,zt4.UNIONsetzt4.UNION.NAME=zt4.CHARBASE.NAMEWHEREzt4.CHARBASE.CHARID=zt4.UNION.CHARID
22.CARTOONPET表里MASTERNAME与CHAREBASE里的人物名同步
updatezt4.CHARBASE,zt4.CARTOONPETsetzt4.CARTOONPET.MASTERNAME=zt4.CHARBASE.NAMEWHEREzt4.CHARBASE.CHARID=zt4.CARTOONPET.MASTERID

21.合并CHARBASE数据库
insertintozt.CHARBASESELECT*FROMzt4.CHARBASE
22.合并SCHOOLMEMBER数据库
insertintozt.SCHOOLMEMBERSELECT*FROMzt4.SCHOOLMEMBER
23.合并SEPT数据库
insertintozt.SEPTSELECT*FROMzt4.SEPT

24.合并UNIONMEMBER数据库

insertintozt.UNIONMEMBERSELECT*FROMzt4.UNIONMEMBER

25.合并UNION
insertintozt.UNIONSELECT*FROMzt4.UNION

26.合并CARTOONPET
insertintozt.CARTOONPETSELECT*FROMzt4.CARTOONPET

26.合并BALANCE
insertintozt.BALANCESELECT*FROMzt4.BALANCE

(责任编辑：admin)