Recently learning MySQL, the installation environment is a Windows Server 2003 32-bit operating system +mysql 5.1.47
mysql command line and MySQL Workbench are also installed.
This is the test database.
CREATE TABLE fortest (empno int auto_increment,empname nvarchar () not null,constraint Pk_fortest primary key (Empno)) def Ault Charset=utf8;
And then insert a piece of data
Set names character UTF8;
Insert into Fortest (empno) VALUES (' Ouyang Fiffi ');
Found not inserted, always error, specific errors are as follows:
This situation is mainly caused by a default encoding setting error.
Now look at the configuration of the MySQL server.
The specific order is
Show variables like ' character% ';
The results are as follows:
Specific solutions:
Using the MySQL command line can achieve many of the features we need, but when using the MySQL command line, there is a problem when the MySQL command line to insert Chinese data or query the Chinese data garbled, or the display is incorrect.
In the MySQL command line input: Show variables like ' character_set_% ';
View the encoding of the current configuration;
In the MySQL command line input: set names UTF8;
is equivalent to
Set Character_set_client=utf8;
Set Character_set_results=utf8;
Set Character_set_connection=utf8;
These three commands
Before inserting data on the command line
Execute command set names GBK; or set names gb2312;
Re-insert
Before querying data
Execute command set names GBK; or set names gb2312;
And then set names UTF8;
This will insert and query Chinese correctly and keep the database encoded as UTF8
For MySQL Workbench query in Chinese garbled is mainly due to the MySQL server configuration in the default setting of the encoding is not UTF8 caused, specifically under the Windows operating system, to modify the MySQL installation directory under the My.ini
(Linux is the my.cnf file under the installation directory)
The main changes are as follows:
[Client]
port=3306
[MySQL]
Default-character-set=utf8
[Mysqld]
# The TCP/IP Port the MySQL Server would listen on
port=3306
#Path to installation directory. All paths is usually resolved relative to this.
Basedir= "D:/program files/mysql/mysql Server 5.1/"
#Path to the database root
Datadir= "D:/mysql/mysql Server 5.1/data/"
# The default character set that would be used if a new schema or table is
# created and no character set is defined
Default-character-set=utf8
The part of the specific modification is the text portion of the red bold character.
Reprinted from: http://blog.sina.com.cn/s/blog_8a3a624901018j83.html
mysql command line and MySQL workbence query results in Chinese garbled solution method