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

古城技术联盟

官方discuzX 3.4 默认是utf8,安装后无法发一些表情符。
3 c" u3 l0 k# C9 v3 m! U9 e
现在做测试改成 utf8mb4.
测试机环境
1 k% \" b( l. _# }+ W( Kcentos 7.5
9 b( ]0 {* V" u  rmysql 8.0.11
+ ]# }" p, |4 a/ nphp 7.2.5
apache 2.4.6

一、全新安装

+ q7 |* l2 K7 X! k  L2 V7 R: V下载补丁文件，替换加入到discuzX 3.4  2018.01.01官方原版文件中，即可完成安装，可以正常发表情符。
patch_utf8mb4.zip (47.35 KB, 下载次数: 2, 售价: 5 个城币)

2020-6-30 20:28 上传

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

3 c2 o" d8 `3 e+ L+ v' S2 A二、换服务器升级安装（内容更新中）

1.导出旧空间上数据库的表结构文件struct.sql 和 数据文件data.sql  

1. mysqldump -d -u root -p$mysqlpass bbs > struct.sql
   * z% o6 L' q5 V0 O2 K4 v2 f* r
2. mysqldump -t -u root -p$mysqlpass bbs --default-character-set=UTF8 > data.sql

复制代码

$ \4 |' }2 O% q
( X* c* q( Q; C5 P6 r2.修改表结构文件 struct.sql，

_ci结尾的校对集大小写不敏感，即不区分大小写,ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的.
) }! ^4 m% _8 O5 S5 [凡是在以前导入中提示 ERROR 1062 (23000): Duplicate entry 'xxxx' for key 'username' 之类的提示，往往出在选用不当的校对集引起的。
$ A% B% @4 w6 ~9 X5 T本次升级调试时使用了_ci的校对集，就遇到这个问题，象 'Avaloń' 与 'avalon' 被认为是重复，导致导入失败。

, G& ~7 z+ a! V  f' a% V校对集 COLLATE=utf8mb4_bin 是区分大小写，可以防止旧库中有KEY属性的论坛用户名ABC、abc导入时被认为是重复的问题。
/ K9 ^, }5 ^3 i8 S
! R; x+ C$ r( i# ~: }4 \修改struct.sql

; C1 J- l/ X4 l) fDEFAULT CHARSET=gbk 全部改成 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
varchar(255) 修改值
char(255) 修改值

callsed 文件内容

1. /^CREATE TABLE `pre_common_addon`/,/^) ENGINE=MyISAM/s/`key` varchar(255)/`key` varchar(250)/
   
2. /^CREATE TABLE `pre_common_admincp_perm`/,/^) ENGINE=MyISAM/s/`perm` varchar(255)/`perm` varchar(244)/
   & s! Q  W, }" a0 R. j8 N
3. /^CREATE TABLE `pre_common_advertisement_custom`/,/^) ENGINE=MyISAM/s/`name` varchar(255)/`name` varchar(250)/
   7 `3 R3 ~  \' r; M3 A; C
4. /^CREATE TABLE `pre_common_cache`/,/^) ENGINE=MyISAM/s/`cachekey` varchar(255)/`cachekey` varchar(250)/
   ) H3 p$ }2 U% @; G2 ]
5. /^CREATE TABLE `pre_common_card`/,/^) ENGINE=MyISAM/s/`id` char(255)/`id` char(250)/
   ; B; ~! ?6 A, b1 F
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)/
   6 ^! ~2 j# t# T$ }
8. /^CREATE TABLE `pre_common_setting`/,/^) ENGINE=MyISAM/s/`skey` varchar(255)/`skey` varchar(250)/
   
9. /^CREATE TABLE `pre_forum_groupfield`/,/^) ENGINE=MyISAM/s/`type` varchar(255)/`type` varchar(242)/
   
10. /^CREATE TABLE `pre_home_favorite`/,/^) ENGINE=MyISAM/s/`idtype` varchar(255)/`idtype` varchar(232)/
    7 G; J! O- X9 X* h8 H7 m, S
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)/
    
13. /^CREATE TABLE `cdb_uc_badwords`/,/^) ENGINE=MyISAM/s/`find` varchar(255)/`find` varchar(250)/
    
14. s/DEFAULT CHARSET=gbk/DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/

复制代码

) Y; a( @. v1 X! n- J$ p& }在linux shell 下执行以下命令进行替换

! ]5 W( `3 Z) r7 a

1. sed -i -f callsed struct.sql

复制代码

% B, @9 J* g+ Y, f, R也可以在mysql 命令行下先修改原库(注意先备份数据库）
' @1 C# S2 F: P# d* ]: h
4 @3 T+ F; d* ^1 f

1. USE bbs;
   3 N6 I" j+ O$ Q. k" Z1 L9 x
2. 
   
3. ALTER TABLE `pre_common_addon` MODIFY COLUMN `key` varchar(250);
   1 I7 q3 X0 y$ l8 f6 \/ ^0 n
4. ALTER TABLE `pre_common_admincp_perm` MODIFY COLUMN `perm` varchar(244);
   ' g. ^& O: ]% n  t7 s
5. ALTER TABLE `pre_common_advertisement_custom` MODIFY COLUMN `name` varchar(250);
   # E- g* S, P+ U
6. ALTER TABLE `pre_common_cache` MODIFY COLUMN `cachekey` varchar(250);
   , }# ^+ R  Y  h  n6 m
7. ALTER TABLE `pre_common_card` MODIFY COLUMN `id` char(250);
   
8. ALTER TABLE `pre_common_member_profile_setting` MODIFY COLUMN `fieldid` varchar(250);
   - k5 a* C$ U+ Q6 D! j0 J
9. ALTER TABLE `pre_common_member_security` MODIFY COLUMN `fieldid` varchar(242);
   ( m1 L; w. D9 @" `: E- Z% N
10. ALTER TABLE `pre_common_setting` MODIFY COLUMN `skey` varchar(250);
    
11. ALTER TABLE `pre_forum_groupfield` MODIFY COLUMN `type` varchar(242);
    
12. ALTER TABLE `pre_home_favorite` MODIFY COLUMN `idtype` varchar(232);
    " F/ ]) k# `' b$ s: y; Q
13. ALTER TABLE `pre_mobile_setting` MODIFY COLUMN `skey` varchar(250);
    0 }0 D, a) H) x/ a! h( r4 ~& ]
14. ALTER TABLE `pre_portal_topic` MODIFY COLUMN `name` varchar(250);
    7 s; b2 T$ W! x) N/ |0 P
15. ALTER TABLE `cdb_uc_badwords` MODIFY COLUMN `find` varchar(250);

复制代码

4 n0 H. Y5 ]; U4 d% q2 Z3.在新空间的 mysql命令行下，导入struct.sql 和 数据文件data.sql  
- A0 t0 [( U% d* T
. B3 }8 n; G8 \

1. USE bbs;
   
2. SOURCE struct.sql;
   
3. SOURCE data.sql

复制代码

7 I- D4 s* a# C/ D; b  A4.恢复旧空间的web数据库到新空间上，修改以下文件，将第一次出现连接库的'gbk' 改为 'utf8mb4'.每个文件只改一处。
8 X4 H4 V0 ], m. ]2 n! w! g) Fconfig/config_global.php
$ r9 {: J$ s, H2 Oconfig/config_global.php
$ m$ {& v. w' V9 \uc_server/data/config.inc.php

5.按官方升级要求，上传官方版文件替换，进后台 > 工具 > 更新缓存。
另外还有source/class/table/下面三个文件需要更新，具体见本帖上方发的全新安装补丁包中提取。
, X9 b7 N* J& K& [3 xtable_common_usergroup.php
table_forum_announcement.php
% t# ]6 `( D3 e' }) y. Ktable_forum_forum.php
5 J# Z- s, ^4 Y" V" I
6.界面 > 编辑器设置 > Discuz!代码，删除那些不可用的标签，可解决因使用Discuz标签而导致帖子内容无法正常显示的问题。

8 L) ^4 B$ G- z/ i2 C4 W4 s5 G7.升级安装成功

目前仍存在问题：编辑正在投放的广告位时无内容显示。
% q& g/ `5 s& D
. l. D- P0 w% e' Z这次还发现有一个分页标签，点下方的2可以看下一页，一直没用过这个标签，第一次用。
% y! t) V  x. `# m# y
. Z& e! ^, z, h; Y如果ENGINE使用InnoDB，则修改
ENGINE=MyISAM 全部替换为 ENGINE=InnoDB

并将所有有auto_increment 属性的，加为KEY。
  |, w" j; ?* |# v* f如 id 有auto_increment属性，则在相应段加上一行 KEY  `id`  (`id`),  (如果已有完全相同这一行就不要加）

- R. D" M( U& Z% l) M

                               
登录/注册后可看大图

* s! \; o7 F  e7 k: \- J

以下是这次解决问题过程，没兴趣的可不看：
8 R0 g/ @' |+ ?* G8 ]$ C( L' X  O
1.常规安装，然后导出数据库，修改默认字符集
# Q3 y. f) `- T& [" m( h
' Y& c! a, F5 d- S& ?% b+ j$ c% x3 |sed -i '/DEFAULT CHARSET=utf8[^m]/s/DEFAULT CHARSET=utf8/&mb4/' bbs.sql
  K/ T8 L% f! s8 w
' C6 z  [8 H+ _$ ?
2.如果直接导入bbs.sql，会出错，提示如下：
2 i5 M# b; S8 r' |4 ]
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
' g! ^2 U/ F2 T% z, |/ aERROR 1146 (42S02) at line 116: Table 'bbs.pre_common_admincp_perm' doesn't exist
# @4 P+ n$ |4 F: N5 a/ oERROR 1146 (42S02) at line 117: Table 'bbs.pre_common_admincp_perm' doesn't exist
$ L/ b" u6 u/ s1 x# hERROR 1146 (42S02) at line 118: Table 'bbs.pre_common_admincp_perm' doesn't exist
ERROR 1071 (42000) at line 631: Specified key was too long; max key length is 1000 bytes
8 {6 t  r0 `5 l( T! TERROR 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
/ L3 R6 [0 z/ s- f8 C  [6 v0 JERROR 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
2 b0 t- e8 H6 b3 M3 z2 \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
* |$ r% k9 U7 G- KERROR 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
) p, X* P+ r3 U6 ^& oERROR 1146 (42S02) at line 1859: Table 'bbs.pre_common_member_profile_setting' doesn't exist
; e* l& k6 [/ SERROR 1146 (42S02) at line 1860: Table 'bbs.pre_common_member_profile_setting' doesn't exist
( q  ^5 ]9 j+ r* E' J, DERROR 1071 (42000) at line 1870: Specified key was too long; max key length is 1000 bytes
( y( I' g+ p1 L: A/ T, 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
1 J) j, v. A; b6 s9 {, w4 t3 ]& I, {ERROR 1146 (42S02) at line 1890: Table 'bbs.pre_common_member_security' doesn't exist
# S* [  N6 s# S3 hERROR 1071 (42000) at line 2673: Specified key was too long; max key length is 1000 bytes
- I: e: G0 C9 y$ h8 ]ERROR 1146 (42S02) at line 2684: Table 'bbs.pre_common_setting' doesn't exist
2 \4 E5 w4 z; U: B) a% `ERROR 1146 (42S02) at line 2685: Table 'bbs.pre_common_setting' doesn't exist
- O4 ]; D1 @- O6 UERROR 1146 (42S02) at line 2686: Table 'bbs.pre_common_setting' doesn't exist
5 x: _# z1 ~! X$ n8 GERROR 1146 (42S02) at line 2687: Table 'bbs.pre_common_setting' doesn't exist
: C" e0 ?" L- ?2 ?  H# N- AERROR 1071 (42000) at line 4800: Specified key was too long; max key length is 1000 bytes
# e: N+ ^# p. ]5 \+ m# e& T1 @9 jERROR 1146 (42S02) at line 4816: Table 'bbs.pre_forum_groupfield' doesn't exist
2 G0 W, M0 D" F. h$ g: lERROR 1146 (42S02) at line 4817: Table 'bbs.pre_forum_groupfield' doesn't exist
! j2 u# \9 K7 s, ~- T" fERROR 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
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
* F5 Y, n* Z- u5 j% d. H6 fERROR 1071 (42000) at line 7797: Specified key was too long; max key length is 1000 bytes
3 @( y) @$ I8 z" PERROR 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
ERROR 1071 (42000) at line 8567: Specified key was too long; max key length is 1000 bytes
- c7 v! n7 {; v0 ]& gERROR 1146 (42S02) at line 8582: Table 'bbs.pre_ucenter_badwords' doesn't exist
ERROR 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

处理方法1：
# O- U- Y: K+ ]( \2 [9 \7 R-----------------
根据 ERROR 1071 (42000) at line 104: Specified key was too long; max key length is 1000 bytes 提示，查找 bbs.sql 文件相应行104行及后续10行：
1 S1 n& o' O4 A2 t3 T- S
+ g& c+ E' X* ?' F  y3 Jsed -n '104,114p' bbs.sql
! ^: |, l) p8 {6 r# S9 i8 q% S
2 J/ n9 f/ T/ ?( L7 m# i9 c5 t
, f9 W" l: n) P- k) ECREATE TABLE `pre_common_admincp_perm` (
$ V' n9 n% ?- I: ?  `cpgroupid` smallint(6) unsigned NOT NULL,
" d' l  V8 Z; d0 x6 J  `perm` varchar(255) NOT NULL,
  UNIQUE KEY `cpgroupperm` (`cpgroupid`,`perm`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
! e) T( H$ Q1 F- r! r; r
--
8 T: I2 R9 F$ l9 E3 g0 W0 N& A( V-- Dumping data for table `pre_common_admincp_perm`
& S, x, w2 _1 H) A/ }8 q--

! Q. ], b9 w. L% x8 S( Q" k可以看出 UNIQUE KEY `cpgroupperm` (`cpgroupid`,`perm`) 定义的长度 是 (6+255) * 4 > 1000 , 只要把`perm` varchar 值改小，使其总和不要超过1000即可。
* R# N5 f  `- C! _现在改为 230.

sed -i '106s/255/230/' bbs.sql

& ]$ K# Z- a7 s, s( j2 I* p
其他类似提示的行做同样的修改，成功导入数据库。
3 J$ N1 G/ y! a9 u
( g' {  ]4 H; F处理方法2：
-----------------
根据上面提示中出现的所有表，对安装文件先做处理
% N/ R" q- y- {+ O6 B  u
pre_ucenter_badwords 表：对应查 uc_badwords  ，从 uc_server/install/uc.sql  修改
其他表在 install/data/install.sql 修改
) O1 ?# I9 Z6 G( F, d/ c
' ?% e+ {- \3 e官方原始文件 config 下的所有.php文件中的 utf8 都改为 utf8mb4
注意 utf-8 不要改动。

! k4 F! n1 L4 [) @! H2 z: W8 t改好以下文件
install/data/install.sql
uc_server/install/uc.sql
# X2 v/ h. k$ x+ D0 U
# O# _2 ]/ H# I! G& h2 K' l8 W修改 install/include/install_var.php

define('DBCHARSET', 'utf8');     改为    define('DBCHARSET', 'utf8mb4');

9 r/ ^. O/ I* U! g7 H- `再进行安装。

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