Ucenter 1.5 快速升级到 1.6 版本的办法
因discuz论坛需要升级到Discuz! X2 版本，前提必须把 Ucenter 升级到 当前最新版本1.6 版本。按照官方的升级程序，如果数据量非常大的话，就很难升级成功了，要么升级时间过长，要么直接卡住不动了。由于Ucenter 升级到1.6主要是数据库短消息的升级，所以可以采用执行SQL语句的办法来进行升级。
一、升级准备
1.备份原最新数据库和应用程序
2.手动修改升级程序中SQL语句中的表明前缀。
3.执行SQL语句 alter table uc_pms add index mmd (msgfromid,msgtoid,dateline) 建立索引
防止因短消息表太大，出现 502 的错误。
二、升级
原程序以下目录要保留（没必要按照官方教程全部覆盖，以免产生过多垃圾程序文件）：
data/config.inc.php (注意修改数据库IP地址)
data/cache
data/logs （uc登录日志文件，不影响系统使用）
data/avatar (头像图片目录，可安装成功之后建立软连接)
ps:注意文件夹权限。
执行SQL语句进行数据库升级：
建表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
DROP TABLE IF EXISTS pre_ucenter_pm_members; CREATE TABLE pre_ucenter_pm_members ( plid mediumint(8) unsigned NOT NULL default '0', uid mediumint(8) unsigned NOT NULL default '0', isnew tinyint(1) unsigned NOT NULL default '0', pmnum int(10) unsigned NOT NULL default '0', lastupdate int(10) unsigned NOT NULL default '0', lastdateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (plid,uid), KEY isnew (isnew), KEY lastdateline (uid,lastdateline), KEY lastupdate (uid,lastupdate) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_lists; CREATE TABLE pre_ucenter_pm_lists ( plid mediumint(8) unsigned NOT NULL auto_increment, authorid mediumint(8) unsigned NOT NULL default '0', pmtype tinyint(1) unsigned NOT NULL default '0', subject varchar(80) NOT NULL, members smallint(5) unsigned NOT NULL default '0', min_max varchar(17) NOT NULL, dateline int(10) unsigned NOT NULL default '0', lastmessage text NOT NULL, PRIMARY KEY (plid), KEY pmtype (pmtype), KEY min_max (min_max), KEY authorid (authorid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_indexes; CREATE TABLE pre_ucenter_pm_indexes ( pmid mediumint(8) unsigned NOT NULL auto_increment, plid mediumint(8) unsigned NOT NULL default '0', pmidold int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid), KEY pmidold (pmidold) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_0; CREATE TABLE pre_ucenter_pm_messages_0 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_1; CREATE TABLE pre_ucenter_pm_messages_1 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_2; CREATE TABLE pre_ucenter_pm_messages_2 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_3; CREATE TABLE pre_ucenter_pm_messages_3 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_4; CREATE TABLE pre_ucenter_pm_messages_4 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_5; CREATE TABLE pre_ucenter_pm_messages_5 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_6; CREATE TABLE pre_ucenter_pm_messages_6 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_7; CREATE TABLE pre_ucenter_pm_messages_7 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_8; CREATE TABLE pre_ucenter_pm_messages_8 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; DROP TABLE IF EXISTS pre_ucenter_pm_messages_9; CREATE TABLE pre_ucenter_pm_messages_9 ( pmid mediumint(8) unsigned NOT NULL default '0', plid mediumint(8) unsigned NOT NULL default '0', authorid mediumint(8) unsigned NOT NULL default '0', message text NOT NULL, delstatus tinyint(1) unsigned NOT NULL default '0', dateline int(10) unsigned NOT NULL default '0', PRIMARY KEY (pmid), KEY plid (plid,delstatus,dateline), KEY dateline (plid,dateline) ) TYPE=MyISAM; |
升级：
1、生成 pm_plists 表基础数据
1 2 3 4 5 6 7 |
INSERT INTO `pre_ucenter_pm_lists` (authorid, pmtype, subject, members, min_max, dateline,lastmessage) SELECT * from (SELECT msgfromid as authorid, 1 as pmtype, '' as subject, 2 as members, CONCAT(LEAST(msgfromid,msgtoid),'_',GREATEST(msgfromid,msgtoid)) as min_max, dateline, CONCAT('a:3:{s:12:"lastauthorid";s:',LENGTH(msgfromid),':"',msgfromid,'";s:10:"lastauthor";s:',LENGTH(msgfrom),':"',msgfrom,'";s:11:"lastsummary";s:',LENGTH(message),':"',LEFT(IF(LENGTH(subject) > 0 AND STRCMP(subject, message), CONCAT(subject, '\r\n', message), message),150),'";}') as lastmessage FROM `pre_ucenter_pms` WHERE related = 1 order by dateline desc) as tmp GROUP BY min_max; |
2、生成 pm_members 表基础数据
1 2 |
INSERT INTO `pre_ucenter_pm_members` (plid, uid, isnew, lastupdate) SELECT plid, SUBSTRING_INDEX(min_max, '_', 1), 0, 0 FROM `pre_ucenter_pm_lists`; INSERT INTO `pre_ucenter_pm_members` (plid, uid, isnew, lastupdate) SELECT plid, SUBSTRING_INDEX(min_max, '_', -1), 0, 0 FROM `pre_ucenter_pm_lists`; |
3、生成 pm_indexs 表数据，增加一个字段 pmidold，升级后删除此字段
1 |
INSERT INTO `pre_ucenter_pm_indexes` (plid, pmidold) SELECT l.plid, p.pmid FROM `pre_ucenter_pms` p LEFT JOIN `pre_ucenter_pm_lists` l ON CONCAT(LEAST(p.msgfromid, p.msgtoid), '_', GREATEST(p.msgfromid, p.msgtoid)) = l.min_max WHERE p.related = 1; |
4,插入短信分表数据
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO `pre_ucenter_pm_messages_0` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 0; INSERT INTO `pre_ucenter_pm_messages_1` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 1; INSERT INTO `pre_ucenter_pm_messages_2` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 2; INSERT INTO `pre_ucenter_pm_messages_3` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 3; INSERT INTO `pre_ucenter_pm_messages_4` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 4; INSERT INTO `pre_ucenter_pm_messages_5` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 5; INSERT INTO `pre_ucenter_pm_messages_6` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 6; INSERT INTO `pre_ucenter_pm_messages_7` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 7; INSERT INTO `pre_ucenter_pm_messages_8` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 8; INSERT INTO `pre_ucenter_pm_messages_9` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) > 0 AND STRCMP(p.subject, p.message), CONCAT(p.subject, '\r\n', p.message), p.message), IF(l.authorid = p.msgfromid, p.delstatus, IF(p.delstatus > 0, 3-p.delstatus, 0)), p.dateline FROM `pre_ucenter_pm_indexes` i LEFT JOIN `pre_ucenter_pms` p ON i.pmidold = p.pmid LEFT JOIN `pre_ucenter_pm_lists` l ON i.plid = l.plid WHERE i.plid % 10 = 9; |
5、更新 pm_members 表的最后时间
1 2 3 4 5 6 7 8 9 10 |
UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_0` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 0 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_1` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 1 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_2` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 2 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_3` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 3 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_4` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 4 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_5` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 5 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_6` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 6 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_7` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 7 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_8` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 8 AND m.plid = msg.plid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, dateline FROM `pre_ucenter_pm_messages_9` ORDER BY dateline DESC) msg SET m.lastdateline = msg.dateline WHERE m.plid % 10 = 9 AND m.plid = msg.plid; |
6,更新 pm_members 表的短消息总数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_0` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 0 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_0` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 0 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_1` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 1 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_1` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 1 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_2` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 2 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_2` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 2 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_3` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 3 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_3` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 3 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_4` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 4 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_4` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 4 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_5` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 5 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_5` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 5 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_6` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 6 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_6` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 6 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_7` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 7 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_7` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 7 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_8` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 8 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_8` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 8 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_9` WHERE delstatus IN (0,1) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 9 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1), l.authorid) = m.uid; UPDATE `pre_ucenter_pm_members` m, (SELECT plid, count(*) as pmnum FROM `pre_ucenter_pm_messages_9` WHERE delstatus IN (0,2) GROUP BY plid) msg, `pre_ucenter_pm_lists` l SET m.pmnum = msg.pmnum WHERE m.plid % 10 = 9 AND m.plid = msg.plid AND m.plid = l.plid AND IF(SUBSTRING_INDEX(l.min_max, '_', 1) = l.authorid, l.authorid, SUBSTRING_INDEX(l.min_max, '_', -1)) = m.uid; |
剩余处理：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
ALTER TABLE pre_ucenter_applications ADD COLUMN allowips text NOT NULL; ALTER TABLE pre_ucenter_applications CHANGE type type varchar(16) NOT NULL default ''; ALTER TABLE pre_ucenter_applications CHANGE name name varchar(20) NOT NULL default ''; ALTER TABLE pre_ucenter_applications CHANGE url url varchar(255) NOT NULL default ''; ALTER TABLE pre_ucenter_applications CHANGE authkey authkey varchar(255) NOT NULL default ''; ALTER TABLE pre_ucenter_applications CHANGE ip ip varchar(15) NOT NULL default ''; ALTER TABLE pre_ucenter_applications CHANGE viewprourl viewprourl varchar(255) NOT NULL default ''; ALTER TABLE pre_ucenter_applications CHANGE apifilename apifilename varchar(30) NOT NULL default 'uc.php'; ALTER TABLE pre_ucenter_applications CHANGE charset charset varchar(8) NOT NULL default ''; ALTER TABLE pre_ucenter_applications CHANGE dbcharset dbcharset varchar(8) NOT NULL default ''; ALTER TABLE pre_ucenter_applications CHANGE extra extra text NOT NULL; ALTER TABLE pre_ucenter_applications CHANGE tagtemplates tagtemplates text NOT NULL; REPLACE INTO pre_ucenter__settings (k, v) VALUES ('privatepmthreadlimit','25'); REPLACE INTO pre_ucenter__settings (k, v) VALUES ('chatpmthreadlimit','30'); REPLACE INTO pre_ucenter__settings (k, v) VALUES ('chatpmmemberlimit','35'); REPLACE INTO pre_ucenter__settings (k, v) VALUES ('version','1.6.0'); |
