如何在 MySQL 数据库中存储 Emoji 字符

How to store Emoji Character in MySQL Database

提问by Selvamani P

I am using Emoji character in my project. That characters are saved (??) into mysql database. I had used database Default collation in utf8mb4_general_ci. It show

我在我的项目中使用表情符号字符。该字符被保存（??）到 mysql 数据库中。我在utf8mb4_general_ci. 它显示

1366 Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'comment' at row 1

1366 不正确的字符串值：'\xF0\x9F\x98\x83\xF0\x9F...' 列 'comment' 在第 1 行

回答by Selvamani P

1) Database: Change Database default collation as utf8mb4.

1) 数据库：将数据库默认排序规则更改为utf8mb4.

2) Table: Change table collation as CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.

2）表：将表排序规则更改为CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.

Query:

询问：

ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

3) Code:

3) 代码：

INSERT INTO tablename (column1, column2, column3, column4, column5, column6, column7)
VALUES ('273', '3', 'Hdhdhdhhzhzhzzhjzj 我爱你 ?', 49, 1, '2016-09-13 08:02:29', '2016-09-13 08:02:29')

4) Set utf8mb4in database connection:

4）utf8mb4在数据库连接中设置：

  $database_connection = new mysqli($server, $user, $password, $database_name); 
  $database_connection->set_charset('utf8mb4');

回答by ospider

step 1, change your database's default charset:

第 1 步，更改数据库的默认字符集：

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

step 2, set charset when creating table:

第二步，创建表时设置字符集：

CREATE TABLE IF NOT EXISTS table_name (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

or alter table

或改变表

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name modify name text charset utf8mb4;

回答by samawaat

Both the databases and tablesshould have character set utf8mb4and collation utf8mb4_unicode_ci.

数据库和表都应该有字符集utf8mb4和排序规则utf8mb4_unicode_ci。

When creating a new databaseyou should use:

创建新数据库时，您应该使用：

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

If you have an existing databaseand you want to add support:

如果您有一个现有的数据库并且想要添加支持：

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

You also need to set the correct character set and collationfor your tables:

您还需要为表设置正确的字符集和排序规则：

CREATE TABLE IF NOT EXISTS table_name (
    ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

or change it if you've got existing tableswith a lot of data:

或者如果您有包含大量数据的现有表，请更改它：

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note that utf8_general_ciis no longer recommended best practice. See the related Q & A:

请注意，这utf8_general_ci不再是推荐的最佳实践。见相关问答：

What's the difference between utf8_general_ci and utf8_unicode_cion Stack Overflow.

Stack Overflow 上的utf8_general_ci 和 utf8_unicode_ci 有什么区别。

回答by Deepak Arora

I have updated my database and table to upgraded from utf8to utf8mb4. But nothing works for me. Then I tried to update column datatype to blob, luckily it worked for me and data has been saved. Even my database and table both are CHARACTER SET utf8 COLLATE utf8_unicode

我已经更新了我的数据库和表以从utf8升级到utf8mb4。但没有什么对我有用。然后我尝试将列数据类型更新为blob，幸运的是它对我有用并且数据已保存。甚至我的数据库和表都是CHARACTER SET utf8 COLLATE utf8_unicode

回答by Sunil Gupta

If you are using Solr + Mysql + Java, you can use:

如果您使用的是 Solr + Mysql + Java，则可以使用：

This can be Used :

这可以使用：

• case1: When you don`t want to alter DB.
• case2: when you have to import emoticons from your Mysql to Solr core.

• 案例1：当您不想更改数据库时。
• 案例 2：当您必须将表情符号从 Mysql 导入到 Solr 核心时。

In above case this is one of the solutions to store your emoticons in your system.

在上述情况下，这是将表情符号存储在系统中的解决方案之一。

Steps to use it:

使用步骤：

Library used: import java.net.URLDecoder; import java.net.URLEncoder;

使用的库：import java.net.URLDecoder；导入 java.net.URLEncoder;

1. Use urlEncoder to encode your String having emoticons.
2. Store it in DB without altering the MysqlDB.
3. You can store it in solr core(decoded form)if you want or you can store encoded form.
4. When fetching these emoticons from DB or Solr core you can now decode it Using urlDecoder.

1. 使用 urlEncoder 对带有表情符号的字符串进行编码。
2. 将其存储在 DB 中而不更改 MysqlDB。
3. 如果需要，您可以将其存储在 solr 核心（解码形式）中，也可以存储编码形式。
4. 从 DB 或 Solr 核心获取这些表情符号时，您现在可以使用 urlDecoder 对其进行解码。

Code example:

代码示例：

import java.net.URLDecoder;
import java.net.URLEncoder;

public static void main(String[] args) {
    //SpringApplication.run(ParticipantApplication.class, args);
    System.out.println(encodeStringUrl("3?5?3?????????????     "));
    System.out.println(decodeStringUrl("Hello+emoticons%2C%2C%F0%9F%98%80%F0%9F%98%81%F0%9F%98%8A%F0%9F%98%8B%F0%9F%98%8E%F0%9F%98%8A%F0%9F%98%8D%E2%98%BA%F0%9F%98%98%E2%98%BA%F0%9F%98%91%F0%9F%98%87%F0%9F%98%98%F0%9F%98%8B%F0%9F%90%84"));
}

public static String encodeStringUrl(String url) {
    String encodedUrl =null;
    try {
         encodedUrl = URLEncoder.encode(url, "UTF-8");
    } catch (UnsupportedEncodingException e) {
        return encodedUrl;
    }
    return encodedUrl;
}

public static String decodeStringUrl(String encodedUrl) {
    String decodedUrl =null;
    try {
         decodedUrl = URLDecoder.decode(encodedUrl, "UTF-8");
    } catch (UnsupportedEncodingException e) {
        return decodedUrl;
    }
    return decodedUrl;
}

回答by Harpreet

The command to modify the column is:

修改列的命令是：

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME TYPE;

And we need to use type = BLOB

我们需要使用 type = BLOB

Example to modify is as under:-

修改示例如下：-

ALTER TABLE messages MODIFY content BLOB;

I checked that latest mySQL and other databases don't need ''to use in command on table_name, column_name etc.

我检查了最新的 mySQL 和其他数据库不需要''在 table_name、column_name 等命令中使用。

Fetch and Save data:Directly save the chat content to column and to retrieve data, fetch data as byte array (byte[])from db column and then convert it to stringe.g. (Java code)

获取和保存数据：将聊天内容直接保存到列并检索数据，(byte[])从db列中获取数据为字节数组，然后将其转换为stringeg（Java代码）

new String((byte[]) arr) 

回答by Nicola Pedretti

My answer only adds to Selvamani P answer.

我的回答只会增加 Selvamani P 的回答。

You might also need to change any SET NAMES utf8queries with SET NAMES utf8mb4. That did the trick for me.

您可能还需要使用 更改任何SET NAMES utf8查询SET NAMES utf8mb4。这对我有用。

Also, this is a great articleto port your website from utf8 to utf8mb4. In particular the article makes 2 good points on indexes and repairing tables after converting them to utf8mb4:

此外，这是一篇将您的网站从 utf8 移植到 utf8mb4的好文章。特别是这篇文章在将它们转换为 utf8mb4 后对索引和修复表提出了 2 个好处：

INDEXES

索引

When converting from utf8 to utf8mb4, the maximum length of a column or index key is unchanged in terms of bytes. Therefore, it is smaller in terms of characters, because the maximum length of a character is now four bytes instead of three. [...] The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters when using utf8mb4.

从 utf8 转换为 utf8mb4 时，列或索引键的最大长度以字节为单位不变。因此，它在字符方面更小，因为字符的最大长度现在是四个字节而不是三个字节。[...] InnoDB 存储引擎的最大索引长度为 767 字节，因此对于 utf8 或 utf8mb4 列，您最多可以分别索引 255 或 191 个字符。如果您当前有索引长度超过 191 个字符的 utf8 列，则在使用 utf8mb4 时需要索引较少数量的字符。

REPAIRING TABLES

修理桌子

After upgrading the MySQL server and making the necessary changes explained above, make sure to repair and optimize all databases and tables. I didn't do this right away after upgrading (I didn't think it was necessary, as everything seemed to work fine at first glance), and ran into some weird bugs where UPDATE statements didn't have any effect, even though no errors were thrown.

升级 MySQL 服务器并进行上述必要更改后，请确保修复和优化所有数据库和表。升级后我没有立即执行此操作（我认为没有必要，因为乍一看一切似乎都很好），并且遇到了一些奇怪的错误，其中 UPDATE 语句没有任何效果，即使没有错误被抛出。

Read more about the queries to repair tables on the article.

在文章中阅读有关修复表的查询的更多信息。

回答by Ashh

The main point hasn't been mentioned in the above answers that,

上面的回答中没有提到要点，

We need to pass query string with the options "useUnicode=yes"and "characterEncoding=UTF-8"in connection string

我们需要通过选项"useUnicode=yes"和"characterEncoding=UTF-8"连接字符串传递查询字符串

Something like this

像这样的东西

mysql://USERNAME:PASSWORD@HOSTNAME:PORT/DATABASE_NAME?useUnicode=yes&characterEncoding=UTF-8

回答by user3855339

Well, you need not to change the Whole DB Charset. Instead of that you can do it by changing column to blobtype.

好吧，您不需要更改整个数据库字符集。相反，您可以通过将列更改为blob类型来实现。

ALTER TABLE messages MODIFY content BLOB;

ALTER TABLE 消息修改内容 BLOB；

回答by user12359502

Emoji support for application having tech stack - mysql, java, springboot, hibernate

表情符号支持具有技术堆栈的应用程序 - mysql、java、springboot、hibernate

Apply below changes in mysql for unicode support.

在 mysql 中应用以下更改以获得 unicode 支持。

1. ALTER DATABASE <database-name> CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
2. ALTER TABLE <table-name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

1. ALTER DATABASE <database-name> CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
2. ALTER TABLE <table-name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

DB Connection - jdbc url change:

数据库连接 - jdbc url 更改：

jdbc:mysql://localhost:3306/<database-name>?useUnicode=yes&characterEncoding=UTF-8

jdbc:mysql://localhost:3306/<database-name>?useUnicode=yes&characterEncoding=UTF-8

Note - If the above step is not working please update mysql-connector version to 8.0.15. (mysql 5.7 works with connector version 8.0.15 for unicode support)

注意 - 如果上述步骤不起作用，请将 mysql-connector 版本更新到 8.0.15。（mysql 5.7 与连接器版本 8.0.15 配合使用以支持 unicode）