Import data from other tables, there is a Chinese garbled error. The previous operation did not encounter a similar problem, some do not have its solution.
Search on the Internet, and finally refer to this article, before executing INSERT, first execute set names GBK; Then insert successfully.
Links: http://www.cnblogs.com/pricks/archive/2010/01/10/1643471.html
Original:
The character set support for MySQL 4.1 (Character set supports) has two aspects: the character set (Character set) and the Sort method (Collation). Support for character sets is refined to four levels: server, database, data table (table), and connection (connection). The following will be divided into two parts, set up the server code and database, data table and connection part of the code, so as to prevent the emergence of Chinese garbled.
One server encoding setting
There are two ways to set up the server encoding:
One is to install MySQL, there will be a step to choose the encoding method, at this time select GBK. If not selected, the default encoding is latin1;
Second, after installing to play MySQL, manually modify its configuration file, as follows:
(1) Modify the My.ini (MySQL Server Instance Configuration file) under the MySQL installation directory. Set up
DEFAULT-CHARACTER-SET=GBK (Note that there are 2 places)
(2) Modify the db.opt configuration file in the corresponding database directory in the data directoryDEFAULT-CHARACTER-SET=GBKDefault-collation=gbk_chinese_ci Restart the database and close the console window to log back in to the database.
two encoding settings for databases, data tables, and connection sections
2.1 Setting Up Database and data table encodings To solve the garbled problem, we must first understand what code the database and data tables use.
if not specified, it will be the default latin1.
The most used should be the 3 character set Gb2312,gbk,utf8.
How do I specify the character set of a database and a data table? The following also GBK for example:
"CREATE database in MySQL Command line client"
mysql> CREATE TABLE ' Mysqlcode ' (
' ID ' TINYINT (255) UNSIGNED not NULL auto_increment PRIMARY KEY,
' Content ' VARCHAR (255) Not NULL
)
TYPE = MYISAM CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 0 rows affected, 1 warning (0.03 sec)
where the following type = MYISAM CHARACTER SET gbk COLLATE gbk_chinese_ci;
is the character set of the specified database, COLLATE (collation), which allows MySQL to support multiple encoded databases at the same time.
Of course, you can also modify the database data table's character set by using the following directives:
ALTER DATABASE Mysqlcode default character set ' GBK '.
Before the server, database and data table encoding is set, then the code in the database is GBK, Chinese can be stored in. But if you're going to do things like insert or SELECT, there's still a problem with Chinese garbled, because you haven't setThe encoding of the Connection (connection) section, and database operations such as INSERT, select, and so on, contain connection actions to the database. If you don't believe, you can now try out the following SQL text: mysql> INSERT INTO mysqlcode values (null, ' Java enthusiast ');
Press ENTER and the results are as follows: ERROR 1406 (22001): Data too long for column ' content ' at row 1
2.2 Setting the connection encoding The encoding of the server, database, and data table sections is set, and the connection encoding must be set. The connection encoding settings are as follows:mysql> SET character_set_client= ' GBK ';
mysql> SET character_set_connection= ' GBK '
mysql> SET character_set_results= ' GBK '
With the connection code set, the following can be successfully inserted in Chinese: mysql> INSERT INTO mysqlcode values (null, ' Java enthusiast ');
Query OK, 0 rows affected (0.02 sec)
In fact, the above three commands to set the connection code can be simplified to one:mysql> set names ' GBK '; When the connection encoding is set, the Chinese can also be displayed correctly when you select a query:mysql> SELECT * from Mysqlcode;
+----+-----------+
| ID | Content |
+----+-----------+
| 1 | Java Hobby |
+----+-----------+
1 row in Set (0.00 sec)
three complete examplesHere is an example of a complete demonstration of the above theory, and finally achieve the purpose of inserting Chinese into MySQL:
3.1 Setting up MySQL server encodingThis step is described in section I above, mainly after installing MySQL, modifying the encoding in My.ini and datatable.db to GBK
3.2 Creating a databaseConnect to the local database first: Mysql-h localhost-u root-proot, and then create a database: mysql>. DB test;
3.3 Create a data table and set its encoding--Chinese Code test data sheet use test
DROP TABLE IF EXISTS ' test '. ' TEST_NML ';
SET @saved_cs_client = @ @character_set_client;
SET character_set_client = GBK;
CREATE TABLE ' test '. ' Test_nml ' (
' ID ' TINYINT (255) UNSIGNED not NULL auto_increment PRIMARY KEY,
' Content ' VARCHAR (255) Not NULL
) Engine=innodb DEFAULT charset=gbk comment= ' user basic information ';
SET character_set_client = @saved_cs_client; The red part here is equivalent to the
TYPE = MYISAM CHARACTER SET gbk COLLATE gbk_chinese_ci;
3.4 inserting Chinese data--Inserting data
SET @saved_cs_client = @ @character_set_client;
Set names GBK;
INSERT into TEST_NML values (null, ' I am Chinese '); Note: Each time you perform an INSERT or update or select connection operation, you must set the encoding, which is preceded by: Set names ' GBK ';
MARIADB Database Write Chinese garbled problem
