OFBiz使用utf8mb4保存emoji

1.保证MySQL 版本高于 5.5.3

2.确保mysql-connector-java-版本高于5.1.1-bin.jar

3.正确配置数据库 utf8mb4
4.更改ofbiz对于mysql字段长度的定义

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

default-character-set = utf8mb4


[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking

collation-server = utf8mb4_general_ci

init-connect=?.ET NAMES utf8mb4?

character-set-server = utf8mb4

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_large_prefix = 1 


[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition
default-character-set = utf8mb4
[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

注意 innodb_large_prefix 、innodb_file_format



检查是否正确

show variables like '%character%';


show variables like 'innodb_large_prefix';


show variables like 'innodb_file_format';

创建UTF8MB4数据库、及测试数据表是否正常插入表情

create database databaseName CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
use databaseName;
create table test (value varchar(255));  
insert into asdf values ('表情');
#此处无法粘贴进来。自己找

接下来正常的OFBiz导表流程


如果出现

Specified key was too long; max key length is 767 bytes 或 java.sql.SQLException -> Index Column Size too large。

Maximum Column Size is 767 Byte






--



That error means you are trying to add unique index on a varchar(255) utf8mb4 column. In utf8mb4 column, each character needs 4 bytes. So 255 characters means 1020 bytes. The max key length is 767 bytes.





增加  fieldtypemysql_utf8mb4.xml
   
 

<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
-->

<fieldtypemodel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="http://ofbiz.apache.org/dtds/fieldtypemodel.xsd">
  <!-- ===================== field-type-def ==================== -->
    <!-- General Types -->
    <field-type-def type="blob" sql-type="LONGBLOB" java-type="java.sql.Blob"/>
    <field-type-def type="byte-array" sql-type="LONGBLOB" java-type="byte[]"/>
    <field-type-def type="object" sql-type="LONGBLOB" java-type="Object"/>

    <field-type-def type="date-time" sql-type="DATETIME(3)" java-type="java.sql.Timestamp"/>
    <field-type-def type="date" sql-type="DATE" java-type="java.sql.Date"/>
    <field-type-def type="time" sql-type="TIME(3)" java-type="java.sql.Time"/>

    <field-type-def type="currency-amount" sql-type="DECIMAL(18,2)" java-type="java.math.BigDecimal"/>
    <field-type-def type="currency-precise" sql-type="DECIMAL(18,3)" java-type="java.math.BigDecimal"/>
    <field-type-def type="fixed-point" sql-type="DECIMAL(18,6)" java-type="java.math.BigDecimal"/>
    <field-type-def type="floating-point" sql-type="DOUBLE" java-type="Double"/>
    <field-type-def type="numeric" sql-type="DECIMAL(20,0)" java-type="Long"/>

    <field-type-def type="id" sql-type="VARCHAR(20)" java-type="String"/>
    <field-type-def type="id-long" sql-type="VARCHAR(60)" java-type="String"/>
  <field-type-def type="id-vlong" sql-type="VARCHAR(191)" java-type="String"/>

    <field-type-def type="indicator" sql-type="CHAR(1)" java-type="String"/>
    <field-type-def type="very-short" sql-type="VARCHAR(10)" java-type="String"/>
    <field-type-def type="short-varchar" sql-type="VARCHAR(60)" java-type="String"/>
    <field-type-def type="long-varchar" sql-type="VARCHAR(191)" java-type="String"/>
    <field-type-def type="very-long" sql-type="LONGTEXT" java-type="String"/>

    <field-type-def type="comment" sql-type="VARCHAR(191)" java-type="String"/>
    <field-type-def type="description" sql-type="VARCHAR(191)" java-type="String"/>
    <field-type-def type="name" sql-type="VARCHAR(191)" java-type="String"/>
    <field-type-def type="value" sql-type="VARCHAR(191)" java-type="String"/>

    <!-- Specialized Types -->
    <field-type-def type="credit-card-number" sql-type="VARCHAR(191)" java-type="String"/>
    <field-type-def type="credit-card-date" sql-type="VARCHAR(7)" java-type="String"/>
    <field-type-def type="email" sql-type="VARCHAR(191)" java-type="String"/>
    <field-type-def type="url" sql-type="LONGTEXT" java-type="String"/>
  <field-type-def type="id-ne" sql-type="VARCHAR(20)" java-type="String"/>
  <field-type-def type="id-long-ne" sql-type="VARCHAR(60)" java-type="String"/>
  <field-type-def type="id-vlong-ne" sql-type="VARCHAR(191)" java-type="String"/>
    <field-type-def type="tel-number" sql-type="VARCHAR(60)" java-type="String"/>
</fieldtypemodel>

修改 entityengine.xml
   

<field-type name="mysql" loader="fieldfile" location="fieldtypemysql_mb4.xml"/>

使配置适用 Utf8mb4

评论

1 楼 沈寅麟 2019-03-12  

如果:[MySQL] specified key was too long max key length is 767bytes。
----------------------------------------------------------------------
是因为:

当字符集为 utf8mb4 时，一个字符对应 4 bytes;
当字符集为 utf8 时，一个字符对应 3 bytes;
所以对于（一）中的问题：

字符集为 utf8mb4, 所以可申请索引的 varchar 长度为：767/4 ≈ 191；

而字符集为 utf8 时，可设置的创建索引 varchar 长度为: 767/3 ≈ 255
----------------------------------------------------------------------