承接
网站维护 网站升级 数据转换 页面美化 插件二开 BUG修复
热门
插件 板摸 教程 分享 攻略 经验 电脑 服务器
分类
数据转换 网站升级 故障排查 BUG修复 页面美化 插件升级
草根吧»论坛首页 › 站长综合资源区 › 精品教程 › discuz x3.3 x3.2存档表、用户表优化分表后用户表合并过 ...
返回列表 发新帖
查看: 4230|回复: 2

discuz x3.3 x3.2存档表、用户表优化分表后用户表合并过程教程

[复制链接]
民审-M

1万

热度

1万

元宝

1万

贡献

金牌草根
发表于 2017-7-13 14:47:55 | 显示全部楼层 |阅读模式
原来用3.1，执行了用户优化用户表就分成了两个，有什么问题我就不说了，前两天升级了DX3.3顺便把用户表合并清理了，具体步骤如下：
版本：DX3.3
工具：phpmyadmin
说明：我也是按照论坛一位朋友的操作来的，写出来一是给需要做合并的朋友参考，另外也给自己留个记录。。。，数据库操作有风险，自己注意备份，出了问题别来骂我就好，我只是把自己的操作步骤写出来，不证明别人都一定没问题。。。。
-----------------------------------------------------------------------------------------------------
建两个临时表备用，复制主表格式
  1. create table  pre_common_member_temp like  pre_common_member;
复制代码
  1. create table  pre_common_member_temp2 like  pre_common_member;
复制代码

将主表数据插入临表一
  1. insert into pre_common_member_temp  select * from pre_common_member;
复制代码

备份存档表
  1. create table  pre_common_member_archive_bak like  pre_common_member_archive;
复制代码
  1. insert into  pre_common_member_archive_bak select * from  pre_common_member_archive;
复制代码


将存档表数据插入临表一
  1. insert ignore into pre_common_member_temp  select * from pre_common_member_archive;
复制代码
注：insert ignore into  （忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。）

将临表一的数据去重复排序插入临表二
  1. insert into pre_common_member_temp2  select * from pre_common_member_temp order by uid;
复制代码

主表改成bak备份，临表二改成主表名，清空存档表的数据
  1. ALTER  TABLE pre_common_member RENAME TO pre_common_member_bak;
复制代码
  1. ALTER  TABLE pre_common_member_temp2 RENAME TO pre_common_member;
复制代码
  1. 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执行语句：
  1. create table  pre_common_member_count_temp like  pre_common_member_count;
  2. create table  pre_common_member_count_temp2 like  pre_common_member_count;
  3. insert into pre_common_member_count_temp  select * from pre_common_member_count;
  4. create table  pre_common_member_count_archive_bak like  pre_common_member_count_archive;
  5. insert into  pre_common_member_count_archive_bak select * from  pre_common_member_count_archive;
  6. insert into pre_common_member_count_temp  select * from pre_common_member_count_archive;
  7. insert into pre_common_member_count_temp2  select * from pre_common_member_count_temp order by uid;
  8. ALTER  TABLE pre_common_member_count RENAME TO pre_common_member_count_bak;
  9. ALTER  TABLE pre_common_member_count_temp2 RENAME TO pre_common_member_count;
  10. TRUNCATE TABLE  `pre_common_member_count_archive`;
复制代码
  1. create table  pre_common_member_field_forum_temp like  pre_common_member_field_forum;
  2. create table  pre_common_member_field_forum_temp2 like  pre_common_member_field_forum;
  3. insert into pre_common_member_field_forum_temp  select * from pre_common_member_field_forum;
  4. create table  pre_common_member_field_forum_archive_bak like  pre_common_member_field_forum_archive;
  5. insert into  pre_common_member_field_forum_archive_bak select * from  pre_common_member_field_forum_archive;
  6. insert into pre_common_member_field_forum_temp  select * from pre_common_member_field_forum_archive;
  7. insert into pre_common_member_field_forum_temp2  select * from pre_common_member_field_forum_temp order by uid;
  8. ALTER  TABLE pre_common_member_field_forum RENAME TO pre_common_member_field_forum_bak;
  9. ALTER  TABLE pre_common_member_field_forum_temp2 RENAME TO pre_common_member_field_forum;
  10. TRUNCATE TABLE  `pre_common_member_field_forum_archive`;
复制代码
  1. create table  pre_common_member_field_home_temp like  pre_common_member_field_home;
  2. create table  pre_common_member_field_home_temp2 like  pre_common_member_field_home;
  3. insert into pre_common_member_field_home_temp  select * from pre_common_member_field_home;
  4. create table  pre_common_member_field_home_archive_bak like  pre_common_member_field_home_archive;
  5. insert into  pre_common_member_field_home_archive_bak select * from  pre_common_member_field_home_archive;
  6. insert into pre_common_member_field_home_temp  select * from pre_common_member_field_home_archive;
  7. insert into pre_common_member_field_home_temp2  select * from pre_common_member_field_home_temp order by uid;
  8. ALTER  TABLE pre_common_member_field_home RENAME TO pre_common_member_field_home_bak;
  9. ALTER  TABLE pre_common_member_field_home_temp2 RENAME TO pre_common_member_field_home;
  10. TRUNCATE TABLE  `pre_common_member_field_home_archive`;
复制代码
  1. create table  pre_common_member_profile_temp like  pre_common_member_profile;
  2. create table  pre_common_member_profile_temp2 like  pre_common_member_profile;
  3. insert into pre_common_member_profile_temp  select * from pre_common_member_profile;
  4. create table  pre_common_member_profile_archive_bak like  pre_common_member_profile_archive;
  5. insert into  pre_common_member_profile_archive_bak select * from  pre_common_member_profile_archive;
  6. insert into pre_common_member_profile_temp  select * from pre_common_member_profile_archive;
  7. insert into pre_common_member_profile_temp2  select * from pre_common_member_profile_temp order by uid;
  8. ALTER  TABLE pre_common_member_profile RENAME TO pre_common_member_profile_bak;
  9. ALTER  TABLE pre_common_member_profile_temp2 RENAME TO pre_common_member_profile;
  10. TRUNCATE TABLE  `pre_common_member_profile_archive`;
复制代码
  1. create table  pre_common_member_status_temp like  pre_common_member_status;
  2. create table  pre_common_member_status_temp2 like  pre_common_member_status;
  3. insert into pre_common_member_status_temp  select * from pre_common_member_status;
  4. create table  pre_common_member_status_archive_bak like  pre_common_member_status_archive;
  5. insert into  pre_common_member_status_archive_bak select * from  pre_common_member_status_archive;
  6. insert into pre_common_member_status_temp  select * from pre_common_member_status_archive;
  7. insert into pre_common_member_status_temp2  select * from pre_common_member_status_temp order by uid;
  8. ALTER  TABLE pre_common_member_status RENAME TO pre_common_member_status_bak;
  9. ALTER  TABLE pre_common_member_status_temp2 RENAME TO pre_common_member_status;
  10. TRUNCATE TABLE  `pre_common_member_status_archive`;
复制代码

--------------------------------------------------
上面做完基本就没事了，有强迫症的可以删除带有_archive的空表，备份的_bak表，_temp的表，都可以删除。
  1. DROP TABLE `pre_common_member_archive`;
  2. DROP TABLE `pre_common_member_count_archive`;
  3. DROP TABLE `pre_common_member_field_forum_archive`;
  4. DROP TABLE `pre_common_member_field_home_archive`;
  5. DROP TABLE `pre_common_member_profile_archive`;
  6. DROP TABLE `pre_common_member_status_archive`;
复制代码


删除带有archive的空表，更新缓存的时候出现这个错误，而且注册不了（在注册的最后一步会出现不跳转的情况）

(1146) Table 'db_web411070.common_member_archive' doesn't existSELECT COUNT(*) FROM common_member_archive
解决方法：

即在站长—数据库—升级(Discuz! 数据库升级 - 请将数据库升级语句粘贴在下面中执行语句 ：
  1. DELETE FROM `pre_common_setting` WHERE `skey` = 'membersplit';
复制代码

若没有找到执行语句的输入窗口 则修改config/config_global.php 当中的 $_config[admincp][runquery] 设置修改为 1
(为了安全执行完该语句后 确认解决了1146错误后 再将配置改回来 )  刷新后再输入执行。

为了数据安全执行该语句前建议备份数据！！！

存档表, 分表, 优化, 速度, 积分

相关帖子

本文来源于网友学习研究交流 www.caogen8.co，请以学习研究交流为主。
如果您没有贡献积分，可以直接免费领取，免费领取
如果你需要加入本学习研究交流，请以学习研究交流为目的，免责声明
如果找不到您要的资源，请搜索一下，点击搜索
回复 <

举报

提现请阅读《贡献值提现说明（申请提现须知）》,恶意提现、积分作弊直接封禁!
懒羊羊1已认证网站

167

热度

795

元宝

60

贡献

步入草根
发表于 2018-12-3 09:13:24 | 显示全部楼层
帖子分表怎么搞啊
回复 支持 反对

举报

懒羊羊1已认证网站

167

热度

795

元宝

60

贡献

步入草根
发表于 2018-12-3 09:13:40 | 显示全部楼层
帖子分表还原
回复 支持 反对

举报

返回列表 发新帖
 懒得打字嘛，点击右侧快捷回复【最新发布】   【赞助草根吧享更多权益】
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

收藏帖子 返回列表 搜索

Powered by Discuz! X5.0

© 2001-2026 Discuz! Team.

小黑屋|手机版|草根吧