On the Internet to check some of the MySQL coding related information, here to tidy up, the wrong place to welcome you to criticize.
1. mysql character set-related variables:
-Character_set_server: Default internal operation character Set
–character_set_client: The 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: The default character set for the currently selected database
–character_set_system: System metadata (field name, etc.) character set
– There are also variables that begin with Collation_ to describe the character Fu She.
2. mysql provides different granularity of character set control:
Control particle Size |
View character Sets |
|
Character_set_connection |
Fields (field) |
Show full fields from TableName |
Tables (table) |
Show CREATE TABLE TableName |
Databases (database) |
Character_set_database or Show CREATE DATABASE DataBaseName |
MySQL servers (server) |
Character_set_server |
When creating databases, tables, and fields, the character sets on each granularity can be specified or unspecified, so that the default character set of the previous level is used.
The following is a look at the control of the character set when creating databases, tables, and fields.
3.1 Creating a Database
(1) View the current system server character set is Latin1
(2) Create a database (no encoding is specified)
You can see that the character set of the newly created database is the same as the character set of the server
(3) specifying the database character set
The newly created database character set is not the same as the server character set, but uses its own character set UTF8
(4) Modify the server's character set to UTF8
(5) Create a database again using the default character set
The new database uses the same encoding as the server
3.2 Creating a Table
(1) Current character Set settings
Database encoded as Latin1
(2) Create a table
The Name field specifies that the character set is UFT8 and the Address field is set by default
(3) Insert Chinese test
As you can see, the Name field is displayed correctly and the Address field is garbled
4. Practical recommendations
In practice, it is recommended that you specify a DB character set when creating a database, as follows:
Create DATABASE TestDB default CharSet UTF8
After creating the table, the field can not specify the character set, the default integration of the DB character set.
Of course, after the DB is created, you can also modify the encoding of the DB:
ALTER DATABASE testdb DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin
Modify the encoding of the table: ALTER table Test DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin
Modify the encoding of a field: ALTER TABLE test Change address address VARCHAR CHARACTER SET UTF8 COLLATE utf8_bin not NULL
Modified the Address field encoding, the original data still does not display properly, because when the data was inserted, the client passed in the UTF8 format, there are characters that latin1 cannot encode, the data
There was a corruption in the process character set conversion, and the query was unable to get the full data when it was inserted.
However, the newly inserted Chinese data can be displayed correctly.
Reference:
MySQL changing the encoding of a library table field
MySQL Character set settings
Summary of MySQL coding problems