MySQL error: data too long for Column

Run in Windows DOS window todayMySQLCommand. If the insert statement contains Chinese characters, the "data too long for column" error occurs.

Later I found a post (http://www.blogjava.net/sakis/archive/2006/07/14/58242.html) online, the content is as follows:

"Data too long for column" error in mysql5

Run a local script from mysql5 to create a database. The "data too long for column" error occurs when a Chinese field is inserted. I checked the Internet and found that most of the multibyte users encountered this situation. Most of the Solutions for Google search are to encode the database using GBK or utf8, but I chose utf8 when installing MySQL. The original error occurs because the local script file is not UTF-8 encoded. use notepad or ultraedit to convert the encoding to utf8. Again,JSPPage, database connection method, database creation ,..., All must be UTF-8 encoded!

BTW, MySQL has recently gained popularity through the wave of Web. techn orati (as if GFW had been filtered over the past few days), and MySQL was used by a number of websites such as Flickr and Del. icio. us. MySQL also opened a large block on the home page to show off.

--------------------------------------------

As he said, it is right to import the SQL file through the source command or pipeline. But running the insert statement in the console/DOS window with Chinese characters still causes errors. Why? After some tests, we have summarized the following experiences:
The commands sent by calling programs in the console are encoded strings, and the received information returned by the program also uses the encoded response stream.

For example, we want to call:

Rename 1.txt test .txt

In dos, this line of command is encoded as a string in some way. If the renmae command receives this command string, but does not understand the encoding of this command parameter, or the console uses utf8 encoding when sending this command, while renmae considers it GBK encoding, the command will rename the file as a garbled name. (In Chinese Windows, the encoding is GBK by default, so we have never encountered such a garbled problem. In the upper left corner of the DOS window, right-click --> properties. The current code page: 936 (ANSI/oem-Simplified Chinese GBK) verifies that the DOS window is a GBK encoded sending character)

However, after entering the MySQL Command, the MySQL command may not necessarily know that the command you sent is GBK encoded. It may think that the command you sent is utf8 encoded, or the encoding that is consistent with the database encoding, or the encoding of the command string that is consistent with a global variable (which encoding is used by MySQL by default to receive the command, I have not studied it clearly, please refer to the official Supplement ).Therefore, to avoid the misunderstanding of your instruction code in MySQL, you need to add the -- default-character-set = GBK parameter when calling MySQL. Use mysql-uroot-p -- default-character-set = GBK to log on.Note: Do not use MySQL-uroot-p -- default-character-set = utf8 to log on because your database is UTF-8 encoded. We are talking about the encoding of the instruction string sent in DOS. The DOS window will not use utf8 encoding to send the instruction.

Of course, if a console sends commands encoded with UTF-8 characters, is it necessary to use
Mysql-uroot-p -- default-character-set = utf8? No, but the analysis should be the case.

Why is it okay to run the same SQL statement using GUI programs such as MySQL-front and sqlyog? When logging on to MySQL-front/sqlyog, you can specify the connection character set. I guess the SQL command sent in the SQL edit box after logging on may be encoded using this character set, if it is consistent with the database, of course there is no problem.

Attach a simple SQL test code:

# Drop database test_001;
Create Database test_001 default Character Set utf8 collate utf8_general_ci;
Use test_001;

Create Table config (
Id smallint not null default '1 ',
Name varchar (64) not null,
Descr varchar (64 ),
Primary Key (ID)
);
Create a table first, and then insert.
Use test_001;
Insert into config (ID, name, descr) values ('1', 'Chinese name', 'description ');

When the MySQL command does not use the -- default-character-set parameter
Error 1406 (22001): Data too long for column 'name' at Row 1
Error
If the -- default-character-set = GBK parameter is added to the MySQL Command, the error is ruled out!

The above test is in the Windows XP Chinese version, MySQL 5.0.27-Community-NT
The status information is:
MySQL ver 14.12 distrib 5.0.27, for Win32 (ia32)
Connection ID: 19
Current Database: test_001
Current User: root @ localhost
SSL: not in use
Using delimiter :;
Server version: 5.0.27-Community-NT
Protocol Version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
DB characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 7 hours 41 min 27 Sec

==============
In fact, the simplest test method is to first modify-default-character-set = gb2312 or utf8 in the my. ini file, restart MySQL, and then input data. If not, consider the above method.