Change MySQL default character set to UTF-8 in my.cnf?

For now, we use the following command in PHP to set the character set to UTF-8 .

Because of this overhead, we want to set it as the default in MySQL. Can we do this in / etc/my.cnf or elsewhere?

SET NAMES 'utf8'
SET CHARACTER SET utf8

I found a default character set in / etc/my.cnf, but nothing about it.

At this point, I do the following to set the MySQL character set and proofing variable to UTF-8:

skip-character-set-client-handshake
character_set_client=utf8
character_set_server=utf8

Is this the right way to deal with this problem?

#1 building

Under Xubuntu 12.04, I simply added

[mysqld]
character_set_server = utf8

To / etc/mysql/my.cnf

The result is

mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Please check it again. http://dev.mysql.com/doc/refman/5.6/en/charset-server.html

#2 building

For the latest version of MySQL,

default-character-set = utf8

Causes problems. I think it has been abandoned.

Just as Justin Ball In " Upgrade to MySQL 5.5.12 and now MySQL cannot start ”You should:

  1. Delete the command, you should be OK.

  2. Then your configuration file (for example '/ etc/my.cnf') should look like this:

    [mysqld] collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8
  3. Restart MySQL.

  4. To ensure that your MySQL is UTF-8, run the following query at the MySQL prompt:

    • First query:

       mysql> show variables like 'char%';

      The output shall be as follows:

       +--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/| +--------------------------+---------------------------------+
    • Second query:

       mysql> show variables like 'collation%';

      The query output is:

       +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +----------------------+-----------------+

#3 building

All of the settings listed here are correct, but here is the best and sufficient solution:

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
character-set-server = utf8
collation-server = utf8_unicode_ci

[client]
default-character-set = utf8

Add these to / etc/mysql/my.cnf.

Note that due to performance issues, I chose a collation of type utf8? Unicode? CI.

The result is:

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

This is when you connect as a non SUPER user!

For example, the difference between connections as SUPER and non SUPER users (in the case of utf8 ﹣ Unicode ﹣ CI grooming, of course):

Super private:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci | <---
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

Non SUPER private users:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

I wrote. A comprehensive article (rus), which explains in detail why you should use one or another option. Consider all types of character sets and collations: for servers, for databases, for connections, for tables, and even for columns.

I hope this article and article will help to clarify the ambiguous moment.

#4 building

Change MySQL role:

Customer

default-character-set=utf8

mysqld

character_set_server=utf8

We should not write default character set = utf8 in mysqld, because this may cause the following errors:

Start: job failed to start

Last:

 +--------------------------+----------------------------+
 | Variable_name            | Value                      |
 +--------------------------+----------------------------+
 | character_set_client     | utf8                       |
 | character_set_connection | utf8                       |
 | character_set_database   | utf8                       |
 | character_set_filesystem | binary                     |
 | character_set_results    | utf8                       |
 | character_set_server     | utf8                       |
 | character_set_system     | utf8                       |
 | character_sets_dir       | /usr/share/mysql/charsets/ |
 +--------------------------+----------------------------+

#5 building

MySQL 5.5, you only need to:

[mysqld]
character_set_client=utf8
character_set_server=utf8
collation_server=utf8_unicode_ci

The collation ABCD server is optional.

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Tags: MySQL PHP

Posted on Thu, 12 Mar 2020 03:52:55 -0700 by snaack