[MySQL Learning] Strict_all_tables corresponding out of RANGE VALUE for column and data truncated for column

Yesterday, a statement was used in the stored procedure insert into a select from B. When the bigint field is found to be inserted into the Int field in the app, the value is truncated and is not the desired value after insertion. Because the processing of exception captures is not used in the stored procedure, no errors were found at first, but only when compared to the data. Later, compared to the two tables, it was found that the field type is not the same. Next, try to verify (the Test Environment under window).

Create two tables

CREATE TABLE t_int (id INT, VName VARCHAR (20)); CREATE TABLE t_bigint (id BIGINT, VName VARCHAR (40));

Test bigint Insert to int: If the maximum value of int is exceeded, the maximum power (2,31)-1 is truncated directly. If you are working directly at the command line, there will be a warning prompt.

</pre><p></p><p></p><pre code_snippet_id= "397037" Snippet_file_name= "Blog_ 20140618_3_2598704 "name=" code "class=" SQL ">insert into T_int (id,vname) VALUES (POWER (2,31), ' 0123456789abcdefghij '); SHOW warnings;1264 out of RANGE VALUE to COLUMN ' id ' at ROW 1; SELECT * from T_int; SELECT POWER (2,31) 21474836482147483647

Test varchar (40) inserted into varchar (20): The same will also be intercepted directly by the maximum value of the field, the command line will be a warning thrown.

INSERT into T_int (id,vname) VALUES (POWER (2,31), ' 0123456789abcdefghij1 '); SHOW warnings;1264 out of RANGE VALUE to column ' id ' at row 1;1265 DATA truncated for column ' VName ' at row 1;idvname2147 4836470123456789abcdefghij21474836470123456789abcdefghijinsert into T_bigint (id,vname) VALUES (POWER (2,31), ' 0123456789abcdefghij1 '); INSERT into T_int SELECT * from T_bigint;

Because I use SQLyog, the default mode is empty, so in this debugging, there is a problem. An error occurred in the app because the client's sql_mode was not specified in my URL.

/*[17:15:32][  ms]*/SHOW VARIABLES like ' lower_case_table_names ';/*[17:15:32][   0 ms]*/SET names ' UTF8 ';/*[ 17:15:32][   0 ms]*/SET sql_mode= ";/*[17:15:32][  ms]*/SHOW DATABASES;

The direct connection to MySQL under CMD is not a problem, because I have configured the Sql_mode in the configuration file.

Mysql> INSERT into T_int (id,vname) VALUES (POWER (2,31), ' 0123456789abcdefghij '); ERROR 1264 (22003): Out of Range value to column ' id ' at row 1mysql>mysql> show variables like '%mode% '; +---------- ----------------+----------------------------------------------------------------+| Variable_name            | Value            |+--------------------------+----------------------------------------------------------------+| Innodb_autoinc_lock_mode | 1            | | | Slave_exec_mode          | STRICT            | | Sql_mode                 | Strict_trans_tables,no_auto_create_user,no_engine_substitution |+--------------------------+------------------- ---------------------------------------------+3 rows in Set (0.00 sec) mysql>

However, if you encapsulate the INSERT statement in a stored procedure, you will not get an error and there will be a warning.

Mysql> call Pro_insert (); Query OK, 1 row affected, 3 warnings (0.01 sec) mysql> select * from t_int;+------------+----------------------+| ID | VName |+------------+----------------------+| 2147483647 | 0123456789abcdefghij | | 2147483647 | 0123456789abcdefghij |+------------+----------------------+2 rows in Set (0.00 sec) mysql> set sql_mode= ' Strict_all_ TABLES '; Query OK, 0 rows Affected (0.00 sec) Mysql> call Pro_insert (); Query OK, 1 row affected, 3 warnings (0.03 sec) mysql> select * from t_int;+------------+----------------------+| ID | VName |+------------+----------------------+| 2147483647 | 0123456789abcdefghij | | 2147483647 | 0123456789abcdefghij | | 2147483647 | 0123456789abcdefghij | | 2147483647 | 0123456789abcdefghij |+------------+----------------------+4 rows in Set (0.00 sec) mysql> Show variables like '%mode% ';+--------------------------+-------------------+| variable_name | Value |+--------------------------+-------------------+| Innodb_autoinc_lock_mode | 1 | | Slave_exec_mode | STRICT | | Sql_mode | Strict_all_tables |+--------------------------+-------------------+3 rows in Set (0.00 sec) mysql>

The reason for the problem is because of sql_mode. And I have no exception handling mechanism. The next article first put Sql_mode.

Solution, just throw the wrong. In the stored procedure set sql_mode= ' Strict_trans_tables,no_engine_substitution '; (Last night class when the leaf total reminder, in the stored procedure set Sql_mode)

Mysql> call Pro_insert (); Query OK, 1 row affected, 3 warnings (0.01 sec) mysql> Show warnings;+---------+------+------------------------------- --------------+| Level   | Code | Message                                     |+---------+------+---------------------------------------------+| Warning | 1264 | Out of range value for column ' ID ' at row 1 | | Warning | 1264 | Out of range value for column ' ID ' at row 2 | | Warning | 1265 | Data truncated for column ' VName ' at row 2  |+---------+------+---------------------------------------------+3 rows In Set (0.00 sec) Mysql> call Pro_insert (); ERROR 1264 (22003): Out of Range value to column ' id ' at row 1mysql>

Stored Procedures

DELIMITER $ $USE ' test ' $ $DROP PROCEDURE IF EXISTS ' Pro_insert ' $ $CREATE definer= ' root ' @ '% ' PROCEDURE ' Pro_insert ' () Beginset sql_mode= ' strict_trans_tables,no_engine_substitution '; INSERT into T_int (id,vname) VALUES (POWER (2,31), ' 0123456789abcdefghij '); INSERT into T_int (id,vname) VALUES (POWER (2,31), ' 0123456789abcdefghij1 ');    end$ $DELIMITER;