MySql: charset and collation settings _ MySQL

MySql: charset and collation settings bitsCN.com

MySql: charset and collation settings

Charset and collation have multiple levels of settings: Server-level, database-level, table-level, column-level, and connection-level.

1. server-level

View Settings: show global variables like 'character _ set_server '; and show global variables like 'collation _ server ';

Modify settings: in option file (/etc/mysql/my. cnf), set:

[Mysqld]

Character_set_server = utf8

Collation_server = utf8_general_ci

2. Database-level

View Settings: select * from information_schema.schemata where schema_name = 'cookbook ';

Settings:

1. if no explicit settings are available, server-level configuration is automatically used.

2. explicit settings: specify

Create database playUtf8 default character set latin1 COLLATE latin1_swedish_ci;

3. table-level

View Settings: show create table course;

Settings:

1. if no explicit settings are available, the database-level configuration is automatically used.

2. explicit settings: specify

Create table utf (id int) default charset = utf8 default collate = utf8_bin;

4. column-level

View Settings: show create table course;

Settings:

1. if no explicit settings are available, table-level configuration is automatically used.

2. explicit settings:

Create table Table1 (column1 VARCHAR (5) character set latin1 COLLATE latin1_german1_ci );

5. connection level

View settings:

Show variables like 'character _ set_client '; # The Server uses this encoding to understand the statements sent by the client.

Show variables like 'character _ set_connection '; # I don't know what it means. let's wait until I read the mysql source code.

Show variables like 'character _ set_results '; # The Server uses this encoding to return the result set and error message.

Settings:

The client can specify these parameters during connection. at the same time, the server also provides a Global value. if the client does not specify these parameters, the server uses this Global value. How do I set this global value? I have checked many documents and it seems that I haven't seen the setting method (some people say that it is wrong to specify the command line parameter when I use my. cnf or when I start mysqld)

Appendix: what encoding does connector/j use to transmit SQL statements?

Answer: "The character encoding between client and server is automatically detected upon connection. The encoding used by the driver is specified on the server using the character_set_server system variable for server versions 4.1.0 and newer ."

That is to say, it is to query the character_set_server value of the server during the connection, and then determine the encoding used by the connection.

However, the official document also says, "to override the automatic detection encoding function on the client, you can use the" characterEncoding "attribute in the URL used to connect to the server. "

BitsCN.com