Mysql unable to store emoji in utf8mb4 collation












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









share|improve this question

























  • 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
















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









share|improve this question

























  • 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














1












1








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









share|improve this question
















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















share|improve this question













share|improve this question




share|improve this question








edited Nov 1 '16 at 6:50







Vahid Najafi

















asked Oct 30 '16 at 14:06









Vahid NajafiVahid Najafi

10616




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



















  • 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

















Exactly 1 Emoji turned into exactly 4 question marks? "Again" -- meaning you did a new INSERT?

– Rick James
Nov 1 '16 at 18:37





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





I expect ???? to occur only when the column is declared latin1 (other single-byte charset).

– Rick James
Nov 1 '16 at 18:43










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


























  • 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



















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








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.




















    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


















    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















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    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


























    • 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
















    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


























    • 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














    1












    1








    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















    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














    share|improve this answer



    share|improve this answer








    edited May 23 '17 at 12:40









    Community

    1




    1










    answered Oct 30 '16 at 17:54









    Rick JamesRick James

    42.9k22259




    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



















    • 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

















    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





    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





    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





    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





    For show create table , please see edit part in my question. Thanks.

    – Vahid Najafi
    Nov 1 '16 at 6:51













    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








    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.

























      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








      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.























        0












        0








        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








        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.










        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








        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.









        share|improve this answer



        share|improve this answer






        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 27 mins ago









        Roberto NRoberto N

        1




        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.





        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.






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






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            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















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh