[MySQL学习]STRICT_ALL_TABLES相应的OUT of RANGE VALUE FOR COLUMN和DATA truncated FOR COLUMN

昨天在存储过程中用了语句insert into a select from b。在应用中发现bigint字段插入到int字段的时候，数值被截断了。插入后已经不是所要的值。

由于存储过程中没实用到异常捕捉的处理。所以一開始并未发现错误。仅仅是在比对数据的时候才发现不正确。后来比对了两张表，才发现是字段类型不一样。

接下来就试着验证了一下（window下的測试环境）。

创建两张表

CREATE TABLE T_INT(id INT ,vname VARCHAR(20));
CREATE TABLE T_BIGINT(id BIGINT ,vname VARCHAR(40));

測试bigint插入到int：假设超过了int的最大值，则直接截成最大值power(2,31)-1。假设是直接在命令行下操作时。会有警告提示。

</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 FOR COLUMN 'id' AT ROW 1;

SELECT * FROM t_int;
SELECT POWER(2,31)
2147483648
2147483647

測试varchar(40)插入到varchar(20):相同也会直接依照字段最大值截取。命令行下会有警告抛出。

INSERT INTO t_int(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij1');
SHOW WARNINGS;
1264 OUT of RANGE VALUE FOR COLUMN 'id' AT ROW 1;
1265 DATA truncated FOR COLUMN 'vname' AT ROW 1;

id	vname
2147483647	0123456789abcdefghij
2147483647	0123456789abcdefghij

INSERT INTO T_BIGINT(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij1');
INSERT INTO t_int SELECT * FROM t_bigint;

由于我用的是SQLyog，默认的模式是空，所以在这个上面调试的时候，就出问题了。在应用中出错。是由于我的url中没有指定client的sql_mode。

/*[17:15:32][  31 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][  16 ms]*/ SHOW DATABASES;

在cmd下直接连接MySQL是不会有问题，由于我已经在配置文件里配置了sql_mode。

mysql> INSERT INTO t_int(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>
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_S
UBSTITUTION |
+--------------------------+----------------------------------------------------
------------+
3 rows in set (0.00 sec)

mysql>

可是假设将insert语句封装到存储过程中，不会报错。仅仅会有有警告。

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>

出问题的解决办法就是由于sql_mode。

还有就是我没有异常处理机制。

下一篇先贴一下sql_mode.

解决方式，直接抛错。在存储过程中SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';（昨天晚听课时叶总提醒。在存储过程中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 for column 'id' at row 1
mysql>

存储过程

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `PRO_INSERT`$$

CREATE DEFINER=`root`@`%` PROCEDURE `PRO_INSERT`()
BEGIN
SET 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 ;