Ask: third party tools import script garbled problem.

Environmental Science:
mysql5.0, The setup wizard in a character set coding is the default Latin1,
No brush script library any character set specified,
Phenomenon:
In the MySQL console into the brush script library and preset data, in the MySQL console query, display Chinese, in my web application, connection settings without the character set encoding is specified, SQL GBK ISO, the query results as ISO GBK, is a normal Chinese.
In sqlyog or mysql-front tool to import the same brush Script Library (the connection string is not set character set), after the introduction in the sqlyog check out is garbled, check out the web application, is garbled.
Try:
Upon inquiry, that the MySQL console character set environment:
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+--------+
Then the sqlyog character set environment:
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+--------+
In my sqlyog into the brush script library, forced by the SQL statement to set the tone and the MySQL console environment like, the results still.
These efforts failed, I add the set charset gbk/utf8 in the preset data insert statement brush script library before, also still.
The removal of sqlyog, mysql-front is also same, garbled.
Consult:
How does not modify the MySQL configuration, use sqlyog to import these brush script library without a garbled? (Web services to be deployed to the third party environment, the environment of the MySQL instance does not allow me to change configuration, probably is Latin1, and I need a third party tools to remote import)

Thank you for the first.

Started by Hebe at February 11, 2016 - 12:45 AM

Check the table in the source character set is what.


MySQL Chinese garbled

Posted by Stephen at February 24, 2016 - 1:26 AM

Set names Latin1 before importing;

Posted by Addison at February 25, 2016 - 1:51 AM

Thanks for two senior's help.

Speed of progress:
set names 'gbk';
set charset latin1; 

DROP DATABASE IF EXISTS user_test;
CREATE DATABASE user_test;
USE user_test;
CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,  
  `age` int,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;
insert into user value(1, 'Li Ming', 22);

The above is used for testing SQL.
*If the set names Latin1 first executes "in mysql-front;" (2 floor), and then to build tables and import data, the MySQL console query results are garbled.

*If the set names'gbk'first executes "in the new open mysql-front; set charset Latin1;" these two, then execute the tabulation and import data, the MySQL console query results will be normal.

*If the implementation of the above all scripts in the newly opened mysql-front, MySQL console query results as garbage, and then perform second times, the MySQL console query results will be normal.


The 1 floor moderator suggested I several essence, I read it again, a preliminary understanding of the database connection code, the client code, database instance returns transcoding settings, associated with the code generated. Many thanks.

Below are some questions I still felt a little difficult to explain.

The new question:
1, Why the set charset, set names these two and preset data batch execution, not garbled?
2, Set charset of character_set_client, character_set_results, But set names of character_set_client, character_set_results, character_set_connection, And I'm the first implementation of set charset 'gbk', Put character_set_client, character_set_results into GBK, Followed by second charset Latin1 character_set_client set, character_set_results, character_set_connection were replaced by Latin1, Then the direct execution of set charset Latin1, The connection, client, it returns three modifications to the code has been achieved, Why does not perform the first sentence of the set charset'gbk'case, Import the results remain garbled.?

Posted by Hebe at March 02, 2016 - 2:01 AM

The latest progress:
Used in sqlyog, Navicat.
set names 'gbk';
set charset latin1;

OK, now back to origin, import script in any third party tools, the results are garbled.

Posted by Hebe at March 09, 2016 - 3:58 AM

Your examination results posted anywhere?

Posted by Stephen at March 10, 2016 - 4:42 AM

You mean the inspection results do not know specifically?

Posted by Hebe at March 14, 2016 - 5:25 AM

Now use the sqlyog, sense of MySQL and front almost, executed in the sqlyog query window:
set names xxx;
set charset xxx;
SET character_set_client = xxx;
SET character_set_results = xxx;
SET character_set_connection = xxx;
Such statements, Use the show variables like'%char%'; see code is modified, But from the current test results in my view, I suspect that executes the statement for the MySQL front or the sqlyog itself is not used in the MySQL front or sqlyog., Whether it is insert or select, The same statement in the MySQL console in the import, Can be found in Chinese, In these two tools into the query, Or the MySQL console into a tool to access, All are garbled, Internal MySQL front or sqlyog is always a character set to interact with MySQL.

The MySQL configuration uses the default Latin1, not ut8, configuration files can not be changed, the SQL statement can be changed, the solution is built around the SQL table statement and statement of character set.

ACMAIN_CHM predecessors, I really had no idea, to you for help.

Posted by Hebe at November 30, 2016 - 9:40 PM

According to the post, post your examination results.

Posted by Stephen at December 14, 2016 - 9:46 PM

Check the export SQL file character set is what

Posted by Andra at December 21, 2016 - 10:00 PM

Hello, examination results as follows:

A MySQL console, inspection results:
1, show create table user;

--------------------------------------------------+
| Table | Create Table
                                                 |
+-------+--------------------------------------------

--------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=lati
+-------+--------------------------------------------

2, show full columns from user;

+-------+-------------+-------------------+------+-----+---------+--------------
--+---------------------------------+---------+
| Field | Type        | Collation         | Null | Key | Default | Extra
  | Privileges                      | Comment |
+-------+-------------+-------------------+------+-----+---------+--------------
--+---------------------------------+---------+
| id    | int(11)     | NULL              | NO   | PRI | NULL    | auto_incremen
t | select,insert,update,references |         |
| name  | varchar(20) | latin1_swedish_ci | NO   |     | NULL    |
  | select,insert,update,references |         |
| age   | int(11)     | NULL              | YES  |     | NULL    |
  | select,insert,update,references |         |
+-------+-------------+-------------------+------+-----+---------+--------------
--+---------------------------------+---------+

3, show variables like 'char%';

+--------------------------+----------------------------------------------------
-----+
| 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
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+


Two, sqlyog examination results:
1, show create table user;

Table Create Table

user CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

DEFAULT CHARSET=latin1
2, show full columns from user;

Field Type Collation Null Key Default Extra Privileges
Comment
id int(11) \N NO PRI \N auto_increment select,insert,update,references

name varchar(20) latin1_swedish_ci NO \N select,insert,update,references

age int(11) \N YES \N select,insert,update,references

3, show variables like 'char%';

Variable_name Value

character_set_client utf8

character_set_connection utf8

character_set_database latin1

character_set_filesystem binary

character_set_results utf8

character_set_server latin1

character_set_system utf8

character_sets_dir C:\\Program Files\\MySQL\\MySQL Server 5.1\\share\\charsets\\

Posted by Hebe at December 31, 2016 - 10:39 PM

The SQL file is written, the character set is ANSI (win7 Chinese version for GB2312).

Posted by Hebe at January 08, 2017 - 12:21 AM

Open the SQL file with WORD, such as ULTRAEDIT software, there is no hint, preferably in the unified character set
The re establishment of database, table, and then import data

Posted by Andra at January 08, 2017 - 11:19 PM

In UE, the SQL file is the utf-16, I just used the new UE SQL script files to a GBK, import replication at the GBK version of the SQL script file sqlyog, into the front using the "set names Latin1", after the introduction of still garbled.

Posted by Hebe at January 12, 2017 - 12:07 AM