MySQL5.1 Character Set settings

View multiple character sets supported by MySQL: showcharacterset; view character set verification rules: showcollation; showcollationlikegb %; each character set has a default verification rule. Bytes ------------------------------------------------------------------------------------------

View multiple character sets supported by MySQL: show character set; view character set verification rules: show collation; show collation like 'gb % '; each character set has a default verification rule. Bytes ------------------------------------------------------------------------------------------

View multiple character sets supported by MySQL:
Show character set;

View character set verification rules:
Show collation;
Show collation like 'gb % ';

Each character set has a default verification rule.

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

Set Character Set and character proofreading rules when creating a database:
Create database db_name default character set utf8 default collate utf8_general_ci;

Modify the character set and character verification rules of the database:
Alter database db_name default character set gb2312 default collate gb2312_chinese_ci;

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

When creating a table, specify the character set of the table and column and the character verification rules of the table and column:
Create table table_name (
Id int primary key,
Name varchar (100) character set gb2312 collate gb2312_chinese_ci
) Default character set gb2312 default collate gb2312_chinese_ci;

Modify the character collation of a table and the character collation rules of a table:
Alter table table_name default character set utf8 default collate utf8_general_ci;
Modify the character set of the columns in the table and the character verification rules of the columns in the table:
Alter table table_name modify name varchar (100) character set utf8 collate utf8_general_ci;

View the character set and character proofreading rules of a table:
Show create table table_name;

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

System variables of character sets and verification rules
View Character Set system variables:
Show variables like 'character _ set _ % ';
View the checking rule system variables:
Show variables like 'collation _ % ';

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

Character Set system variables:
Character_set_server: Default internal operation Character Set
Character_set_client: character set used by the client source data
Character_set_connection: Connection layer Character Set
Character_set_results: Query Result Character Set
Character_set_database: Default Character Set of the currently selected Database
Character_set_system: System metadata (field name, etc.) Character Set

Character Set conversion process:
1. MySQL Server converts the request data from character_set_client to character_set_connection when receiving the request
2. Convert the request data from character_set_connection to the internal character set before performing internal operations.

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

Modify the connection character set:
Set names utf8;
It is equivalent:
Set character_set_client = utf8;
Set character_set_results = utf8;
Set character_set_connection = utf8;

Note: The MySQL service becomes invalid after it is restarted.

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

Configure character set in window
% MySQL5.1 _ HOME %/my. ini
Modify in my. ini file

Modify the client Character Set and find:
------------------------------------------------------------------------------
[Client]
Port = 3306
[Mysql]
Default-character-set = GBK
------------------------------------------------------------------------------
To:
------------------------------------------------------------------------------
[Client]
Port = 3306
[Mysql]
Default-character-set = GB2312
------------------------------------------------------------------------------

Modify the server Character Set and find:
------------------------------------------------------------------------------
[Mysqld]
Port = 3306
Default-character-set = GBK
------------------------------------------------------------------------------
To:
------------------------------------------------------------------------------
[Mysqld]
Port = 3306
Default-character-set = GB2312
------------------------------------------------------------------------------

Restart the service
Check the character set system variable: show variables like 'character _ set _ % ';

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

Configure character set in liunx
Find the file:
/Usr/share/mysql/my-medium.cnf.
Copy the file to the/etc directory:
Cp/usr/share/mysql/my-medium.cnf/etc/my. cnf

Modify in my. cnf File

Modify the client Character Set and find:
------------------------------------------------------------------------------
[Client]
------------------------------------------------------------------------------
Add default-character-set:
------------------------------------------------------------------------------
[Client]
Default-character-set = UTF8
------------------------------------------------------------------------------

Modify the server Character Set and find:
------------------------------------------------------------------------------
[Mysqld]
------------------------------------------------------------------------------
Add default-character-set:
------------------------------------------------------------------------------
[Mysqld]
Default-character-set = UTF8
------------------------------------------------------------------------------

Restart mysql:/ect/init. d/mysql restart
Check the character set system variable: show variables like 'character _ set _ % ';

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

JDBC connection URL of MySQL:
Jdbc: mysql: /// localhost: 3306/test? User = root & password = root & useUnicode = true & characterEncoding = utf8 & autoReconnect = true & failOverReadOnly = false

When using the database connection pool, it is best to set the following two parameters:
AutoReconnect = true & failOverReadOnly = false