Ygthkb

Isn't a semicolon (';') needed after a function declaration in C++?

Why Doesn't It Completely Uninstall?

Variance of sine and cosine of a random variable

Why write a book when there's a movie in my head?

Why do we divide Permutations to get to Combinations?

How can a kingdom keep the secret of a missing monarch from the public?

Found a major flaw in paper from home university – to which I would like to return

How can guns be countered by melee combat without raw-ability or exceptional explanations?

How bad is a Computer Science course that doesn't teach Design Patterns?

Badly designed reimbursement form. What does that say about the company?

Given the mapping a = 1, b = 2, ... z = 26, and an encoded message, count the number of ways it can be decoded

What does an unprocessed RAW file look like?

Aliased pipeline using head and cut

simplicial objects in a model category

How does the spell Slow affect freefall?

Is it ethical to apply for a job on someone's behalf?

How to play songs that contain one guitar when we have two or more guitarists?

How do I avoid the "chosen hero" feeling?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Taking an academic pseudonym?

Wy do single power points exist?

Why are `&array` and `array` pointing to the same address?

What is the name of this perspective and how is it constructed?

Is it possible to detect 100% of SQLi with a simple regex?

Mysql unable to store emoji in utf8mb4 collation

Are two indexes needed?MySQL database drop insanely slowFinding rows for a specified date rangeAdding index to large mysql tables#1093 - You can't specify target table 'R' for update in FROM clauseSimple query is slow on 4M-rows tableCannot set character_set_database and character_set_server to utf8mb4German umlaute represented by questionmarks when latin1 is usedconvert default charset utf8 tables to utf8mb4 mysql 5.7.17INSERT gives Error Code: 1366. Incorrect string value: 'xF0x9Fx98x80' for column

1

I can't store emoji characters in mysql datatbase.

I searched a lot, but again fails. I have followed this tutorial and changed these variables from Variables tab in phpmyadmin.

| Variable_name          | Value

----------------------------

character_set_client     | utf8

character_set_connection | utf8mb4

character_set_database   | utf8mb4

character_set_filesystem | binary

character_set_results    | utf8

character_set_server     | utf8mb4

character_set_system     | utf8

collation_connection     | utf8mb4_unicode_ci

collation_database       | utf8mb4_unicode_ci

collation_server         | utf8mb4_unicode_ci

NOTE: set_client and set_result are also utf8mb4 in Variables tab in phpmyadmin.

I even added these lines in my.ini file:

[client]

default-character-set = utf8mb4



[mysql]

default-character-set = utf8mb4



[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

But again no result!! I just get ???? instead of emoji. Any idea?

Edit: SHOW CREATE TABLE emoji result

 CREATE TABLE `emoji` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `emj_text` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `emj_text` (`emj_text`)

) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

mysql phpmyadmin

share|improve this question

edited Nov 1 '16 at 6:50

Vahid Najafi

asked Oct 30 '16 at 14:06

Vahid NajafiVahid Najafi

10616

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Exactly 1 Emoji turned into exactly 4 question marks? "Again" -- meaning you did a new INSERT?
  
  – Rick James
  Nov 1 '16 at 18:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I expect ???? to occur only when the column is declared latin1 (other single-byte charset).
  
  – Rick James
  Nov 1 '16 at 18:43
  

  
  
  
  

add a comment | 

1

I can't store emoji characters in mysql datatbase.

I searched a lot, but again fails. I have followed this tutorial and changed these variables from Variables tab in phpmyadmin.

| Variable_name          | Value

----------------------------

character_set_client     | utf8

character_set_connection | utf8mb4

character_set_database   | utf8mb4

character_set_filesystem | binary

character_set_results    | utf8

character_set_server     | utf8mb4

character_set_system     | utf8

collation_connection     | utf8mb4_unicode_ci

collation_database       | utf8mb4_unicode_ci

collation_server         | utf8mb4_unicode_ci

NOTE: set_client and set_result are also utf8mb4 in Variables tab in phpmyadmin.

I even added these lines in my.ini file:

[client]

default-character-set = utf8mb4



[mysql]

default-character-set = utf8mb4



[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

But again no result!! I just get ???? instead of emoji. Any idea?

Edit: SHOW CREATE TABLE emoji result

 CREATE TABLE `emoji` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `emj_text` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `emj_text` (`emj_text`)

) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

mysql phpmyadmin

share|improve this question

edited Nov 1 '16 at 6:50

Vahid Najafi

asked Oct 30 '16 at 14:06

Vahid NajafiVahid Najafi

10616

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Exactly 1 Emoji turned into exactly 4 question marks? "Again" -- meaning you did a new INSERT?
  
  – Rick James
  Nov 1 '16 at 18:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I expect ???? to occur only when the column is declared latin1 (other single-byte charset).
  
  – Rick James
  Nov 1 '16 at 18:43
  

  
  
  
  

add a comment | 

I can't store emoji characters in mysql datatbase.

I searched a lot, but again fails. I have followed this tutorial and changed these variables from Variables tab in phpmyadmin.

| Variable_name          | Value

----------------------------

character_set_client     | utf8

character_set_connection | utf8mb4

character_set_database   | utf8mb4

character_set_filesystem | binary

character_set_results    | utf8

character_set_server     | utf8mb4

character_set_system     | utf8

collation_connection     | utf8mb4_unicode_ci

collation_database       | utf8mb4_unicode_ci

collation_server         | utf8mb4_unicode_ci

NOTE: set_client and set_result are also utf8mb4 in Variables tab in phpmyadmin.

I even added these lines in my.ini file:

[client]

default-character-set = utf8mb4



[mysql]

default-character-set = utf8mb4



[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

But again no result!! I just get ???? instead of emoji. Any idea?

Edit: SHOW CREATE TABLE emoji result

 CREATE TABLE `emoji` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `emj_text` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `emj_text` (`emj_text`)

) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

mysql phpmyadmin

share|improve this question

edited Nov 1 '16 at 6:50

Vahid Najafi

asked Oct 30 '16 at 14:06

Vahid NajafiVahid Najafi

10616

| Variable_name          | Value

----------------------------

character_set_client     | utf8

character_set_connection | utf8mb4

character_set_database   | utf8mb4

character_set_filesystem | binary

character_set_results    | utf8

character_set_server     | utf8mb4

character_set_system     | utf8

collation_connection     | utf8mb4_unicode_ci

collation_database       | utf8mb4_unicode_ci

collation_server         | utf8mb4_unicode_ci

[client]

default-character-set = utf8mb4



[mysql]

default-character-set = utf8mb4



[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

 CREATE TABLE `emoji` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `emj_text` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `emj_text` (`emj_text`)

) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

share|improve this question

edited Nov 1 '16 at 6:50

Vahid Najafi

asked Oct 30 '16 at 14:06

Vahid NajafiVahid Najafi

10616

share|improve this question

edited Nov 1 '16 at 6:50

Vahid Najafi

asked Oct 30 '16 at 14:06

Vahid NajafiVahid Najafi

10616

asked Oct 30 '16 at 14:06

Vahid NajafiVahid Najafi

10616

asked Oct 30 '16 at 14:06

Vahid NajafiVahid Najafi

10616

2 Answers
2

active

oldest

votes

1

Paraphrasing https://stackoverflow.com/a/38363567/1766831 :

Question Marks (regular ones, not black diamonds) (Se?or for Señor):

• The bytes to be stored are not encoded as utf8mb4. Fix this.


• The column in the database is CHARACTER SET utf8mb4. Fix this.


• Also, check that the connection during reading is UTF-8. (That is, check phpmyadmin for utf8mb4.

Once ???? is stored, the Emoji is lost. That is, the INSERT side has the problem. The SELECT side may also have a problem.

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Oct 30 '16 at 17:54

Rick JamesRick James

42.9k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. But I didn't understand the first two items you mentioned about. The bytes to be stored are not encoded as utf8mb4 and The column in the database is CHARACTER SET utf8mb4! Would you please explain more that what I'm going to do?
  
  – Vahid Najafi
  Oct 31 '16 at 7:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The Emoji are coming from some source. Very likely they are encoded in UTF-8; let's assume that is the case. Can you provide SHOW CREATE TABLE and the code that does the connection to the database. Perhaps it is a phpmyadmin config.
  
  – Rick James
  Oct 31 '16 at 20:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  utf8mb4 (inside mysql) is the same as UTF-8 (outside mysql).
  
  – Rick James
  Oct 31 '16 at 20:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For show create table , please see edit part in my question. Thanks.
  
  – Vahid Najafi
  Nov 1 '16 at 6:51
  

  
  
  
  

add a comment | 

0

I was the same problem and I follow the tutorial you posted above and alter my laravel/SQL Configuration as follows:

        'mysql' => [

        'driver'    => 'mysql',

        'host'      => env('DB_HOST', 'localhost'),

        'database'  => env('DB_DATABASE', 'database_name'),

        'username'  => env('DB_USERNAME', 'username'),

        'password'  => env('DB_PASSWORD', ''),

        'charset'   => 'utf8mb4',              //correct charset for emojis

        'collation' => 'utf8mb4_unicode_ci',   //proper collation

        'prefix'    => '',

        'strict'    => false,

    ],

and that worked!

share|improve this answer

answered 14 mins ago

Roberto NRoberto N

1

New contributor

Roberto N is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f153720%2fmysql-unable-to-store-emoji-in-utf8mb4-collation%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1

Paraphrasing https://stackoverflow.com/a/38363567/1766831 :

Question Marks (regular ones, not black diamonds) (Se?or for Señor):

• The bytes to be stored are not encoded as utf8mb4. Fix this.


• The column in the database is CHARACTER SET utf8mb4. Fix this.


• Also, check that the connection during reading is UTF-8. (That is, check phpmyadmin for utf8mb4.

Once ???? is stored, the Emoji is lost. That is, the INSERT side has the problem. The SELECT side may also have a problem.

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Oct 30 '16 at 17:54

Rick JamesRick James

42.9k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. But I didn't understand the first two items you mentioned about. The bytes to be stored are not encoded as utf8mb4 and The column in the database is CHARACTER SET utf8mb4! Would you please explain more that what I'm going to do?
  
  – Vahid Najafi
  Oct 31 '16 at 7:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The Emoji are coming from some source. Very likely they are encoded in UTF-8; let's assume that is the case. Can you provide SHOW CREATE TABLE and the code that does the connection to the database. Perhaps it is a phpmyadmin config.
  
  – Rick James
  Oct 31 '16 at 20:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  utf8mb4 (inside mysql) is the same as UTF-8 (outside mysql).
  
  – Rick James
  Oct 31 '16 at 20:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For show create table , please see edit part in my question. Thanks.
  
  – Vahid Najafi
  Nov 1 '16 at 6:51
  

  
  
  
  

add a comment | 

1

Paraphrasing https://stackoverflow.com/a/38363567/1766831 :

Question Marks (regular ones, not black diamonds) (Se?or for Señor):

• The bytes to be stored are not encoded as utf8mb4. Fix this.


• The column in the database is CHARACTER SET utf8mb4. Fix this.


• Also, check that the connection during reading is UTF-8. (That is, check phpmyadmin for utf8mb4.

Once ???? is stored, the Emoji is lost. That is, the INSERT side has the problem. The SELECT side may also have a problem.

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Oct 30 '16 at 17:54

Rick JamesRick James

42.9k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. But I didn't understand the first two items you mentioned about. The bytes to be stored are not encoded as utf8mb4 and The column in the database is CHARACTER SET utf8mb4! Would you please explain more that what I'm going to do?
  
  – Vahid Najafi
  Oct 31 '16 at 7:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The Emoji are coming from some source. Very likely they are encoded in UTF-8; let's assume that is the case. Can you provide SHOW CREATE TABLE and the code that does the connection to the database. Perhaps it is a phpmyadmin config.
  
  – Rick James
  Oct 31 '16 at 20:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  utf8mb4 (inside mysql) is the same as UTF-8 (outside mysql).
  
  – Rick James
  Oct 31 '16 at 20:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For show create table , please see edit part in my question. Thanks.
  
  – Vahid Najafi
  Nov 1 '16 at 6:51
  

  
  
  
  

add a comment | 

Paraphrasing https://stackoverflow.com/a/38363567/1766831 :

Question Marks (regular ones, not black diamonds) (Se?or for Señor):

• The bytes to be stored are not encoded as utf8mb4. Fix this.


• The column in the database is CHARACTER SET utf8mb4. Fix this.


• Also, check that the connection during reading is UTF-8. (That is, check phpmyadmin for utf8mb4.

Once ???? is stored, the Emoji is lost. That is, the INSERT side has the problem. The SELECT side may also have a problem.

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Oct 30 '16 at 17:54

Rick JamesRick James

42.9k22259

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Oct 30 '16 at 17:54

Rick JamesRick James

42.9k22259

edited May 23 '17 at 12:40

Community♦

1

edited May 23 '17 at 12:40

Community♦

1

answered Oct 30 '16 at 17:54

Rick JamesRick James

42.9k22259

answered Oct 30 '16 at 17:54

Rick JamesRick James

42.9k22259

0

I was the same problem and I follow the tutorial you posted above and alter my laravel/SQL Configuration as follows:

        'mysql' => [

        'driver'    => 'mysql',

        'host'      => env('DB_HOST', 'localhost'),

        'database'  => env('DB_DATABASE', 'database_name'),

        'username'  => env('DB_USERNAME', 'username'),

        'password'  => env('DB_PASSWORD', ''),

        'charset'   => 'utf8mb4',              //correct charset for emojis

        'collation' => 'utf8mb4_unicode_ci',   //proper collation

        'prefix'    => '',

        'strict'    => false,

    ],

and that worked!

share|improve this answer

answered 14 mins ago

Roberto NRoberto N

1

New contributor

Roberto N is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I was the same problem and I follow the tutorial you posted above and alter my laravel/SQL Configuration as follows:

        'mysql' => [

        'driver'    => 'mysql',

        'host'      => env('DB_HOST', 'localhost'),

        'database'  => env('DB_DATABASE', 'database_name'),

        'username'  => env('DB_USERNAME', 'username'),

        'password'  => env('DB_PASSWORD', ''),

        'charset'   => 'utf8mb4',              //correct charset for emojis

        'collation' => 'utf8mb4_unicode_ci',   //proper collation

        'prefix'    => '',

        'strict'    => false,

    ],

and that worked!

share|improve this answer

answered 14 mins ago

Roberto NRoberto N

1

New contributor

Roberto N is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I was the same problem and I follow the tutorial you posted above and alter my laravel/SQL Configuration as follows:

        'mysql' => [

        'driver'    => 'mysql',

        'host'      => env('DB_HOST', 'localhost'),

        'database'  => env('DB_DATABASE', 'database_name'),

        'username'  => env('DB_USERNAME', 'username'),

        'password'  => env('DB_PASSWORD', ''),

        'charset'   => 'utf8mb4',              //correct charset for emojis

        'collation' => 'utf8mb4_unicode_ci',   //proper collation

        'prefix'    => '',

        'strict'    => false,

    ],

and that worked!

share|improve this answer

answered 14 mins ago

Roberto NRoberto N

1

New contributor

Roberto N is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

        'mysql' => [

        'driver'    => 'mysql',

        'host'      => env('DB_HOST', 'localhost'),

        'database'  => env('DB_DATABASE', 'database_name'),

        'username'  => env('DB_USERNAME', 'username'),

        'password'  => env('DB_PASSWORD', ''),

        'charset'   => 'utf8mb4',              //correct charset for emojis

        'collation' => 'utf8mb4_unicode_ci',   //proper collation

        'prefix'    => '',

        'strict'    => false,

    ],

share|improve this answer

answered 14 mins ago

Roberto NRoberto N

1

New contributor

Roberto N is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 14 mins ago

Roberto NRoberto N

1

New contributor

Roberto N is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 14 mins ago

Roberto NRoberto N

1