How to import Chinese Characters in MySQL [data too long for column XX at row N]

Importing and exporting data in a database is the simplest and most necessary function.

For example, when the system is under development on a personal PC, the database has a lot of initialization data. After the system development is complete, you need to export the initialization data from the PC to an SQL script, you only need to import these SQL scripts on the server to complete database initialization.

However, the import of Chinese characters has become a big problem. When importing Chinese characters, the data always reports an error"DaTA too long for column XX at row n"

This problem has always plagued me. Because the data volume was small in the past, I didn't need to import the function, so I had to re-initialize it again. I don't know how to initialize the data. It's too much data to reinitialize, but I always think there will be a way to solve this problem. Today I 've been searching for information online for more than half a day, and this problem is basically solved.

To clearly describe this problem, we will explain it in detail as follows:

1. Create a database

Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents ents and Settings \ awish> mysql-u root-P
Enter Password :*******
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 24 to server version: 5.0.27-Community-NT

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> Create Database test;

2. Set the data encoding to utf8.

Mysql> use test;
Database changed
Mysql>Set names utf8; // set the database character set to utf8
Query OK, 0 rows affected (0.00 Sec)

3. Create a data table

Mysql> Create Table person
-> (
-> ID int (8) Not null auto_increment primary key,
-> Name varchar (16) default 'nobody ',
-> Birthday char (19 ),
->)Engine = InnoDB default charset = utf8 // create a data table. Set the character set to utf8.
->;
Query OK, 0 rows affected (0.03 Sec)

4. Create an imported SQL script file c: \ test. SQL

Use test;

Insert into person values (null, 'zhang san', '2017-08-20 ');
Insert into person values (null, 'lily', '2017-08-20 ');
Insert into person values (null, 'wang 5', '2017-08-20 ');

5. Import the SQL script file

Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents ents and Settings \ awish> mysql-u root-P test <C: \ test. SQL
Enter Password :*******
Error 1406 (22001) at line 3:DaTA too long for column 'name' at Row 1

C: \ Documents ents and Settings \ awish>

Analysis: Zhang San, Li Si, and Wang Wu have only four bytes, and the table can be defined as at most 15 at the time of table creation. In theory, they are all correct. Why is too long? The only explanation is the encoding problem !!!!

If you change the preceding SQL script to insert into person values (null, 'aaaaaaaaaaaaaaaaa', '2017-08-20 '), you can insert it normally !!

Later, I found that the default MySQL encoding is gb2312.

Add the following to the test. SQL script:Set names gb2312Problem Solving

Use test;

Set names gb2312;

Insert into parent values (null, 'zhang san', '2017-08-20 ');
Insert into parent values (null, 'lily', '2017-08-20 ');
Insert into parent values (null, 'wang 5', '2017-08-20 ');

Import command:

C: \ Documents ents and Settings \ awish> mysql-u root-P test <C: \ test. SQL
Enter Password :*******

C: \ Documents ents and Settings \ awish>

Import successful !!!

6. query data

C: \ Documents ents and Settings \ awish> mysql-u root-P Test
Enter Password :*******
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 27 to server version: 5.0.27-Community-NT

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> use test;
Database changed
Mysql> select * From person;
+ ---- + -------- + ------------ +
| ID | Name | birthday |
+ ---- + -------- + ------------ +
| 1 | invalid bandwidth limit |
| 2 | invalid bandwidth limit |
| 3 | too many records |
+ ---- + -------- + ------------ +
3 rows in SET (0.00 Sec)

Mysql>

Since the Chinese character encoding is utf8, it is not recognized, we can export it to see its effect!

7. Export the database

Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents ents and Settings \ awish> mysqldump-u root-P test>C: \ test2. SQL
Enter Password :*******

C: \ Documents ents and Settings \ awish>

We save it as c: \ test2. SQL

Open the test2. SQL script file and we will see:

--
-- Table structure for table 'person'
--

Drop table if exists 'person ';
Create Table 'person '(
'Id' int (10) unsigned not null auto_increment,
'Name' varchar (16) default 'nobody ',
'Birthday' char (19) default null,
Primary Key ('id ')
) Engine = InnoDB default charset = utf8;

--
-- Dumping daTA for table 'person'
--

Lock tables 'person 'write;
/*! 40000 alter table 'others' disable keys */;
Insert into 'person 'values (1, 'zhang san', '1970-08-20 '), (2, 'Li si', '1970-08-20'), (3, 'wang 5', '2017-08-20 ');
/*! 40000 alter table 'person 'enable keys */;
Unlock tables;

The data is completely correct !!

If you want to import the test2. SQL file to the database, add:Set names gb2312

Article Source: http://gpj1016.blog.163.com/blog/static/8202645120100842119212/