MYSQL - Storing unicode characters (emoji) in TEXT column

Question

When trying to insert a unicode emoji character (😎) to a MYSQL table, the insert fails due to the error;

Incorrect string value: '\\xF0\\x9F\\x98\\x8E\\xF0\\x9F...' for column 'Title' at row 1

From what I've red about this issue, it's apparently caused by the tables default character set, and possible the columns default character set, being set incorrectly. This post suggests to use utf8mb4, which I've tried, but the insert is still failing.

Here's my table configuration;

CREATE TABLE `TestTable` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `InsertDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Title` text,
  `Description` text,
  `Info` varchar(250) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `xId_TestTablePK` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2191 DEFAULT CHARSET=utf8mb4;

Note that the Title and Text columns dont have an explicitly stated character set. Initially I had no default table character set, and had these two columns were setup with DEFAULT CHARSET=utf8mb4. However, when I altered the table's default charset to the same, they were removed (presumably because the columns inherit the type from the table?)

Can anyone please help me understand how I can store these unicode values in my table?

Its worth noting that I'm on Windows, trying to perform this insert on the MYSQL Workbench. I have also tried using C# to insert into the database, specifying the character set with CHARSET=utf8mb4, however this returned the same error.

EDIT

To try and insert this data, I am executing the following;

INSERT INTO TestTable (Title) SELECT '😎😎';

Edit

Not sure if this is relevant or not, but my database is also set up with the same default character set;

CREATE DATABASE `TestDB` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

How did you fix it? By SET NAMES utf8mb4? By changing the connection parameters? — Rick James
– Rick James, Commented Jul 6, 2018 at 20:35
@RickJames, no I just fixed the broken link that was reported in a comment, which seems to have since been removed. This issue is still ongoing. — devklick
– devklick, Commented Jul 7, 2018 at 10:05

Rick James · Accepted Answer · 2018-07-07 14:23:32Z

3

The connection needs to establish that the client is talking utf8mb4, not just utf8. This involves changing the parameters used at connection time. Or executing SET NAMES utf8mb4 just after connecting.

answered Jul 7, 2018 at 14:23

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL - Storing unicode characters (emoji) in TEXT column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related