CODEWITHSUNDEEP
phpmysqlubuntuutf-8
Alex
Alex

Reputation: 401

How can I start use utf8mb4 instead of utf8?

I tried to do https://mathiasbynens.be/notes/mysql-utf8mb4 but after my query

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

messaged this values:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | latin1             |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | latin1_swedish_ci  |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+

And when I try to save strings with like this "🛍" symbols it message to me Incorrect string value: '\xF0

I read a lot about it and try to switch my columns, table and database in which I store this data from utf8 to utf8mb4.

I set this values in /etc/my.cnf

[client]
default-character-set = utf8mb4

[mysqld]
skip-character-set-client-handshake
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'

[mysql]
default-character-set = utf8mb4

I test mysqld 

ps -ax | grep mysqld
 1409 ?        Ssl    0:32 /usr/sbin/mysqld
 9299 pts/1    S+     0:00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn mysqld

and don't sure, but mysqld must contain path to my config file /etc/my.cnf and it missing.

Thanks a lot for any help, I will not ask if not try all that I know and can google myself.

Small update:

After I select my database use booster I found that there are more variables with uf8mb4

that logs about mysql variables I show for all databases, after I did use booster (my database) I found what have more vars with utf8mb4: +--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | latin1_swedish_ci | +--------------------------+--------------------+

Upvotes: 1

Views: 895

Answers (1)

Rick James
Rick James

Reputation: 142278

You need to tell the PHP client that you are using utf8mb4, not utf8.

  • If using mysqli, do $mysqli_obj->set_charset('utf8mb4');
  • If using PDO do somethin like $db = new PDO('dblib:host=host;dbname=db;charset=utf8mb4', $user, $pwd);
  • Alternatively, execute SET NAMES utf8mb4 Any of these will say that the bytes in the client are UTF-8 encoded. Conversion, if necessary, will occur between the client and the database if the column definition is something other than utf8mb4.

Upvotes: 1

Related Questions