Ucenter 1.5 快速升级到 1.6 版本的办法

由码匠 · 2011年12月12日

因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语句进行数据库升级：

建表：

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;

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 表基础数据

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) &gt; 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;

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 表基础数据

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`;

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，升级后删除此字段

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;

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,插入短信分表数据

INSERT INTO `pre_ucenter_pm_messages_0` (pmid, plid, authorid, message, delstatus, dateline) SELECT i.pmid, i.plid, p.msgfromid, IF(LENGTH(p.subject) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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;

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 表的最后时间

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;

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 表的短消息总数

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;

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;

剩余处理：

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');

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');

Ucenter 1.5 快速升级到 1.6 版本的办法

您可能还喜欢...

个人简介

近期文章

友情链接

Ucenter 1.5 快速升级到 1.6 版本的办法

您可能还喜欢...

Discuz! X 中 SESSION 机制讲解

大流量站点推荐使用稳定性更好的APC加速缓存

discuz!x2主题分表、帖子分表功能分析

个人简介

近期文章

标签

友情链接