原来用3.1,执行了用户优化用户表就分成了两个,有什么问题我就不说了,前两天升级了DX3.3顺便把用户表合并清理了,具体步骤如下:
版本:DX3.3
工具:phpmyadmin
说明:我也是按照论坛一位朋友的操作来的,写出来一是给需要做合并的朋友参考,另外也给自己留个记录。。。,数据库操作有风险,自己注意备份,出了问题别来骂我就好,我只是把自己的操作步骤写出来,不证明别人都一定没问题。。。。
-----------------------------------------------------------------------------------------------------
建两个临时表备用,复制主表格式
create table pre_common_member_temp like pre_common_member;
create table pre_common_member_temp2 like pre_common_member;
将主表数据插入临表一
insert into pre_common_member_temp select * from pre_common_member;
备份存档表
create table pre_common_member_archive_bak like pre_common_member_archive;
insert into pre_common_member_archive_bak select * from pre_common_member_archive;
将存档表数据插入临表一
insert ignore into pre_common_member_temp select * from pre_common_member_archive;
注:insert ignore into (忽略数据库中已经存在 的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。)
将临表一的数据去重复排序插入临表二
insert into pre_common_member_temp2 select * from pre_common_member_temp order by uid;
主表改成bak备份,临表二改成主表名,清空存档表的数据
ALTER TABLE pre_common_member RENAME TO pre_common_member_bak;
ALTER TABLE pre_common_member_temp2 RENAME TO pre_common_member;
TRUNCATE TABLE `pre_common_member_archive`;
除了这个表pre_common_member,还有pre_common_member_field_home,pre_common_member_profile,pre_common_member_status,pre_common_member_count(最后这个不确定,忘记了,好像一共是5个)等几个带archive的,都这样合并为一个,并保留个空的archive表。
快捷操作提示:
可以将以上操作语句分别替换为下面需要执行SQL的表名即可:
【总共6个表,上面的操作只是操作的下面列表的第一个,请继续执行余下5个表】
---------------------------------------------------
pre_common_member
pre_common_member_count
pre_common_member_field_forum
pre_common_member_field_home
pre_common_member_profile
pre_common_member_status
--------------------------------------------------
快捷SQL执行语句:
create table pre_common_member_count_temp like pre_common_member_count;
create table pre_common_member_count_temp2 like pre_common_member_count;
insert into pre_common_member_count_temp select * from pre_common_member_count;
create table pre_common_member_count_archive_bak like pre_common_member_count_archive;
insert into pre_common_member_count_archive_bak select * from pre_common_member_count_archive;
insert into pre_common_member_count_temp select * from pre_common_member_count_archive;
insert into pre_common_member_count_temp2 select * from pre_common_member_count_temp order by uid;
ALTER TABLE pre_common_member_count RENAME TO pre_common_member_count_bak;
ALTER TABLE pre_common_member_count_temp2 RENAME TO pre_common_member_count;
TRUNCATE TABLE `pre_common_member_count_archive`;
create table pre_common_member_field_forum_temp like pre_common_member_field_forum;
create table pre_common_member_field_forum_temp2 like pre_common_member_field_forum;
insert into pre_common_member_field_forum_temp select * from pre_common_member_field_forum;
create table pre_common_member_field_forum_archive_bak like pre_common_member_field_forum_archive;
insert into pre_common_member_field_forum_archive_bak select * from pre_common_member_field_forum_archive;
insert into pre_common_member_field_forum_temp select * from pre_common_member_field_forum_archive;
insert into pre_common_member_field_forum_temp2 select * from pre_common_member_field_forum_temp order by uid;
ALTER TABLE pre_common_member_field_forum RENAME TO pre_common_member_field_forum_bak;
ALTER TABLE pre_common_member_field_forum_temp2 RENAME TO pre_common_member_field_forum;
TRUNCATE TABLE `pre_common_member_field_forum_archive`;
create table pre_common_member_field_home_temp like pre_common_member_field_home;
create table pre_common_member_field_home_temp2 like pre_common_member_field_home;
insert into pre_common_member_field_home_temp select * from pre_common_member_field_home;
create table pre_common_member_field_home_archive_bak like pre_common_member_field_home_archive;
insert into pre_common_member_field_home_archive_bak select * from pre_common_member_field_home_archive;
insert into pre_common_member_field_home_temp select * from pre_common_member_field_home_archive;
insert into pre_common_member_field_home_temp2 select * from pre_common_member_field_home_temp order by uid;
ALTER TABLE pre_common_member_field_home RENAME TO pre_common_member_field_home_bak;
ALTER TABLE pre_common_member_field_home_temp2 RENAME TO pre_common_member_field_home;
TRUNCATE TABLE `pre_common_member_field_home_archive`;
create table pre_common_member_profile_temp like pre_common_member_profile;
create table pre_common_member_profile_temp2 like pre_common_member_profile;
insert into pre_common_member_profile_temp select * from pre_common_member_profile;
create table pre_common_member_profile_archive_bak like pre_common_member_profile_archive;
insert into pre_common_member_profile_archive_bak select * from pre_common_member_profile_archive;
insert into pre_common_member_profile_temp select * from pre_common_member_profile_archive;
insert into pre_common_member_profile_temp2 select * from pre_common_member_profile_temp order by uid;
ALTER TABLE pre_common_member_profile RENAME TO pre_common_member_profile_bak;
ALTER TABLE pre_common_member_profile_temp2 RENAME TO pre_common_member_profile;
TRUNCATE TABLE `pre_common_member_profile_archive`;
create table pre_common_member_status_temp like pre_common_member_status;
create table pre_common_member_status_temp2 like pre_common_member_status;
insert into pre_common_member_status_temp select * from pre_common_member_status;
create table pre_common_member_status_archive_bak like pre_common_member_status_archive;
insert into pre_common_member_status_archive_bak select * from pre_common_member_status_archive;
insert into pre_common_member_status_temp select * from pre_common_member_status_archive;
insert into pre_common_member_status_temp2 select * from pre_common_member_status_temp order by uid;
ALTER TABLE pre_common_member_status RENAME TO pre_common_member_status_bak;
ALTER TABLE pre_common_member_status_temp2 RENAME TO pre_common_member_status;
TRUNCATE TABLE `pre_common_member_status_archive`;
--------------------------------------------------
上面做完基本就没事了,有强迫症的可以删除带有
_archive的空表,备份的
_bak表,
_temp的表,都可以删除。
DROP TABLE `pre_common_member_archive`;
DROP TABLE `pre_common_member_count_archive`;
DROP TABLE `pre_common_member_field_forum_archive`;
DROP TABLE `pre_common_member_field_home_archive`;
DROP TABLE `pre_common_member_profile_archive`;
DROP TABLE `pre_common_member_status_archive`;
删除带有archive的空表,更新缓存的时候出现这个错误,而且注册不了(在注册的最后一步会出现不跳转的情况)
(1146) Table 'db_web411070.common_member_archive' doesn't existSELECT COUNT(*) FROM common_member_archive
解决方法:
即在站长—数据库—升级(Discuz! 数据库升级 - 请将数据库升级语句粘贴在下面:)中执行语句 :
DELETE FROM `pre_common_setting` WHERE `skey` = 'membersplit';
若没有找到执行语句的输入窗口 则修改config/config_global.php 当中的 $_config[admincp][runquery] 设置修改为 1
(为了安全执行完该语句后 确认解决了1146错误后 再将配置改回来 ) 刷新后再输入执行。
为了数据安全执行该语句前建议备份数据!!!