How can I insert smiley faces into MySQL ( )

I'm on MySQL 5.5.21, and trying to insert the '\xF0\x9F\x98\x8A' smiley face character. But for the life of me, I can't figure out how to do it.

According to various forums which I've been reading, it is possible. But whenever I try it, the data just gets truncated.

mysql> INSERT INTO hour  (  `title`,   `content`,   `guid` ,  `published` , `lang` ,  `type` ,  
       `indegree` ,  `lon` ,  `lat` ,  `state` ,  `country` , `hour`  )   
       VALUES ( "title" ,  "content   content" ,  "guid" ,  1,  1,   
                     "WEBLOG",  1,  1,  1,  "state" ,  "country" ,  1 );
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8A  ...' for column 'content' at row 1 |
| Warning | 1265 | Data truncated for column 'published' at row 1                                |
+---------+------+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|           687302 |
+------------------+
1 row in set (0.00 sec)

mysql> select * from hour where id = 687302;
+--------+-------+----------+------+---------------------+
| id     | title | content  | guid | published           |
+--------+-------+----------+------+---------------------+
| 687302 | title | content  | guid | 0000-00-00 00:00:00 |
+--------+-------+----------+------+---------------------+
1 row in set (0.00 sec)

But my table definition is as follows.

``
CREATE TABLE hour (
id bigint(20) NOT NULL AUTO_INCREMENT,
title varchar(255) CHARACTER SET utf8 NOT NULL,
content` text CHARACTER S

I’ve recently written a detailed guide on how to switch from MySQL’s utf8 to utf8mb4. If you follow the steps there, everything should work correctly. Here are direct links to each individual step in the process:

• Step 1: Create a backup

• Step 2: Upgrade the MySQL server

• Step 3: Modify databases, tables, and columns

• Step 4: Check the maximum length of columns and index keys

• Step 5: Modify connection, client, and server character sets

• Step 6: Repair and optimize all tables

I suspect that your problem can be solved by following step 5. Hope this helps!

StackExchange Database Administrators Q#20690, answer score: 29