設置全局 set global collation_connection = utf8mb4_general_ci 設置會話級別 1.配置文件方式,給每個新的連接配置 [mysqld] init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_general_c ...
set global collation_connection = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_general_ci '
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 '
SET NAMESindicates 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
SELECTstatement that produces a result set.)
SET NAMES '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;
charset_namealso implicitly sets
collation_connectionto 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
SET NAMES 'charset_name' COLLATE 'collation_name'
SET CHARACTER SET ''
SET CHARACTER SETis similar to
SET NAMESbut sets
collation_database(which, as mentioned previously, indicate the character set and collation of the default database).
SET CHARACTER SETstatement is equivalent to these three statements:
SET character_set_client = charset_name; SET character_set_results = charset_name; SET collation_connection = @@collation_database;
collation_connectionalso implicitly sets
character_set_connectionto the character set associated with the collation (equivalent to executing
SET character_set_connection = @@character_set_database). It is unnecessary to set
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.