php - ERROR 1366 (HY000): Incorrect string value: 'xF0x9Fx98x9C' for column 'comment' at row 1

Question

Here's my sql:

INSERT INTO comments (createdate,userid,profileid,comment,status) 
VALUES (1449503167,65704,65704,'@Mr_S66 Wish I was There For The Xmas Party I Miss My Studio 66 Family e???',15)

Here's my comments schema:

    +------------+---------------+------+-----+---------+----------------+
    | Field      | Type          | Null | Key | Default | Extra          |
    +------------+---------------+------+-----+---------+----------------+
    | commentid  | int(11)       | NO   | PRI | NULL    | auto_increment |
    | parentid   | int(11)       | YES  |     | 0       |                |
    | refno      | int(11)       | YES  |     | 0       |                |
    | createdate | int(11)       | YES  |     | 0       |                |
    | remoteip   | varchar(80)   | YES  |     |         |                |
    | locid      | int(11)       | YES  | MUL | 0       |                |
    | clubid     | int(11)       | YES  |     | 0       |                |
    | profileid  | int(11)       | YES  | MUL | 0       |                |
    | userid     | int(11)       | YES  | MUL | 0       |                |
    | legacyuser | int(11)       | YES  | MUL | 0       |                |
    | mediaid    | int(11)       | YES  |     | 0       |                |
    | status     | int(11)       | YES  |     | 1       |                |
    | comment    | varchar(4000) | YES  |     |         |                |
    | likes      | int(11)       | YES  |     | 0       |                |
    | dislikes   | int(11)       | YES  |     | 0       |                |
    | import     | int(11)       | YES  |     | 0       |                |
    | author     | varchar(50)   | YES  |     |         |                |
    +------------+---------------+------+-----+---------+----------------+

Heres my output of the sql query:

ERROR 1366 (HY000): Incorrect string value: 'xF0x9Fx98x9C' for column 'comment' at row 1

Not quite sure how to solve this yet. Possibly filter the comment text using php to accommodate the string value.

Answer 1

Something in your environment is not set up to correctly process Unicode text.

The byte sequence F0 9F 98 9C, represented incorrectly as "e???" in your query, is the UTF8 encoding of the Unicode character "??", FACE WITH STUCK-OUT TONGUE AND WINKING EYE. (That is, it's an emoji character.)

To store this character correctly, you will need to make sure that:

• You are enabling UTF8 on your MySQL connection (i.e, SET NAMES utf8mb4, or use an option when connecting that similarly enables it).
• You are running MySQL 5.5 or later.
• Your table's character set is utf8mb4.