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

昨天在存储过程中用了语句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。假设是直接在命令行下操作时。会有警告提示。

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;

idvname

21474836470123456789abcdefghij

21474836470123456789abcdefghij

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 ;