Resolution about MySQL appearing 1264 out of range value for column errors

The time to restore MySQL data to customer service today. Data that would have been tested well. But to the customer there is no work. Old Error!

1	INSERT INTO ka_tan4 set num=‘716641385999‘, username=‘admin‘,adddate=‘1353078270‘,rate=‘2‘,sum_m=‘500‘,tjrenid=‘1259‘

Error occurred when:

1	#1264 - Out of range value adjusted forcolumn ‘num‘at row 1

Reason:
A strict check of the new version of MySQL on the field. Normally after MySQL is installed under Linux, the default Sql_mode value is empty, in this case MySQL is performing a strict check, if the length of the field to be inserted beyond the length of the column definition, then MySQL will not terminate the operation, but will automatically truncate the subsequent character to continue the insert operation! But underneath windows, it's not like that. It's a direct stop to report the above error!

Workaround (Choose one of two methods):
1, modify the My.ini, will

1	sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Switch

1	sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restart MySQL.
2. Execute the following statement before executing the SQL statement:
mysql_query ("Set sql_mode=");

3. Modify the MySQL database table structure to fit the data length, the best way is to modify the configuration file. Such The platform gap will not be too big!

The frequently used Sql_mode values are the following table:

Sql_mode value	Describe
Ansi	Change the syntax and behavior to make it more compliant with standard SQL.
Strict_trans_tables	If the given value cannot be inserted into the transaction table, the statement is discarded, for non-transactional tables, if the value appears in the first row of a single statement or multiline statement, the statement is discarded.
Traditional	Make MySQL behaves like a "traditional" SQL database system. A simple description of the pattern is "give an error instead of a warning" when an incorrect value is inserted in the column. Note: Once you find an error, discard insert/update immediately. If you use a non-transactional storage engine, this is not what you want, because the data changes made before the error do not "scroll" and the result is "only part of the update".

Note: If you set the value of Sql_mode to the following two values (that is, strict mode), MySQL will give an error when inserting or updating the incorrect value in the column, and discard the insert/update operation. These two modes are recommended in our general application instead of using the default NULL or ANSI mode. However, it is important to note that if the database is running in strict mode and your storage engine does not support transactions, there is a risk of data inconsistency, such as: There are two DML statements in a set of SQL, if there is a problem in the following, but the previous operation succeeded, Then MySQL cannot roll back the previous operation, so setting up Sql_mode requires the application to weigh the pros and cons to get an appropriate choice (CSDN)

Resolution about MySQL appearing 1264 out of range value for column errors