[Mysql] The SQL _mode configuration associated with Data truncated for column, truncatedsql_mode
System Logs
Ata truncated for column 'agent' at row 1
The reason for this problem in mysql is nothing more than Character Set setting or a too long field.
Mysql has been unified into UTF-8 during initialization, so you can ignore this problem.
If the string is too long and the Import fails, one way is to modify the field length, but I cannot know how long the storage is suitable for this field. The other way is to intercept the string in the program. This method is feasible, it is also better.
But I thought again, why is it automatically truncated in the past, and this time it cannot be inserted? The reason is that the SQL _mode setting of mysql is incorrect. Let's take a look at this configuration item.
Let's take a look at the myql information.Version | 5.0.77-log
View sqlmode settingsMysql> show global variables like '% SQL _mode %'; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | SQL _mode | + --------------- + ------- + 1 row in set
You can also query mysql> SELECT @ global. SQL _mode; + ------------------- + | @ global. SQL _mode | + --------------------- + | + ------------------- + 1 row in set
By default, this item is null. In this setting, some illegal operations can be allowed, such as the insertion of some illegal data. This should be the work that should be done in database initialization. Here is the lack of experience.
Go to the official documentation: Workshop.
ANSI: equivalent to the combination of REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, and IGNORE_SPACE.
ORACLE: equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER. TRADITIONAL: equivalent to a combination of STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, delimiter, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION. MSSQL: equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. DB2: equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. MYSQL323: equivalent to the combination of NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE. MYSQL40: equivalent to the combination of NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE. MAXDB: equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER.
Modify Configuration:First, set the mysql process to be valid, and then set it in the configuration file to ensure that it will always take effect. I want myql to intercept and save it, so use the ansi mode.
Current Process
mysql> set global sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER';
Configuration File
sql-mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER'
Check mysq after setting.
Reference: http://technoboy.iteye.com/blog/1004905
This article from the "orangleliu notebook" blog, reprint please be sure to keep this source http://blog.csdn.net/orangleliu/article/details/41778595
Author orangleliu adopts signature-non-commercial use-share protocol in the same way
