mysql 8.0设置collation_connection

设置全局

set global collation_connection = utf8mb4_general_ci 

set global default_collation_for_utf8mb4 = utf8mb4_general_ci

 

 

注：

default_collation_for_utf8mb4 在配置文件中配置会启动失败，改成启动后设置成功了！具体办法如下：

1. 启动后执行sql脚本

[mysqld]

init-file=/path/to/your/init.sql

2. 脚本中设置全局变量

init.sql 脚本写如下语句

set global default_collation_for_utf8mb4 = utf8mb4_general_ci；

3. 给脚本授权

chown mysql:mysql /path/to/your/init-file.sql

chmod 750 /path/to/your/init-file.sql

 

设置会话级别

1.配置文件方式,给每个新的连接配置

[mysqld]

init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_general_ci '

2.执行命令

set collation_connection = utf8mb4_general_ci 

或

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci

 

附录：

 

 

 

 

 

 

 

 

SQL Statements for Connection Character Set Configuration

After a connection has been established, clients can change the character set and collation system variables for the current session. These variables can be changed individually using SET statements, but two more convenient statements affect the connection-related character set sytem variables as a group:

• SET NAMES 'charset_name' [COLLATE 'collation_name']

  SET NAMES indicates what character set the client uses to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement that produces a result set.)

  A SET NAMES 'charset_name' statement is equivalent to these three statements:

  SET character_set_client = charset_name;
  SET character_set_results = charset_name;
  SET character_set_connection = charset_name;

  Setting character_set_connection to charset_name also implicitly sets collation_connection to the default collation for charset_name. It is unnecessary to set that collation explicitly. To specify a particular collation to use for collation_connection, add a COLLATE clause:

  SET NAMES 'charset_name' COLLATE 'collation_name'

• SET CHARACTER SET 'charset_name'

  SET CHARACTER SET is similar to SET NAMES but sets character_set_connection and collation_connection to character_set_database and collation_database (which, as mentioned previously, indicate the character set and collation of the default database).

  A SET CHARACTER SET charset_name statement is equivalent to these three statements:

  SET character_set_client = charset_name;
  SET character_set_results = charset_name;
  SET collation_connection = @@collation_database;

  Setting collation_connection also implicitly sets character_set_connection to the character set associated with the collation (equivalent to executing SET character_set_connection = @@character_set_database). It is unnecessary to set character_set_connection explicitly.

Note

Some character sets cannot be used as the client character set. Attempting to use them with SET NAMES or SET CHARACTER SET produces an error. See Impermissible Client Character Sets.

Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET 'latin1' before issuing the SELECT statement, the server converts the latin2 values to latin1 just before sending results back. Conversion may be lossy for characters that are not in both character sets.