mysql command line and MySQL workbence query results in Chinese garbled solution method

Source: Internet
Author: User
Tags mysql command line

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;


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:





# The TCP/IP Port the MySQL Server would listen on

#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

The part of the specific modification is the text portion of the red bold character.

Reprinted from:

mysql command line and MySQL workbence query results in Chinese garbled solution method

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.