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

古城技术联盟

官方discuzX 3.4 默认是utf8,安装后无法发一些表情符。
* L% x4 n% D( E1 w: g! \
6 o+ ?0 {& c7 R0 x' l1 E现在做测试改成 utf8mb4.
测试机环境
centos 7.5
+ S- N3 C! ]3 mmysql 8.0.11
php 7.2.5
1 T$ p0 Q. D5 c& n! R( W9 gapache 2.4.6

9 G7 b5 e; R1 m9 `% L一、全新安装

下载补丁文件，替换加入到discuzX 3.4  2018.01.01官方原版文件中，即可完成安装，可以正常发表情符。
9 p2 T9 z7 Y% }+ F' c) T$ D, G patch_utf8mb4.zip (47.35 KB, 下载次数: 2, 售价: 5 个城币)

2020-6-30 20:28 上传

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

& _6 A9 }$ ~( M  b1 ^9 S5 R( z
3 o2 L1 e$ T$ a+ w: W/ O1 B二、换服务器升级安装（内容更新中）
$ x; q( ^. g' u4 ^
: |5 g+ J+ H2 C7 w1.导出旧空间上数据库的表结构文件struct.sql 和 数据文件data.sql  

1. mysqldump -d -u root -p$mysqlpass bbs > struct.sql
   6 Y! u' H9 A5 \" R
2. mysqldump -t -u root -p$mysqlpass bbs --default-character-set=UTF8 > data.sql

复制代码

7 z* b8 y1 N3 p; r
2.修改表结构文件 struct.sql，

; ~! C, }" K0 Y! C" ]: r  R_ci结尾的校对集大小写不敏感，即不区分大小写,ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的.
# P) R2 g6 i6 x: ^% U: r* K; C凡是在以前导入中提示 ERROR 1062 (23000): Duplicate entry 'xxxx' for key 'username' 之类的提示，往往出在选用不当的校对集引起的。
4 O$ s5 f/ ^6 |  Z/ \2 C  V本次升级调试时使用了_ci的校对集，就遇到这个问题，象 'Avaloń' 与 'avalon' 被认为是重复，导致导入失败。
- l$ H) ~3 ~8 u% r7 e
/ X5 P: J. ^# O9 E- F* n校对集 COLLATE=utf8mb4_bin 是区分大小写，可以防止旧库中有KEY属性的论坛用户名ABC、abc导入时被认为是重复的问题。

$ |6 C5 C( N" `7 X; m' s) Z修改struct.sql
+ X' C" v. A; n9 K- `
DEFAULT CHARSET=gbk 全部改成 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
9 ~3 T' a1 w( Zvarchar(255) 修改值
char(255) 修改值

callsed 文件内容

1. /^CREATE TABLE `pre_common_addon`/,/^) ENGINE=MyISAM/s/`key` varchar(255)/`key` varchar(250)/
   0 |; E6 I& W- I2 f# ?4 Z+ M+ T1 X
2. /^CREATE TABLE `pre_common_admincp_perm`/,/^) ENGINE=MyISAM/s/`perm` varchar(255)/`perm` varchar(244)/
   
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)/
   
5. /^CREATE TABLE `pre_common_card`/,/^) ENGINE=MyISAM/s/`id` char(255)/`id` char(250)/
   8 S8 j" E$ R4 l- h9 P$ ?) V
6. /^CREATE TABLE `pre_common_member_profile_setting`/,/^) ENGINE=MyISAM/s/`fieldid` varchar(255)/`fieldid` varchar(250)/
   
7. /^CREATE TABLE `pre_common_member_security`/,/^) ENGINE=MyISAM/s/`fieldid` varchar(255)/`fieldid` varchar(242)/
   
8. /^CREATE TABLE `pre_common_setting`/,/^) ENGINE=MyISAM/s/`skey` varchar(255)/`skey` varchar(250)/
   ! q. V( g( d3 z5 R: J
9. /^CREATE TABLE `pre_forum_groupfield`/,/^) ENGINE=MyISAM/s/`type` varchar(255)/`type` varchar(242)/
   2 ]: N- J: W( r" U* M! I
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)/
    - \; e1 Y8 C" h8 ]
12. /^CREATE TABLE `pre_portal_topic`/,/^) ENGINE=MyISAM/s/`name` varchar(255)/`name` varchar(250)/
    
13. /^CREATE TABLE `cdb_uc_badwords`/,/^) ENGINE=MyISAM/s/`find` varchar(255)/`find` varchar(250)/
      F! x' |7 P9 k, C
14. s/DEFAULT CHARSET=gbk/DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/

复制代码

8 l% A$ e& z( r5 j$ N) ]9 f在linux shell 下执行以下命令进行替换

' x6 L2 u4 Q- p, T. c

1. sed -i -f callsed struct.sql

复制代码

6 e' V8 z1 V6 W5 i也可以在mysql 命令行下先修改原库(注意先备份数据库）
' l% d9 D6 c7 O

1. USE bbs;
   # N3 f% q6 J6 f  w0 m
2. 
   
3. ALTER TABLE `pre_common_addon` MODIFY COLUMN `key` varchar(250);
   
4. ALTER TABLE `pre_common_admincp_perm` MODIFY COLUMN `perm` varchar(244);
   
5. ALTER TABLE `pre_common_advertisement_custom` MODIFY COLUMN `name` varchar(250);
   - \) H1 r1 C5 f: I' V
6. ALTER TABLE `pre_common_cache` MODIFY COLUMN `cachekey` varchar(250);
   - v8 z, k6 R: I/ @4 C3 t# W
7. ALTER TABLE `pre_common_card` MODIFY COLUMN `id` char(250);
   ) q/ c( E( z+ w
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);
   " c! v" I, X  i# |, B
10. ALTER TABLE `pre_common_setting` MODIFY COLUMN `skey` varchar(250);
    
11. ALTER TABLE `pre_forum_groupfield` MODIFY COLUMN `type` varchar(242);
    0 G! A& l7 L. f0 X( @, G% k9 y- J7 p
12. ALTER TABLE `pre_home_favorite` MODIFY COLUMN `idtype` varchar(232);
    0 A9 u* `2 o  K5 z
13. ALTER TABLE `pre_mobile_setting` MODIFY COLUMN `skey` varchar(250);
    ) s: |4 y; Q0 e& K( V) Z
14. ALTER TABLE `pre_portal_topic` MODIFY COLUMN `name` varchar(250);
    $ H* M5 |% u, ^; G. m
15. ALTER TABLE `cdb_uc_badwords` MODIFY COLUMN `find` varchar(250);

复制代码

0 D( {% N9 P% r7 o0 d/ I: D0 V3.在新空间的 mysql命令行下，导入struct.sql 和 数据文件data.sql  
( g5 [$ e4 U/ e# W7 u8 R0 J
/ x" \) b4 R" O  v! h1 V3 X$ V

1. USE bbs;
   
2. SOURCE struct.sql;
   : B5 F& I0 @+ p9 V+ ?' v* Y+ a
3. SOURCE data.sql

复制代码

4.恢复旧空间的web数据库到新空间上，修改以下文件，将第一次出现连接库的'gbk' 改为 'utf8mb4'.每个文件只改一处。
config/config_global.php
config/config_global.php
! p% G2 b4 w/ r8 Buc_server/data/config.inc.php

5.按官方升级要求，上传官方版文件替换，进后台 > 工具 > 更新缓存。
) d# B1 F1 h" v8 e* l0 |另外还有source/class/table/下面三个文件需要更新，具体见本帖上方发的全新安装补丁包中提取。
) w# Z7 `0 a) T- @table_common_usergroup.php
table_forum_announcement.php
3 n* o7 V5 E* L0 vtable_forum_forum.php
4 I& s& i- m+ o. R" _
) F; t* Z' O6 h5 c8 D; j6.界面 > 编辑器设置 > Discuz!代码，删除那些不可用的标签，可解决因使用Discuz标签而导致帖子内容无法正常显示的问题。
+ G" z3 f6 Q& W/ }; a. e1 s
7.升级安装成功
( l4 Z$ z1 W+ g3 d0 W- ]
目前仍存在问题：编辑正在投放的广告位时无内容显示。
3 A1 l3 G$ ?9 i& s# \
这次还发现有一个分页标签，点下方的2可以看下一页，一直没用过这个标签，第一次用。
9 s! L+ D: {% |6 G" h# Y. ?
如果ENGINE使用InnoDB，则修改
1 c" L/ i4 x2 i9 w! LENGINE=MyISAM 全部替换为 ENGINE=InnoDB
4 o+ s/ O. I( K
并将所有有auto_increment 属性的，加为KEY。
如 id 有auto_increment属性，则在相应段加上一行 KEY  `id`  (`id`),  (如果已有完全相同这一行就不要加）

& t4 I) r+ g. L# s/ m& N
' i) N3 x: a! C4 M

+ F' g" P% q% \7 ^                               
登录/注册后可看大图

8 ?! `6 F7 T  [
4 V, l7 G* S0 B0 N
以下是这次解决问题过程，没兴趣的可不看：
: h# C( H( ~% H; U
1.常规安装，然后导出数据库，修改默认字符集

sed -i '/DEFAULT CHARSET=utf8[^m]/s/DEFAULT CHARSET=utf8/&mb4/' bbs.sql
3 |+ I! s' s# Q4 _0 Z1 Y
# u. d# m4 o. l3 N5 G1 @& Y5 p
! ?4 O) c: k, A4 R% C% u0 J2.如果直接导入bbs.sql，会出错，提示如下：
+ G9 G+ q9 O$ W- d
ERROR 1071 (42000) at line 104: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 115: Table 'bbs.pre_common_admincp_perm' doesn't exist
ERROR 1146 (42S02) at line 116: Table 'bbs.pre_common_admincp_perm' doesn't exist
) B$ j+ h5 m% Z( D* IERROR 1146 (42S02) at line 117: Table 'bbs.pre_common_admincp_perm' doesn't exist
1 I4 G& _: C  gERROR 1146 (42S02) at line 118: Table 'bbs.pre_common_admincp_perm' doesn't exist
) Q1 b' @% q9 d* Q5 f. x, g4 yERROR 1071 (42000) at line 631: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 643: Table 'bbs.pre_common_cache' doesn't exist
3 d& ?, B+ N0 n; a/ d  EERROR 1146 (42S02) at line 644: Table 'bbs.pre_common_cache' doesn't exist
* o0 E. i! {* C- p" e0 @ERROR 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
ERROR 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
0 ]$ O# R; q5 g  H9 P, r/ xERROR 1071 (42000) at line 1831: Specified key was too long; max key length is 1000 bytes
! u6 N0 P1 @$ [+ y# p5 vERROR 1146 (42S02) at line 1857: Table 'bbs.pre_common_member_profile_setting' doesn't exist
# B; o8 s8 x4 s0 \3 Z5 ]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
8 Z9 C5 O# y' b6 s, L5 LERROR 1071 (42000) at line 1870: Specified key was too long; max key length is 1000 bytes
ERROR 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
+ B( X6 x! L0 N: }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
/ v: {! O; P& e/ d- }ERROR 1146 (42S02) at line 2687: Table 'bbs.pre_common_setting' doesn't exist
" D& g+ [$ U' J$ G& i7 ZERROR 1071 (42000) at line 4800: Specified key was too long; max key length is 1000 bytes
. D8 A* ~4 \+ }1 L: Y4 oERROR 1146 (42S02) at line 4816: Table 'bbs.pre_forum_groupfield' doesn't exist
6 v3 P, v6 l% A( [# aERROR 1146 (42S02) at line 4817: Table 'bbs.pre_forum_groupfield' doesn't exist
3 h0 {/ t4 M. `7 L9 N9 hERROR 1146 (42S02) at line 4818: Table 'bbs.pre_forum_groupfield' doesn't exist
6 c1 t1 q, _6 |- JERROR 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
1 w3 B* X% J) ]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
ERROR 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
8 N# G  P9 ]5 J, nERROR 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
; c. N4 a. }) B" s7 A7 A/ DERROR 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
! R/ R, g; d) H, U( f- j2 QERROR 1146 (42S02) at line 8583: Table 'bbs.pre_ucenter_badwords' doesn't exist
ERROR 1146 (42S02) at line 8584: Table 'bbs.pre_ucenter_badwords' doesn't exist

5 M0 m8 ~6 A: J$ j& O; Z处理方法1：
) m5 n! P, C* H3 E8 K$ {4 U$ [7 Z-----------------
根据 ERROR 1071 (42000) at line 104: Specified key was too long; max key length is 1000 bytes 提示，查找 bbs.sql 文件相应行104行及后续10行：
3 a' N, m% a: a) y! B& c4 _$ S1 }9 D: @
5 _# f4 Z! X/ x6 a0 |: {. A" vsed -n '104,114p' bbs.sql

1 K- \9 @/ m8 x0 ?+ q5 T# ~
CREATE TABLE `pre_common_admincp_perm` (
: R8 Q0 M5 e/ m) L1 E6 R4 p  `cpgroupid` smallint(6) unsigned NOT NULL,
  `perm` varchar(255) NOT NULL,
  UNIQUE KEY `cpgroupperm` (`cpgroupid`,`perm`)
. |+ b6 p! ?+ }7 {) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
; h1 y: p& K9 E3 s3 B0 S/*!40101 SET character_set_client = @saved_cs_client */;
: f6 ~! c4 R& G; Q: B
--
. n* _" K4 u3 r-- Dumping data for table `pre_common_admincp_perm`
6 x0 m  E" F& s( w$ O1 V1 }--
3 T5 N3 d! @8 I6 R+ I$ @
, p$ y- ?& N" H3 [/ H可以看出 UNIQUE KEY `cpgroupperm` (`cpgroupid`,`perm`) 定义的长度 是 (6+255) * 4 > 1000 , 只要把`perm` varchar 值改小，使其总和不要超过1000即可。
现在改为 230.

! M9 I; [- u" @. q1 m- F) wsed -i '106s/255/230/' bbs.sql

# i% Y# z/ @* O  Z
其他类似提示的行做同样的修改，成功导入数据库。
: t, b5 A; G% Y6 @; [" y
处理方法2：
2 p, q  y# \- c-----------------
根据上面提示中出现的所有表，对安装文件先做处理
( Q. j" e' [* g* V
pre_ucenter_badwords 表：对应查 uc_badwords  ，从 uc_server/install/uc.sql  修改
. R! {% q) p* R: C其他表在 install/data/install.sql 修改

3 H4 b( y$ V6 |6 u* ~$ @官方原始文件 config 下的所有.php文件中的 utf8 都改为 utf8mb4
注意 utf-8 不要改动。
6 ]+ O0 X6 K9 e* m
* I! X/ G/ p, E& g: N1 Y改好以下文件
install/data/install.sql
uc_server/install/uc.sql
/ Z1 ~6 H2 D$ W- e4 I% I0 U
* p& ?0 D* I2 Z* o7 \  G修改 install/include/install_var.php

define('DBCHARSET', 'utf8');     改为    define('DBCHARSET', 'utf8mb4');
5 ]) a" p6 J  v1 `
% a* ?7 L. W* j' s' m  ]' ~再进行安装。

现在发帖，就可以正常使用表情符了,用户名都可以用表情符注册！

* T$ U9 \' n. P- M6 {) p! z