Discuz X 那些你必须珍藏的有关帖子的SQL命令！！留作备用！！

dly · 发表于 2011-9-3 11:10:29

自己常用的SQL分享给大家,别说我没提醒过你，如果你不备份就操作，你就完了！一个SQL命令能清空你论坛所有帖子

1.论坛附件转为远程：

update pre_forum_attachment_0 set remote=1 where remote=0;
update pre_forum_attachment_1 set remote=1 where remote=0;
update pre_forum_attachment_2 set remote=1 where remote=0;
update pre_forum_attachment_3 set remote=1 where remote=0;
update pre_forum_attachment_4 set remote=1 where remote=0;
update pre_forum_attachment_5 set remote=1 where remote=0;
update pre_forum_attachment_6 set remote=1 where remote=0;
update pre_forum_attachment_7 set remote=1 where remote=0;
update pre_forum_attachment_8 set remote=1 where remote=0;
update pre_forum_attachment_9 set remote=1 where remote=0;

复制代码

建议大家别开什么远程服务器，填写即可，然后把附件上传的图片服务器，这样即可使减少图片压力，又可以把图片传到本地服务器（速度快，错误少啊），定期转移即可
如果图片服务器崩溃，下面是把所有远程附件变本地SQL

update pre_forum_attachment_0 set remote=0 where remote=1;
update pre_forum_attachment_1 set remote=0 where remote=1;
update pre_forum_attachment_2 set remote=0 where remote=1;
update pre_forum_attachment_3 set remote=0 where remote=1;
update pre_forum_attachment_4 set remote=0 where remote=1;
update pre_forum_attachment_5 set remote=0 where remote=1;
update pre_forum_attachment_6 set remote=0 where remote=1;
update pre_forum_attachment_7 set remote=0 where remote=1;
update pre_forum_attachment_8 set remote=0 where remote=1;
update pre_forum_attachment_9 set remote=0 where remote=1;

复制代码

赠送一条：把体积大于 819200kb的附件指定为远程附件

update cdb_attachments set remote = '1' WHERE filesize > 819200;

复制代码

2.锁帖:
把ID为82的板块里的帖子全部锁帖，CLOSED改为0，就是解锁

UPDATE pre_forum_thread SET closed= '1' WHERE fid ='82';

复制代码

把ID为82的板块里的没有附件的帖子全部锁帖，CLOSED改为0，就是解锁

UPDATE pre_forum_thread SET closed= '1' WHERE fid ='82' and attachment ='0';

复制代码

把板块82里时间戳小于1298912461（年月日）的帖子锁帖，时间戳查询网址 http://tool.chinaz.com/Tools/unixtime.aspx

UPDATE pre_forum_thread SET closed= '1' WHERE fid ='82' and dateline<1298912461;

复制代码

3.移动帖子：
把版块57所有没有都移动到155里

UPDATE pre_forum_thread SET fid ='155' WHERE fid ='57';

复制代码

把版块57所有没有附件的帖子都移动到155里，attachment更改为1就是所有有附加的帖子

UPDATE pre_forum_thread SET fid ='155' WHERE fid ='57' and attachment ='0';

复制代码

将A板块的所有帖子转到B板块的C分类里

update pre_forum_thread set fid=B板块ID,typeid=c分类的ID where fid=A板块ID;

复制代码

4.售价
把板块1的帖子售价全部设定为100

update pre_forum_thread set price=100 where fid='1';

复制代码

price=price+100 所有售价加100 减法为“-”
price=price*10 所有售价乘以10 除法为“/”
售价和附件组合使用例子,把板块1的有附件的帖子售价全部设定为100

update pre_forum_thread set price=100 where fid='1' and and attachment ='1';

复制代码

5.批量删帖，谨慎使用啊，我以前经常用，现在很少了。把论坛列表页显示设定为300，在前台几个批量删除就OK。当然使用下面的SQL不管多少帖子只有1秒。
删除版块53下所有帖子

delete from pre_forum_thread where fid = '53';

复制代码

删除82版块没有附件的所有帖子

delete from pre_forum_thread where fid = '82' and attachment ='0';

复制代码

删除53版块下，所有指定时间前的帖子

delete from pre_forum_thread where fid = '51' and dateline<1262275200;

复制代码

6.权限设置
修改05年11月29日21点33份20秒前的主题阅读权限100

update pre_forum_thread set readperm=100 where dateline<1133300000;

复制代码

cdb_attachments0(0-9)的附件默认阅读权限为1

update cdb_attachments0 set readperm=1;

复制代码

from：凯旋森林

BOHOHOBO · 发表于 2011-9-3 14:11:10

好贴，，收藏了