Summary of MySQL coding problems

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
Connection (Connection)	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