Home >Database >Mysql Tutorial >Mysql view encoding method topic
Mysql view encoding method topic
1.
View the character set of the database
show variables like 'character_set_%';
Output:
+---------------- ----------+--------+
| Variable_name | Value |
+-------------------- ------+--------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+----- ---+
Combined with the following encoding table, we found the encoding of the current database system:
latin1_bin
Western Europe (multi-language), binary
binary
binary
The above is my Linux environment The result encoding set viewed in . I am now checking the encoding set results on the WIN platform, such as:
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+-- --------------------------+--------+
Why are there inconsistencies? The UTF-8 displayed on my machine is actually
on LINUX. 2. Modify the encoding through the command
Create database and specify the character set of the database
mysql>create database mydb character set utf-8 ;# Directly specify its encoding
Modify directly through commands
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
After modification, query again
show variables like 'character_set_%';
+--------------------------+----- ---+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+--------------------------+--------+
All results have been adjusted and modified to UTF-8 !
After the modification, I saw that select * from address_address; was garbled! Django is also garbled
3. Solve the garbled problem of data import and export
#create database nginxdjango;
# use nginxdjango;
# show variables like 'character_set_%';
#The printout is actually as follows
+--------------------------+--------+
| Variable_name | Value |
+--- -----------------------+--------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------- ----------+--------+
is still latin encoded.
OK. I set its encoding
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
Check the code again The format is: +--------------------------+--------+
| Variable_name | Value |
+ --------------------------+--------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+--------------------------+--------+
Now set the data Import
source /python/django/sql/nginxdjango.sql;
The encoding of nginxdjango.sql is also in utf-8 format!
After importing it, the database is still garbled, but the program runs normally!
Types of MySQL character set encoding
The difference between gb2312_chinese_ci and gbk_chinese_ci and gb2312_bin, gbk_bin
gb2312_chinese_CI: only supports simplified Chinese
gb2312_BIN: and gb 2312_bin can be said to be a subset of gb2312_chinese_ci,
and gb2312_BIN It is binary storage. The case-sensitive database encoding format has different meanings
gbk_chinese_CI Supports simplified Chinese and traditional Chinese
gbk_bin The explanation is the same as gb2312_BIN Corresponding to gbk_chinese_CI
PS: GBK includes two types: simplified and traditional Chinese
New article :2010-03-09
The default character set settings in MySQL have four levels: server level, database level, and table level. The final step is the field-level character set setting. Note that the first three are the default settings and do not mean that your field will eventually use this character set setting. Therefore, we recommend using show create table table; or show full fields from tableName; to check the character set settings of the fields in the current table.
The character set settings for the connection environment in MySQL include Client, connection, results. Through these parameters, MySQL will know what character set your client tool uses and what character set the result set should be. In this way, MySQL will do the necessary translation. Once these parameters are incorrect, it will naturally lead to string conversion errors during the transfer process. Basically 99% of garbled characters are caused by these.
1. Character set settings for fields in database tables. show create table TableName or show full columns from tableName
mysql> show create table t1;
mysql> show full columns from t1; View the encoding type of the column
3. View the encoding format of the database
show create database test;
Output: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */
2. Current connection system parameters show variables like 'char%'
mysql> show variables like 'char% ';
1. Chinese, please ensure that the character set of this field in the table is Chinese compatible:
big5 | Big5 Traditional Chinese
gb2312 | GB2312 Simplified Chinese
gbk | GBK Simplified Chinese
ut f8 | UTF- 8 Unicode
[Other supplements]
Modify the character set of the database
mysql>use mydb
mysql>alter database mydb character set utf-8;
Create the characters of the specified database Set
mysql>create database mydb character set utf-8;
2010-05-02 Newly added
show variables like 'character_set_%'; Among the several items viewed, these three are affected by the client
character_set_client
character_set_connection
character_set_results
These three items can be set through set names utf8|set names gbk! It just means that the encoding of the currently connected client does not affect the encoding of the database server itself