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
1366 Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'comment' at row 1
This question is related to
mysql
For Rails, next to the accepted answer, don't forget to add:
encoding: utf8mb4
collation: utf8mb4_bin
to your database.yml
1) Database: Change Database default collation as utf8mb4
.
2) Table: Change table collation as CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
.
Query:
ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
3) Code:
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 utf8mb4
in database connection:
$database_connection = new mysqli($server, $user, $password, $database_name);
$database_connection->set_charset('utf8mb4');
The command to modify the column is:
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME TYPE;
And we need to use 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.
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 string
e.g. (Java code)
new String((byte[]) arr)
Emoji support for application having tech stack - mysql, java, springboot, hibernate
Apply below changes in mysql for unicode support.
ALTER DATABASE <database-name> CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE <table-name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DB Connection - jdbc url change:
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)
For anyone trying to solve this on a managed MySQL instance (in my case on AWS RDS), the easiest way was to modify the parameter group and set the server character set and collation to be utf8mb4
and utf8mb4_bin
, respectively. After rebooting the server, a quick query verifies the settings for system databases and any newly created ones:
SELECT * FROM information_schema.SCHEMATA S;
I have a good solution to save your time. I also meet the same problem but I could not solve this problem by the first answer.
Your defualt character is utf-8. But emoji needs utf8mb4 to support it. If you have the permission to revise the configure file of mysql, you can follow this step.
Therefore, do this following step to upgrade your character set ( from utf-8 to utf8mb4).
step 1. open your my.cnf for mysql, add these following lines to your my.cnf.
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
[mysql]
default-character-set = utf8mb4
[client]
default-character-set = utf8mb4
step2. stop your mysql service, and start mysql service
mysql.server stop
mysql.server start
Finished! Then you can check your character are changed into utf8mb4.
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/Cellar/[email protected]/5.7.29/share/mysql/charsets/ |
+--------------------------+----------------------------------------------------------+
8 rows in set (0.00 sec)
Well, you need not to change the Whole DB Charset. Instead of that you can do it by changing column to blob type.
ALTER TABLE messages MODIFY content BLOB;
My answer only adds to Selvamani P answer.
You might also need to change any SET NAMES utf8
queries with SET NAMES utf8mb4
. That did the trick for me.
Also, this is a great article to 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:
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.
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.
Read more about the queries to repair tables on the article.
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
Something like this
mysql://USERNAME:PASSWORD@HOSTNAME:PORT/DATABASE_NAME?useUnicode=yes&characterEncoding=UTF-8
If you use command line interface for inserting sql file to database.
Be sure your table charset utf8mb4
and column collation utf8mb4_unicode_ci
or utf8mb4_bin
mysql -u root -p123456 my_database < profiles.sql
ERROR 1366 (HY000) at line 1679: Incorrect string value: '\xF0\x9F\x98\x87\xF0\x9F...' for column 'note' at row 328
we can solve the problem with this parameter
--default-character-set=name
(Set the default character set)
mysql -u root -p123456 --default-character-set=utf8mb4 my_database < profiles.sql
Both the databases and tables should have character set utf8mb4
and collation utf8mb4_unicode_ci
.
When creating a new database you should use:
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
If you have an existing database and 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 collation for 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 tables with a lot of data:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Note that utf8_general_ci
is no longer recommended best practice. See the related Q & A:
What's the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.
Hi my friends This is how I solved this problem and I was happy to teach it to you as well I am in the Android application I encrypt a string containing text and emoj and send it to the server and save it in the mysql table and after receiving it from the server I decrypt it and display it in the textview. encoded and decoded my message before request and after response: I send Android app messages to mysql via pdo through this method and receive them with pdo. And I have no problem. I think it was a good way. Please like Thankful
public void main()
{
String message="hi mester ali moradi ?? how are you ?";
String encoded_message=encodeStringUrl(message);
String decode_message=decodeStringUrl(encoded_message);
}
public static String encodeStringUrl(String message) {
String encodedUrl =null;
try {
encodedUrl = URLEncoder.encode(message, "UTF-8");
} catch (UnsupportedEncodingException e) {
return encodedUrl;
}
return encodedUrl;
}
public static String decodeStringUrl(String message) {
String decodedUrl =null;
try {
decodedUrl = URLDecoder.decode(message, "UTF-8");
} catch (UnsupportedEncodingException e) {
return decodedUrl;
}
return decodedUrl;
}
_x000D_
If you are inserting using PHP, and you have followed the various ALTER database and ALTER table options above, make sure your php connection's charset is utf8mb4.
Example of connection string:
$this->pdo = new PDO("mysql:host=$ip;port=$port;dbname=$db;charset=utf8mb4", etc etc
Notice the "charset" is utf8mb4, not just utf8!
If you are using Solr + Mysql + Java, you can use:
This can be Used :
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;
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;
}
The simplest solution what works for me is to store the data as json_encode.
later when you retrieve just make sure you json_decode it.
Here you don't have to change the collation or the character set of the database and the table.
I have updated my database and table to upgraded from utf8 to 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
Source: Stackoverflow.com