Search code examples
laravellaravel-7laravel-excelutf8mb4php-7.4

Unable to insert Chinese characters into MySQL db Despite creating table with utf8mb4/utf8mb4_general_ci

I've read a lot of posts on Stackoverflow and generally and they still do not seem to address my situation.

Running Laravel 7.x on PHP 7.4, I'm using Laravel-Excel to import into the db. Screenshot shows my database.php and table have the recommended utf8mb4 settings. Yet it complains of the column that has Chinese characters which when I replace with English, the import sails through. I am not doing a direct DB write here as Laravel-Excel does this with a call to Excel::import in my controller.

Is there some other step I can take?

SHOW CREATE TABLE ends with:

ENGINE=INNODB AUTO_INCREMENT=15735 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

SHOW VARIABLES LIKE 'char%' gives:

"Variable_name" "Value"
"character_set_client"  "utf8"
"character_set_connection"  "utf8"
"character_set_database"    "utf8mb4"
"character_set_filesystem"  "binary"
"character_set_results" "utf8"
"character_set_server"  "latin1"
"character_set_system"  "utf8"
"character_sets_dir"    "C:\\xampp\\mysql\\share\\charsets\\"

enter image description here

Solution

  • The following db/ table changes:

    ALTER DATABASE <mydb> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE <mytable> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

    and changes in database.php:

    ...
'collation' => 'utf8mb4_unicode_520_ci',
...

    Resolved the problem for me. php.ini already had the right setting.

    Thanks @RickJames