我們?cè)谧稣搲�網(wǎng)站時(shí)，經(jīng)常需要對(duì)論壇內(nèi)容進(jìn)行批量修改，如果不采用數(shù)據(jù)庫批量修改，就會(huì)把你累爬下。

下面是學(xué)做網(wǎng)站論壇匯總DISCUZ論壇常用批量修改SQL語句，根據(jù)自己的需要，使用常用SQL語句。注意在使用SQL語句前，一定要備份自己網(wǎng)站的數(shù)據(jù)庫�！鞠嚓P(guān)知識(shí)：網(wǎng)站數(shù)據(jù)如何備份】

所有板塊的特殊主題開啟
UPDATE `cdb_forums` SET `allowpostspecial` = '31'

調(diào)整某個(gè)帖子的點(diǎn)擊數(shù)
UPDATE cdb_threads SET views=點(diǎn)擊數(shù) WHERE tid=主題編號(hào);

所有板塊開啟media代碼
UPDATE `cdb_forums` SET `allowmediacode` = '1'

調(diào)整所有用戶最大頭像尺寸（像素）為120
update cdb_settings set value = 120 WHERE variable = 'maxavatarpixel'

一次性關(guān)閉板塊fid20帖子
UPDATE `cdb_threads` SET `closed` = '1' WHERE `fid` =20 ;

把發(fā)帖數(shù)為0的用戶都轉(zhuǎn)到某個(gè)用戶組
update cdb_members set groupid = * where posts = 0;

UPDATE `cdb_usergroups` SET `allowinvite` = '1', `inviteprice` = '價(jià)格';
開啟全部用戶組的邀請(qǐng)注冊(cè)權(quán)限? ?并設(shè)置價(jià)格

replace INTO cdb_settings VALUES ('watermarktype','0');
去除水印圖片類型

update cdb_members set lastpost='1186675200';
調(diào)整用戶最后登錄時(shí)間為 2007年8月10日

UPDATE `cdb_forums` SET `status` = '1',
`lastpost` = '' WHERE `fid` =6 LIMIT 1 ;
調(diào)整 板塊隱藏的sql！

完整關(guān)閉supesite的sql語句:
UPDATE `cdb_settings` SET `value` = '' WHERE `variable` = 'supe_siteurl' ;
UPDATE `cdb_settings` SET `value` = '' WHERE? ?`variable` = 'supe_sitename';
UPDATE `cdb_settings` SET `value` = '0' WHERE `variable`? ?= 'supe_status';
UPDATE `cdb_settings` SET `value` = '' WHERE `variable` = 'supe_tablepre';
UPDATE `cdb_settings` SET `value` = '0' WHERE `variable`? ?= 'supe_circlestatus';

將A板塊的所有帖子轉(zhuǎn)到B板塊的C分類里
update cdb_threads set fid=B板塊ID,typeid=c分類的ID where? ?fid=A板塊ID;
update cdb_posts set fid=B板塊ID? ?where? ?fid=A板塊ID;
執(zhí)行前備份一下數(shù)據(jù)庫

去除后臺(tái)管理員ip登錄限制
UPDATE `cdb_settings` SET `value` = '' WHERE CONVERT( `variable` USING utf8 ) ='adminipaccess' LIMIT 1 ;

刪除風(fēng)格5
delete from cdb_styles where styleid=5 limit 1;

UPDATE `cdb_members` SET `pmsound` = '1'
論壇所有用戶短消息提示音修改為提示音1

delete from cdb_usergroups where groupid = 0
刪除groupid = 0的用戶組

UPDATE `cdb_forums` SET `alloweditpost` = '1'
所有板塊開啟允許編輯帖子
論壇后臺(tái) 解除insenz綁定語句
delete from cdb_settings where variable='insenz'
論壇后臺(tái)執(zhí)行后 就可以 重新注冊(cè)和綁定了

關(guān)閉游客瀏覽所有版塊的語句
可以這樣構(gòu)造SQL語句
update cdb_forumfields? ?set? ?viewperm ='\t允許瀏覽的用戶組的ID\t'? ?where? ?fid in(論壇板塊ID)
如:
update cdb_forumfields? ?set? ?viewperm ='\t1\t2\t'? ?where? ?fid in(1,2)

UPDATE `cdb_forumfields` SET `rules` = '板塊規(guī)則'
所有板塊的板塊規(guī)則設(shè)置~！

UPDATE `cdb_forums` SET `jammer` = '1'
開啟所有板塊的干擾碼

開啟所有會(huì)員組邀請(qǐng)注冊(cè)的權(quán)限
UPDATE cdb_usergroups SET allowinvite=1, allowmailinvite=1, maxinvitenum=10, maxinviteday=15, inviteprice=10;
//allowinvite? ?允許使用邀請(qǐng)注冊(cè):
//allowmailinvite 允許發(fā)送邀請(qǐng)注冊(cè):
//maxinvitenum 24小時(shí)內(nèi)購買邀請(qǐng)碼最大數(shù)量:
//maxinviteday 邀請(qǐng)碼有效期:
//inviteprice 邀請(qǐng)碼購買價(jià)格:

修改最高日發(fā)帖數(shù)
UPDATE `cdb_settings` SET `value` = '昨日發(fā)帖數(shù) 最高日發(fā)帖數(shù)' WHERE `variable` ='historyposts' LIMIT 1 ;
昨日發(fā)帖數(shù) 和? ?最高日發(fā)帖數(shù) 修改為你需要的數(shù)字即可， 升級(jí)完sql后,更新緩存

'昨日發(fā)帖數(shù) 最高日發(fā)帖數(shù)' 中間的空格是tab鍵~！

UPDATE `cdb_settings` SET `value` = '0' WHERE CONVERT( `variable` USING utf8 ) = 'gzipcompress' LIMIT 1 ;
關(guān)閉論壇的gzip設(shè)置！

批量對(duì)用戶組禁言
update cdb_members set adminid='-1', groupid='4' where groupid='?';
delete from cdb_threads where authorid=0;
delete from cdb_posts where authorid=0;

批量刪除游客組的發(fā)帖和主題
uid 2000~3000的會(huì)員全部積分清零
update cdb_members set credits=0,extcredits1=0,extcredits2=0,extcredits3=0,extcredits4=0,extcredits5=0,extcredits6=0,extcredits7=0,extcredits8=0 where uid > 2000 and uid < 3000;

指定范圍內(nèi)的審核主題通過審核
update cdb_threads set displayorder=0 where tid > 最小的 tid and tid < 最大的 tid

指定范圍內(nèi)的審核帖子通過審核
update cdb_posts set invisible = 0 where tid > 最小的tid and tid < 最大的 tid

批量刪除未審核通過的帖子和主題
delete from cdb_threads? ?where displayorder=-2;
delete from cdb_posts? ?where invisible=-2;

批量關(guān)閉投票
UPDATE cdb_threads SET closed = 1 WHERE special=1

UPDATE `cdb_members` SET `secques` = ''
清空用戶安全問答！

清空某個(gè)用戶組的全部積分sql:
update cdb_members set credits = 0, extcredits1 = 0, extcredits2 = 0, extcredits3 = 0, extcredits4 = 0, extcredits5 = 0, extcredits6 = 0, extcredits7 = 0, extcredits8 = 0, where groupid = 用戶組id;

update cdb_usergroups set allowtransfer=0

關(guān)閉所有用戶組的積分轉(zhuǎn)賬

update cdb_threads set readperm=100 where dateline<1133300000
修改05年11月29日21點(diǎn)33份20秒 前的主題閱讀權(quán)限100
其中1133300000 是unix時(shí)間戳 （以秒為單位） 代表 05年11月29日21點(diǎn)33份20秒
readperm=100 代表閱讀權(quán)限

把2005年1月1日以前的閱讀權(quán)限設(shè)高.
update cdb_threads set readperm=100 where dateline<1104537600 and digest=0

批量替換帖子里面的評(píng)分者的用戶名
update cdb_ratelog set uid = '新uid', username = '新username' where uid = '舊的 uid';

所有的附件默認(rèn)閱讀權(quán)限為1
update cdb_attachments set readperm=1

統(tǒng)計(jì)發(fā)帖量大約0的所有用戶積分1的總量
SELECT count(`extcredits1`) FROM `cdb_members` where `posts`=0

批量修改uid1~6會(huì)員的 showemail 為 1
UPDATE `cdb_members` SET `showemail` ='1' WHERE `uid` in(1,2,3,4,5,6);

將主題tid大于14651的所有主題減去16761780
update cdb_threads set tid=tid-16761780 where tid>14651;
update cdb_posts set tid=tid-16761780 where tid>14651;
update cdb_attachments set tid=tid-16761780 where tid>14651;
update cdb_myposts set tid=tid-16761780 where tid>14651;
update cdb_activities set tid=tid-16761780 where tid>14651;
update cdb_activityapplies set tid=tid-16761780 where tid>14651;
update cdb_favorites set tid=tid-16761780 where tid>14651;
update cdb_mythreads set tid=tid-16761780 where tid>14651;
update cdb_paymentlog set tid=tid-16761780 where tid>14651;
update cdb_polloptions set tid=tid-16761780 where tid>14651;
update cdb_polls set tid=tid-16761780 where tid>14651;
update cdb_pushedthreads set tid=tid-16761780 where tid>14651;
update cdb_relatedthreads set tid=tid-16761780 where tid>14651;
update cdb_rewardlog set tid=tid-16761780 where tid>14651;
update cdb_rsscaches set tid=tid-16761780 where tid>14651;
update cdb_sessions set tid=tid-16761780 where tid>14651;
update cdb_subscriptions set tid=tid-16761780 where tid>14651;
update cdb_threadsmod set tid=tid-16761780 where tid>14651;
update cdb_tradelog set tid=tid-16761780 where tid>14651;
update cdb_trades set tid=tid-16761780 where tid>14651;
此操作前請(qǐng)注意網(wǎng)站備份！�。ò凑�5.0數(shù)據(jù)表進(jìn)行修改的? ?6.0的需要查看下數(shù)據(jù)庫對(duì)比下）

將積分1加到積分2上? ?并清零積分1的sql
update cdb_members set extcredits1=extcredits1+extcredits2;
update cdb_members set extcredits1='0';

把300天未登錄的ID移到某一特殊用戶組
update cdb_members set adminid=-1,groupid=特殊用戶組ID where? ?lastactivity< unix_timestamp()-300*24*3600

服務(wù)器上禁止IP訪問命令
iptables -I INPUT -p TCP -s 218.0.211.0/24 --dport 80 -j DROP

查一個(gè)IP有哪個(gè)會(huì)員登錄
select * from cdb_membsers where lastip="IP"

UPDATE cdb_posts p, cdb_attachments a SET p.attachment=1 WHERE p.pid=a.pid;
找回丟失附件的sql語句

更新昨日發(fā)貼數(shù)
昨日發(fā)貼數(shù)：
select count(*) from cdb_posts where dateline>昨天0時(shí)的時(shí)間戳 and? ???dateline<今天0時(shí)的時(shí)間戳
更新昨日發(fā)貼數(shù)：
UPDATE `cdb_settings` SET `value` = '昨日發(fā)帖數(shù)\t1428' WHERE `variable` ='historyposts' LIMIT 1 ;

把"新手上路"用戶組下的所有會(huì)員轉(zhuǎn)換到另一個(gè)特殊的用戶組
pl:特殊用戶組沒有關(guān)聯(lián)任何的管理組
update cdb_members set groupid=特殊用戶組的id where groupid=新手上路用戶組的id and adminid=0

刪除一個(gè)板塊的所有回復(fù)
delete from cdb_posts where fid = '需要?jiǎng)h除回復(fù)的版塊id' and first = 0;

delete from cdb_forums where fid=23 limit 1;
刪除fid23的板塊

圈子sgid字段缺少 添加的sql
ALTER TABLE cdb_threads ADD sgid mediumint(8) unsigned NOT NULL default '0'

update cdb_threads set replies=(select count(*) from cdb_posts where tid=47708) where tid=47708
更新tid27708主題的統(tǒng)計(jì)

關(guān)閉所有板塊的允許其它模塊共享
update cdb_forums set allowshare=0

UPDATE `cdb_memberfields` SET `sightml` = ' '
所有用戶的簽名清空

UPDATE cdb_posts SET subject=REPLACE(subject,'#','樓');
批量替換帖子中的回復(fù)* #的顯示為 *樓

刪除一個(gè)主題的所有回復(fù)
delete from cdb_posts where tid = '需要?jiǎng)h除回復(fù)的主題的tid' and first = 0;

UPDATE `cdb_settings` SET `value` = '1' WHERE CONVERT( `variable` USING utf8 ) = 'thumbstatus' LIMIT 1 ;
設(shè)置縮略圖為? ???為圖片附件添加縮略圖

UPDATE `cdb_settings` SET `value` = '2' WHERE CONVERT( `variable` USING utf8 ) = 'thumbstatus' LIMIT 1 ;
設(shè)置縮略圖為? ???將圖片附件縮到指定的大小

批量驗(yàn)證所有未驗(yàn)證的會(huì)員
UPDATE `cdb_members` SET `groupid` = '10' WHERE groupid='8';

批量刪除游客回帖和主題
delete from cdb_threads where authorid=0;
delete from cdb_posts where authorid=0;

刪除在180天之前的全部短消息
delete from cdb_pms where dateline<1190518730

清空某個(gè)用戶的已發(fā)短消息

update cdb_pms set delstatus=1 where msgfromid=發(fā)送者ID

所有用戶組（包括系統(tǒng)用戶組，普通用戶組，特殊用戶組）都可以參與投票的SQL語句：
update cdb_usergroups set allowvote=1 where groupid<4 or groupid>9

UPDATE `cdb_usergroups` SET `raterange` = '1\t-1\t1\t100\n2\t-1\t1\t100'
開啟所有用戶組的擴(kuò)展積分1和2的評(píng)分值為 最小-1 最大1 24小時(shí)100

圖片附件設(shè)置正確 但是不直接顯示的調(diào)整sql：
update cdb_attachments set `isimage` =1 where SUBSTRING(`filename`,-3,3)='jpg' or SUBSTRING(`filename`,-3,3)='gif'

固定天數(shù)內(nèi)一部份附件到遠(yuǎn)程附件的sql
update cdb_attachments set remote = '1' where dateline<unix_timestamp()-86400*天數(shù)

修改所有用戶組評(píng)分的SQL語句

update `cdb_usergroups` set `raterange`='擴(kuò)展積分ID\t評(píng)分最小值\t評(píng)分最大值\t24小時(shí)最大評(píng)分?jǐn)?shù)\n擴(kuò)展積分ID\t評(píng)分最小值\t評(píng)分最大值\t24小時(shí)最大評(píng)分?jǐn)?shù)'

將主題tid大于4009的所有主題減去16773200
update cdb_activities set tid=tid-16773200 where tid>4009;
update cdb_activityapplies set tid=tid-16773200 where tid>4009;
update cdb_attachments set tid=tid-16773200 where tid>4009;
update cdb_campaigns set tid=tid-16773200 where tid>4009;
update cdb_debateposts set tid=tid-16773200 where tid>4009;
update cdb_debates set tid=tid-16773200 where tid>4009;
update cdb_favorites set tid=tid-16773200 where tid>4009;
update cdb_forumrecommend set tid=tid-16773200 where tid>4009;
update cdb_myposts set tid=tid-16773200 where tid>4009;
update cdb_mythreads set tid=tid-16773200 where tid>4009;
update cdb_paymentlog set tid=tid-16773200 where tid>4009;
update cdb_polloptions set tid=tid-16773200 where tid>4009;
update cdb_polls set tid=tid-16773200 where tid>4009;
update cdb_posts set tid=tid-16773200 where tid>4009;
update cdb_relatedthreads set tid=tid-16773200 where tid>4009;
update cdb_rewardlog set tid=tid-16773200 where tid>4009;
update cdb_rsscaches set tid=tid-16773200 where tid>4009;
update cdb_sessions set tid=tid-16773200 where tid>4009;
update cdb_subscriptions set tid=tid-16773200 where tid>4009;
update cdb_threads set tid=tid-16773200 where tid>4009;
update cdb_threadsmod set tid=tid-16773200 where tid>4009;
update cdb_threadtags set tid=tid-16773200 where tid>4009;
update cdb_tradelog set tid=tid-16773200 where tid>4009;
update cdb_trades set tid=tid-16773200 where tid>4009;
update cdb_typeoptionvars set tid=tid-16773200 where tid>4009;
update cdb_videos set tid=tid-16773200 where tid>4009;
update cdb_videotags set tid=tid-16773200 where tid>4009;
如果安裝了supesite? ?有了推送主題表還加上：
update cdb_pushedthreads set tid=tid-16773200 where tid>4009;
以上sql為6.0版本

批量替換論壇標(biāo)題內(nèi)容
UPDATE `cdb_threads` SET `subject` = replace (`subject`,'需要替換的','替換后的')

UPDATE `cdb_attachments` SET `thumb` = '0'
取消所有附件的縮略圖數(shù)據(jù)

UPDATE `cdb_threads` SET `highlight` = '0'
取消全部帖子的高亮顯示

論壇里所有回復(fù)長(zhǎng)度小于20的而且沒有附件的貼子全部刪除掉
delete from cdb_posts where length(message)<20 and attachment=0

查詢ftp上的附件是否在數(shù)據(jù)庫內(nèi)
phpmyadmin中 執(zhí)行sql：
select * from cdb_attachments where attachment like "%文件名稱%"

一次性給一個(gè)貼內(nèi)的所有回帖人加積分
update cdb_members set extcredits2=extcredits2+10? ?where uid in(SELECT authorid FROM `cdb_posts` WHERE tid=主題ID)
extcredits2? ?是需要增加的積分字段 ， 使用的時(shí)候要注意增加的哪個(gè)積分字段

truncate cdb_validating
清空用戶審核數(shù)據(jù)表

UPDATE `cdb_members` SET `oltime` = '0'
所有用戶的在線時(shí)間修改為0

把體積大于 819200kb的附件指定為遠(yuǎn)程附件
update cdb_attachments set remote = '1' WHERE filesize > 819200

ALTER TABLE `cdb_threads` CHANGE `tid` `tid` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT;

相關(guān)閱讀：

DZ論壇批量修改已發(fā)布貼子內(nèi)容

批量刪除DZ論壇中未審核的垃圾貼

標(biāo)簽： isp 安全 代碼 服務(wù)器 權(quán)限 數(shù)據(jù)庫

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。