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

古城技术联盟

官方discuzX 3.4 默认是utf8,安装后无法发一些表情符。

$ u9 M* B  J9 G8 J1 v. C现在做测试改成 utf8mb4.
3 o6 x/ M: \3 l/ S- E' k& Z& W' z测试机环境
8 M6 X" r( l& I, X  b" Icentos 7.5
mysql 8.0.11
php 7.2.5
apache 2.4.6
8 v- m# V: G7 }1 U7 b
一、全新安装

下载补丁文件，替换加入到discuzX 3.4  2018.01.01官方原版文件中，即可完成安装，可以正常发表情符。
5 d4 F$ v6 g( ~' | patch_utf8mb4.zip (47.35 KB, 下载次数: 2, 售价: 5 个城币)

2020-6-30 20:28 上传

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

( Z) Y+ f* o7 F
. E( F& h$ E5 J! [二、换服务器升级安装（内容更新中）

  S* p( u+ T# {: J3 ]6 |1.导出旧空间上数据库的表结构文件struct.sql 和 数据文件data.sql  

1. mysqldump -d -u root -p$mysqlpass bbs > struct.sql
   / a' A- b: F8 p
2. mysqldump -t -u root -p$mysqlpass bbs --default-character-set=UTF8 > data.sql

复制代码

5 A! t! R/ O/ |
! ?3 v, [! ~; ^2.修改表结构文件 struct.sql，
: [9 n; U) L: }
) d  h% r3 O3 \: {_ci结尾的校对集大小写不敏感，即不区分大小写,ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的.
凡是在以前导入中提示 ERROR 1062 (23000): Duplicate entry 'xxxx' for key 'username' 之类的提示，往往出在选用不当的校对集引起的。
本次升级调试时使用了_ci的校对集，就遇到这个问题，象 'Avaloń' 与 'avalon' 被认为是重复，导致导入失败。
! c" E* e- I& T6 n9 |0 Y+ \
校对集 COLLATE=utf8mb4_bin 是区分大小写，可以防止旧库中有KEY属性的论坛用户名ABC、abc导入时被认为是重复的问题。
1 N- C3 I, v. M) a6 ?: R- h
修改struct.sql

. T1 Z4 b# y3 B! A- ]DEFAULT CHARSET=gbk 全部改成 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
varchar(255) 修改值
char(255) 修改值
: d7 H3 g: n0 V! V5 [
6 A4 V7 u- J: Z5 }* Mcallsed 文件内容

1. /^CREATE TABLE `pre_common_addon`/,/^) ENGINE=MyISAM/s/`key` varchar(255)/`key` varchar(250)/
   * S8 L& [! E8 P  C- D
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)/
   , W% P* H+ Z! }+ ~
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)/
   
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)/
   5 P, M- d7 m+ o* \
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)/
   8 ~3 x$ T- W* z! m8 J
10. /^CREATE TABLE `pre_home_favorite`/,/^) ENGINE=MyISAM/s/`idtype` varchar(255)/`idtype` varchar(232)/
    - Q8 L9 e8 x9 d0 _
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/

复制代码

: e& J1 ^2 c5 M5 i/ I, \
# f+ X) N7 u/ ]% l3 b6 F5 V在linux shell 下执行以下命令进行替换

1. sed -i -f callsed struct.sql

复制代码

% o6 [3 g: h) \* A9 m) H7 Q$ ~: V: c也可以在mysql 命令行下先修改原库(注意先备份数据库）
4 ?0 N5 E& l+ S0 _2 F5 d( {& d4 K& C
2 H) O# R0 v9 ~9 ]/ ]; [

1. USE bbs;
   
2. 
   # I9 V/ b8 e6 [
3. ALTER TABLE `pre_common_addon` MODIFY COLUMN `key` varchar(250);
   % Q7 `+ W) x, _1 _
4. ALTER TABLE `pre_common_admincp_perm` MODIFY COLUMN `perm` varchar(244);
   
5. ALTER TABLE `pre_common_advertisement_custom` MODIFY COLUMN `name` varchar(250);
   $ s7 G: [5 M/ k
6. ALTER TABLE `pre_common_cache` MODIFY COLUMN `cachekey` varchar(250);
   
7. ALTER TABLE `pre_common_card` MODIFY COLUMN `id` char(250);
   ) j3 w+ j6 g4 B) r7 S6 p  M$ F
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);
   
10. ALTER TABLE `pre_common_setting` MODIFY COLUMN `skey` varchar(250);
    . u/ _  u# t0 R
11. ALTER TABLE `pre_forum_groupfield` MODIFY COLUMN `type` varchar(242);
      G8 `' c7 V* C8 u  U- y
12. ALTER TABLE `pre_home_favorite` MODIFY COLUMN `idtype` varchar(232);
    
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);

复制代码

' w: b( y/ @# j% z4 n0 ?" d" J1 A& Z  g
3.在新空间的 mysql命令行下，导入struct.sql 和 数据文件data.sql  
! L, \% p( R7 O! J5 g; S
4 ^& h. T% J1 p' S

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

复制代码

& o# @% g/ S# D4.恢复旧空间的web数据库到新空间上，修改以下文件，将第一次出现连接库的'gbk' 改为 'utf8mb4'.每个文件只改一处。
0 ]" v" J! m# Z0 b* Aconfig/config_global.php
config/config_global.php
. K2 f8 @6 ~" V4 O# t/ p! q* @uc_server/data/config.inc.php
" p4 u2 I7 V2 l' x( d) g1 L
5.按官方升级要求，上传官方版文件替换，进后台 > 工具 > 更新缓存。
( E; ?. p  ]3 m' \$ O0 u4 I另外还有source/class/table/下面三个文件需要更新，具体见本帖上方发的全新安装补丁包中提取。
* _, L& D1 u' k3 m" x! u: Etable_common_usergroup.php
, ~" S* M; q7 etable_forum_announcement.php
table_forum_forum.php

) g6 R1 k  [7 U" i( j+ P6.界面 > 编辑器设置 > Discuz!代码，删除那些不可用的标签，可解决因使用Discuz标签而导致帖子内容无法正常显示的问题。
* [+ S; t! q: x1 h# D( N
, j: U5 Y8 x- o: x2 }7 B7.升级安装成功

目前仍存在问题：编辑正在投放的广告位时无内容显示。
; y7 S" e$ G3 }4 i6 Q% O
这次还发现有一个分页标签，点下方的2可以看下一页，一直没用过这个标签，第一次用。

% K0 c9 e# n7 D2 x/ s如果ENGINE使用InnoDB，则修改
" H* l- a2 i( {ENGINE=MyISAM 全部替换为 ENGINE=InnoDB
! W* R- ^8 i' e& c3 c/ j, X& B
并将所有有auto_increment 属性的，加为KEY。
$ u* ^4 u; M& [: Q8 x如 id 有auto_increment属性，则在相应段加上一行 KEY  `id`  (`id`),  (如果已有完全相同这一行就不要加）


2 ?! G& {; j6 [

$ x& d+ e7 E* G8 x" k# g6 o/ [                               
登录/注册后可看大图

# r* @/ O) S8 F; L; u/ F
6 U  e9 ~8 q8 y+ a7 w% r
以下是这次解决问题过程，没兴趣的可不看：
/ [% P5 U5 x- N- N; B/ i3 i
1.常规安装，然后导出数据库，修改默认字符集
6 a8 i! `* {. ~' A
sed -i '/DEFAULT CHARSET=utf8[^m]/s/DEFAULT CHARSET=utf8/&mb4/' bbs.sql

* ]' W) _8 Q' I
2.如果直接导入bbs.sql，会出错，提示如下：
2 [- p/ H8 i1 @  I% W1 r
. g2 u; n2 F1 `& J0 O; XERROR 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
3 O1 |+ F6 T% ~0 W: ^+ q3 mERROR 1146 (42S02) at line 117: Table 'bbs.pre_common_admincp_perm' doesn't exist
ERROR 1146 (42S02) at line 118: Table 'bbs.pre_common_admincp_perm' doesn't exist
) h4 x( h: `$ F9 s0 D! m1 e; UERROR 1071 (42000) at line 631: Specified key was too long; max key length is 1000 bytes
6 z' x0 }" @  J0 m+ t5 CERROR 1146 (42S02) at line 643: Table 'bbs.pre_common_cache' doesn't exist
" Z7 r. U4 F9 ~5 YERROR 1146 (42S02) at line 644: Table 'bbs.pre_common_cache' doesn't exist
ERROR 1146 (42S02) at line 645: Table 'bbs.pre_common_cache' doesn't exist
, _, _( ~; x1 w7 D7 wERROR 1071 (42000) at line 655: Specified key was too long; max key length is 1000 bytes
" R+ F4 ]$ |% _0 Z% _) }6 NERROR 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
ERROR 1071 (42000) at line 1831: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 1857: Table 'bbs.pre_common_member_profile_setting' doesn't exist
7 U" [' l" J" T8 D8 T+ bERROR 1146 (42S02) at line 1858: Table 'bbs.pre_common_member_profile_setting' doesn't exist
; r0 g% G( |4 C% d3 x* D3 f  q8 h' UERROR 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
ERROR 1071 (42000) at line 1870: Specified key was too long; max key length is 1000 bytes
; S4 D- s& w% C/ i# lERROR 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
: G+ X/ I. s3 i1 gERROR 1146 (42S02) at line 1890: Table 'bbs.pre_common_member_security' doesn't exist
2 C2 ~  G: D, S5 VERROR 1071 (42000) at line 2673: Specified key was too long; max key length is 1000 bytes
  c3 k. _- y% {, sERROR 1146 (42S02) at line 2684: Table 'bbs.pre_common_setting' doesn't exist
, ]9 |0 M5 a4 q  W' N% R, J; JERROR 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
ERROR 1146 (42S02) at line 2687: Table 'bbs.pre_common_setting' doesn't exist
9 c. @, |0 j+ g( b3 O6 ~" ]ERROR 1071 (42000) at line 4800: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 4816: Table 'bbs.pre_forum_groupfield' doesn't exist
: ]" e$ S8 o5 R; \ERROR 1146 (42S02) at line 4817: Table 'bbs.pre_forum_groupfield' doesn't exist
2 G+ p. U. Z& E0 J) b1 ~; Y+ QERROR 1146 (42S02) at line 4818: Table 'bbs.pre_forum_groupfield' doesn't exist
' Y, R! v2 }6 n/ XERROR 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
; V9 D5 R9 c$ I3 n8 x/ U/ [2 A( pERROR 1146 (42S02) at line 7122: Table 'bbs.pre_home_favorite' doesn't exist
- p. |9 i+ v2 `: ?5 u& cERROR 1071 (42000) at line 7797: Specified key was too long; max key length is 1000 bytes
* R; o4 N6 p* x" O9 n& u; W" IERROR 1146 (42S02) at line 7808: Table 'bbs.pre_mobile_setting' doesn't exist
  t  B8 J8 n) e# Z. PERROR 1146 (42S02) at line 7809: Table 'bbs.pre_mobile_setting' doesn't exist
( O9 I4 q. q# DERROR 1146 (42S02) at line 7810: Table 'bbs.pre_mobile_setting' doesn't exist
& }2 X* P1 R/ qERROR 1146 (42S02) at line 7811: Table 'bbs.pre_mobile_setting' doesn't exist
" k7 E4 Q7 {  r3 b  Y: _, CERROR 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
2 Y  y$ D: r2 T8 \' a" F1 H) AERROR 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：
-----------------
; r$ e# S7 q% X3 x, e3 ~( m根据 ERROR 1071 (42000) at line 104: Specified key was too long; max key length is 1000 bytes 提示，查找 bbs.sql 文件相应行104行及后续10行：
+ j8 W& C  m) `$ w6 b0 c( w# R9 I
: H) P8 G) s& y0 Q6 Jsed -n '104,114p' bbs.sql
* A4 y( @' J% A4 R. c+ }

CREATE TABLE `pre_common_admincp_perm` (
& `; ~$ k. b; Y  `cpgroupid` smallint(6) unsigned NOT NULL,
  `perm` varchar(255) NOT NULL,
  UNIQUE KEY `cpgroupperm` (`cpgroupid`,`perm`)
# r8 Y3 t/ `. ~# T/ N7 f) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
, e" _, I2 B8 b" _! D5 _  ^/*!40101 SET character_set_client = @saved_cs_client */;

/ r9 R2 |; ?, S# ^7 n--
3 V7 {2 X, R/ e-- Dumping data for table `pre_common_admincp_perm`
; F6 Q3 E. Y) Q8 V- ^/ v--

可以看出 UNIQUE KEY `cpgroupperm` (`cpgroupid`,`perm`) 定义的长度 是 (6+255) * 4 > 1000 , 只要把`perm` varchar 值改小，使其总和不要超过1000即可。
现在改为 230.

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


4 D- `( K0 Q( s5 u; }- D% Y其他类似提示的行做同样的修改，成功导入数据库。
) R& F& h# p- e
6 F" Y, [0 R) M2 E/ f+ Z处理方法2：
3 Y$ H$ d- ^4 Z, |2 Z. @' G-----------------
1 W3 S) I' A! W. K: w根据上面提示中出现的所有表，对安装文件先做处理
$ X; Y  Z! v, p$ Q' Q
pre_ucenter_badwords 表：对应查 uc_badwords  ，从 uc_server/install/uc.sql  修改
( G0 J5 t+ F5 i8 J" D其他表在 install/data/install.sql 修改
3 l8 `* \" C' k
官方原始文件 config 下的所有.php文件中的 utf8 都改为 utf8mb4
注意 utf-8 不要改动。

改好以下文件
5 l0 w9 ]6 D" d  Y7 m+ O; a6 [install/data/install.sql
7 z" s1 Q7 `' V" L1 e1 q+ A4 E- iuc_server/install/uc.sql
; b6 L  c3 l* |& z
修改 install/include/install_var.php
& C$ s; e% x; P% ~4 E7 o
) Y4 p6 c: U% ?9 z: A3 _define('DBCHARSET', 'utf8');     改为    define('DBCHARSET', 'utf8mb4');
; g/ S. ^% U$ ~7 z1 \' E) y# @
再进行安装。

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

2 z* _1 f+ M. U5 A. A