discuzX3.4数据库改用utf8mb4字符集，实现emoji表情符

古城技术联盟

官方discuzX 3.4 默认是utf8,安装后无法发一些表情符。
/ r. q. U$ ^% S; U4 M; @
现在做测试改成 utf8mb4.
7 `4 K: |! A8 H4 C7 O& m# K. @测试机环境
centos 7.5
mysql 8.0.11
php 7.2.5
  ?; |& V; }" ?apache 2.4.6
, R( v3 L! k) o1 i+ R
一、全新安装
/ g# o: E1 N+ N" [
- R$ Y- A- B$ k) n! y) E! S: r' I" a下载补丁文件，替换加入到discuzX 3.4  2018.01.01官方原版文件中，即可完成安装，可以正常发表情符。
patch_utf8mb4.zip (47.35 KB, 下载次数: 2, 售价: 5 个城币)

2020-6-30 20:28 上传

点击文件名下载附件
discuzX3.4数据库改用utf8mb4字符集，实现emoji表情符

" o; d# I) y$ w( M1 H7 I
/ h1 [" s# s6 C6 Q7 O4 Y二、换服务器升级安装（内容更新中）

6 d" ^5 d4 Z9 a& i: [+ q1.导出旧空间上数据库的表结构文件struct.sql 和 数据文件data.sql  
# x0 o8 r: d( D* V5 A/ K1 d

1. mysqldump -d -u root -p$mysqlpass bbs > struct.sql
   " @) n* ?$ L% f# e' X
2. mysqldump -t -u root -p$mysqlpass bbs --default-character-set=UTF8 > data.sql

复制代码

2.修改表结构文件 struct.sql，

_ci结尾的校对集大小写不敏感，即不区分大小写,ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的.
凡是在以前导入中提示 ERROR 1062 (23000): Duplicate entry 'xxxx' for key 'username' 之类的提示，往往出在选用不当的校对集引起的。
- h8 D5 R1 Y! p5 A# _9 o8 b4 Q本次升级调试时使用了_ci的校对集，就遇到这个问题，象 'Avaloń' 与 'avalon' 被认为是重复，导致导入失败。
9 o& F2 v& {5 U0 ^* e& R3 u2 d, U
% e1 i6 s4 F7 E' B校对集 COLLATE=utf8mb4_bin 是区分大小写，可以防止旧库中有KEY属性的论坛用户名ABC、abc导入时被认为是重复的问题。
% a3 \. H2 Q# r* b- w
修改struct.sql

DEFAULT CHARSET=gbk 全部改成 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
varchar(255) 修改值
# m8 }, s% c) S4 vchar(255) 修改值
* j5 o# j9 E+ g$ n  v
callsed 文件内容
! S* G. p& v( H& w0 i% J' \( }9 `

1. /^CREATE TABLE `pre_common_addon`/,/^) ENGINE=MyISAM/s/`key` varchar(255)/`key` varchar(250)/
   + M" E8 T8 m9 A9 }$ P
2. /^CREATE TABLE `pre_common_admincp_perm`/,/^) ENGINE=MyISAM/s/`perm` varchar(255)/`perm` varchar(244)/
   ' l' Z3 }: S5 ~3 ]3 M. M& x8 O
3. /^CREATE TABLE `pre_common_advertisement_custom`/,/^) ENGINE=MyISAM/s/`name` varchar(255)/`name` varchar(250)/
   
4. /^CREATE TABLE `pre_common_cache`/,/^) ENGINE=MyISAM/s/`cachekey` varchar(255)/`cachekey` varchar(250)/
   0 T3 P) C% F; ?# t3 j
5. /^CREATE TABLE `pre_common_card`/,/^) ENGINE=MyISAM/s/`id` char(255)/`id` char(250)/
   
6. /^CREATE TABLE `pre_common_member_profile_setting`/,/^) ENGINE=MyISAM/s/`fieldid` varchar(255)/`fieldid` varchar(250)/
   ; m  \- a# s& e! ?: B5 v7 a
7. /^CREATE TABLE `pre_common_member_security`/,/^) ENGINE=MyISAM/s/`fieldid` varchar(255)/`fieldid` varchar(242)/
   * G' D& s; q  b2 X2 n: W
8. /^CREATE TABLE `pre_common_setting`/,/^) ENGINE=MyISAM/s/`skey` varchar(255)/`skey` varchar(250)/
   + F' J0 D6 P: t
9. /^CREATE TABLE `pre_forum_groupfield`/,/^) ENGINE=MyISAM/s/`type` varchar(255)/`type` varchar(242)/
   ; R6 r% f+ D0 ]$ R7 W* M
10. /^CREATE TABLE `pre_home_favorite`/,/^) ENGINE=MyISAM/s/`idtype` varchar(255)/`idtype` varchar(232)/
    
11. /^CREATE TABLE `pre_mobile_setting`/,/^) ENGINE=MyISAM/s/`skey` varchar(255)/`skey` varchar(250)/
    
12. /^CREATE TABLE `pre_portal_topic`/,/^) ENGINE=MyISAM/s/`name` varchar(255)/`name` varchar(250)/
    ; p  A! s- }$ _8 r
13. /^CREATE TABLE `cdb_uc_badwords`/,/^) ENGINE=MyISAM/s/`find` varchar(255)/`find` varchar(250)/
      Z( h* J2 r2 k4 X1 {% r
14. s/DEFAULT CHARSET=gbk/DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/

复制代码

/ d3 u/ T: q5 H5 o0 P1 `3 n+ m
( Z; o, w8 x, C* X$ E5 ?5 C; V在linux shell 下执行以下命令进行替换
) {4 E  n& Z- W- l& a2 K; W% B
, o3 \" M4 C6 e9 Z

1. sed -i -f callsed struct.sql

复制代码

: V5 f# D" j; M& l/ ?也可以在mysql 命令行下先修改原库(注意先备份数据库）

1. USE bbs;
   , ]3 B  Z" H8 P' D& T* y
2. 
   
3. ALTER TABLE `pre_common_addon` MODIFY COLUMN `key` varchar(250);
   % F7 l# [  R! P) t: J4 b* m: U: _
4. ALTER TABLE `pre_common_admincp_perm` MODIFY COLUMN `perm` varchar(244);
   
5. ALTER TABLE `pre_common_advertisement_custom` MODIFY COLUMN `name` varchar(250);
   " k  h9 M  L/ {! k
6. ALTER TABLE `pre_common_cache` MODIFY COLUMN `cachekey` varchar(250);
   
7. ALTER TABLE `pre_common_card` MODIFY COLUMN `id` char(250);
   " W1 O1 j( A6 `( i/ x" p5 c# |
8. ALTER TABLE `pre_common_member_profile_setting` MODIFY COLUMN `fieldid` varchar(250);
   
9. ALTER TABLE `pre_common_member_security` MODIFY COLUMN `fieldid` varchar(242);
   ; I% w( d( F8 r
10. ALTER TABLE `pre_common_setting` MODIFY COLUMN `skey` varchar(250);
    
11. ALTER TABLE `pre_forum_groupfield` MODIFY COLUMN `type` varchar(242);
    0 \# H) X* s* D& m  k
12. ALTER TABLE `pre_home_favorite` MODIFY COLUMN `idtype` varchar(232);
    ) s& M, _* a/ S; ?
13. ALTER TABLE `pre_mobile_setting` MODIFY COLUMN `skey` varchar(250);
    
14. ALTER TABLE `pre_portal_topic` MODIFY COLUMN `name` varchar(250);
    
15. ALTER TABLE `cdb_uc_badwords` MODIFY COLUMN `find` varchar(250);

复制代码

3 Y5 s2 ^6 u# ]3 I5 [: _0 u' C
3.在新空间的 mysql命令行下，导入struct.sql 和 数据文件data.sql  

  d: {$ H  M# G# q0 `& e% P

1. USE bbs;
   0 _/ c2 g% C3 a7 x) F
2. SOURCE struct.sql;
   
3. SOURCE data.sql

复制代码

0 H# D9 b. \+ G; Q% Q8 l/ n, E1 M% O0 X4.恢复旧空间的web数据库到新空间上，修改以下文件，将第一次出现连接库的'gbk' 改为 'utf8mb4'.每个文件只改一处。
config/config_global.php
0 R: R; S8 D$ U* T) @8 ]config/config_global.php
uc_server/data/config.inc.php

5.按官方升级要求，上传官方版文件替换，进后台 > 工具 > 更新缓存。
- `6 [0 [, B5 C; @& m另外还有source/class/table/下面三个文件需要更新，具体见本帖上方发的全新安装补丁包中提取。
table_common_usergroup.php
table_forum_announcement.php
table_forum_forum.php

6.界面 > 编辑器设置 > Discuz!代码，删除那些不可用的标签，可解决因使用Discuz标签而导致帖子内容无法正常显示的问题。

1 l& p' W8 `# k8 d$ u4 @8 ]7.升级安装成功
$ p* f) ~; h/ U0 H, r6 F
. t/ s/ z$ G+ e# Z1 u3 A目前仍存在问题：编辑正在投放的广告位时无内容显示。
" h8 G  ]. @! W% z
这次还发现有一个分页标签，点下方的2可以看下一页，一直没用过这个标签，第一次用。
. V* r) @+ `# q" w4 q# M# m
如果ENGINE使用InnoDB，则修改
ENGINE=MyISAM 全部替换为 ENGINE=InnoDB
. I6 ~& K: G7 h% y
并将所有有auto_increment 属性的，加为KEY。
. N) C/ m2 B/ k" [% |如 id 有auto_increment属性，则在相应段加上一行 KEY  `id`  (`id`),  (如果已有完全相同这一行就不要加）


. G, j4 l" t  I: e7 g

7 r' _5 M! N; b3 Q1 ~7 }% b' [                               
登录/注册后可看大图

$ R+ j5 L6 o4 Z' F3 r+ }, Z" U# A

以下是这次解决问题过程，没兴趣的可不看：

3 w7 W' I! L( L9 J1.常规安装，然后导出数据库，修改默认字符集
( c- {4 |2 b: S9 B9 V3 `0 f+ V
; o8 V- ^% z- N" |/ {4 L  zsed -i '/DEFAULT CHARSET=utf8[^m]/s/DEFAULT CHARSET=utf8/&mb4/' bbs.sql
% x1 b5 D$ l3 J5 _

- n. |2 v( K3 M. G: w4 r$ \2.如果直接导入bbs.sql，会出错，提示如下：

- n7 ~( K( z5 @& bERROR 1071 (42000) at line 104: Specified key was too long; max key length is 1000 bytes
: n. y. N- Q( IERROR 1146 (42S02) at line 115: Table 'bbs.pre_common_admincp_perm' doesn't exist
4 h* f& p0 D! p0 Y7 ]# oERROR 1146 (42S02) at line 116: Table 'bbs.pre_common_admincp_perm' doesn't exist
- D6 p" f* G$ _1 W* y5 V; eERROR 1146 (42S02) at line 117: Table 'bbs.pre_common_admincp_perm' doesn't exist
1 g7 {  h) C( ?/ u' KERROR 1146 (42S02) at line 118: Table 'bbs.pre_common_admincp_perm' doesn't exist
/ {. b8 ^6 E6 e- mERROR 1071 (42000) at line 631: Specified key was too long; max key length is 1000 bytes
+ Y8 M# ^6 e7 z/ u9 m  U" \; L' DERROR 1146 (42S02) at line 643: Table 'bbs.pre_common_cache' doesn't exist
ERROR 1146 (42S02) at line 644: Table 'bbs.pre_common_cache' doesn't exist
- z$ L- m! N3 G! H2 P% b4 k9 R# T3 H5 XERROR 1146 (42S02) at line 645: Table 'bbs.pre_common_cache' doesn't exist
ERROR 1071 (42000) at line 655: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 677: Table 'bbs.pre_common_card' doesn't exist
; F, V9 A' M# e& uERROR 1146 (42S02) at line 678: Table 'bbs.pre_common_card' doesn't exist
ERROR 1146 (42S02) at line 679: Table 'bbs.pre_common_card' doesn't exist
ERROR 1071 (42000) at line 1831: Specified key was too long; max key length is 1000 bytes
; O/ {# f* T. V/ R# {. B0 QERROR 1146 (42S02) at line 1857: Table 'bbs.pre_common_member_profile_setting' doesn't exist
ERROR 1146 (42S02) at line 1858: Table 'bbs.pre_common_member_profile_setting' doesn't exist
ERROR 1146 (42S02) at line 1859: Table 'bbs.pre_common_member_profile_setting' doesn't exist
ERROR 1146 (42S02) at line 1860: Table 'bbs.pre_common_member_profile_setting' doesn't exist
& x: M" j$ g2 \3 d7 VERROR 1071 (42000) at line 1870: Specified key was too long; max key length is 1000 bytes
  A* C- y# n$ W6 Q: m, [, bERROR 1146 (42S02) at line 1888: Table 'bbs.pre_common_member_security' doesn't exist
ERROR 1146 (42S02) at line 1889: Table 'bbs.pre_common_member_security' doesn't exist
ERROR 1146 (42S02) at line 1890: Table 'bbs.pre_common_member_security' doesn't exist
ERROR 1071 (42000) at line 2673: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 2684: Table 'bbs.pre_common_setting' doesn't exist
ERROR 1146 (42S02) at line 2685: Table 'bbs.pre_common_setting' doesn't exist
ERROR 1146 (42S02) at line 2686: Table 'bbs.pre_common_setting' doesn't exist
0 k" t4 P; B# U" g) k; a$ BERROR 1146 (42S02) at line 2687: Table 'bbs.pre_common_setting' doesn't exist
9 F% A3 ^: c" C8 h7 _% {! V/ x& uERROR 1071 (42000) at line 4800: Specified key was too long; max key length is 1000 bytes
1 y5 w# S  h9 y0 N! g7 ^ERROR 1146 (42S02) at line 4816: Table 'bbs.pre_forum_groupfield' doesn't exist
6 Q9 p/ `# F6 `" s# FERROR 1146 (42S02) at line 4817: Table 'bbs.pre_forum_groupfield' doesn't exist
ERROR 1146 (42S02) at line 4818: Table 'bbs.pre_forum_groupfield' doesn't exist
ERROR 1071 (42000) at line 7101: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 7120: Table 'bbs.pre_home_favorite' doesn't exist
2 a% E. V2 b- [& g2 a4 q* e0 S: @8 |ERROR 1146 (42S02) at line 7121: Table 'bbs.pre_home_favorite' doesn't exist
ERROR 1146 (42S02) at line 7122: Table 'bbs.pre_home_favorite' doesn't exist
! J9 v+ t" ^/ v2 b6 W% ?- EERROR 1071 (42000) at line 7797: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 7808: Table 'bbs.pre_mobile_setting' doesn't exist
ERROR 1146 (42S02) at line 7809: Table 'bbs.pre_mobile_setting' doesn't exist
ERROR 1146 (42S02) at line 7810: Table 'bbs.pre_mobile_setting' doesn't exist
ERROR 1146 (42S02) at line 7811: Table 'bbs.pre_mobile_setting' doesn't exist
8 ^( c) E+ b9 \& ~  A8 `5 mERROR 1071 (42000) at line 8567: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 8582: Table 'bbs.pre_ucenter_badwords' doesn't exist
. \1 D9 d( B+ m" W7 vERROR 1146 (42S02) at line 8583: Table 'bbs.pre_ucenter_badwords' doesn't exist
# @/ L5 m8 @! N+ [9 M/ kERROR 1146 (42S02) at line 8584: Table 'bbs.pre_ucenter_badwords' doesn't exist
0 i; t, B5 i& ^" [" X3 ?: U
处理方法1：
-----------------
根据 ERROR 1071 (42000) at line 104: Specified key was too long; max key length is 1000 bytes 提示，查找 bbs.sql 文件相应行104行及后续10行：
2 c4 i* J, q( L  l; N
sed -n '104,114p' bbs.sql

8 ^1 B0 U7 `# w
7 F* Z$ w; }1 H8 M6 Y, tCREATE TABLE `pre_common_admincp_perm` (
' h! ]. [3 Q; c6 B" R- m% C  `cpgroupid` smallint(6) unsigned NOT NULL,
  `perm` varchar(255) NOT NULL,
8 W7 j, L" a+ z  C# h0 {6 n  UNIQUE KEY `cpgroupperm` (`cpgroupid`,`perm`)
* e1 N3 N. V# }2 L0 l9 S3 N1 ~8 [) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
/ N( X( ?2 {+ a9 e' U" G* n5 V/*!40101 SET character_set_client = @saved_cs_client */;
. i( A; A; L' P0 A4 |; l5 {# K
/ Z3 H+ D/ o( H8 k: f6 u--
7 f2 j8 J5 P9 G* }6 c  O-- Dumping data for table `pre_common_admincp_perm`
! L" m5 [- ~( o, a1 @--

可以看出 UNIQUE KEY `cpgroupperm` (`cpgroupid`,`perm`) 定义的长度 是 (6+255) * 4 > 1000 , 只要把`perm` varchar 值改小，使其总和不要超过1000即可。
现在改为 230.
' k3 F; |+ M: g8 ^3 I
/ z9 a2 ~" |- P, }) K# msed -i '106s/255/230/' bbs.sql
- k6 D! q+ I. ~9 K- ?) o
$ Y! `- N  r+ M5 ?' g
其他类似提示的行做同样的修改，成功导入数据库。
2 I+ d3 z6 V. ~* e( v; L0 e+ E
, j/ p( _  {9 c$ A处理方法2：
-----------------
根据上面提示中出现的所有表，对安装文件先做处理

9 Q) @# p9 ~0 Ypre_ucenter_badwords 表：对应查 uc_badwords  ，从 uc_server/install/uc.sql  修改
其他表在 install/data/install.sql 修改
( U( n8 p+ V& b, ]
% [& ^8 P5 o* ~* w$ v" x官方原始文件 config 下的所有.php文件中的 utf8 都改为 utf8mb4
' l5 G) b3 j8 r' L" S- c  Y注意 utf-8 不要改动。
4 S0 \1 w/ {0 c9 S( j, x
改好以下文件
( J6 H! V  W! |- c$ ?, @, Y  U4 {install/data/install.sql
uc_server/install/uc.sql

修改 install/include/install_var.php
( E  k' Z- S2 \# C) A9 {  ?# W
) |  v+ `0 S) s9 y: T' P( x- Ydefine('DBCHARSET', 'utf8');     改为    define('DBCHARSET', 'utf8mb4');
2 X1 g3 J: X4 h. C
4 P: S7 O2 a$ M; h8 p再进行安装。
4 P" C6 P! j) i# j) ]
& v( ~6 U& t, k6 B! e5 Y5 }8 X* k; f( Y现在发帖，就可以正常使用表情符了,用户名都可以用表情符注册！