MySQL不容忽视SQL_MODE的设置

浏览数：19 / 时间：2015年06月12日

CREATE DATABASE  db_test;

CREATE TABLE `tb1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id‘,
  `rank` int(10) unsigned NOT NULL DEFAULT ‘0‘ COMMENT ‘排名‘,
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘当前时间‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tb1(rank, type, add_time) VALUES(19, 1, ‘2015-03-01‘);
INSERT INTO tb1(rank, type, add_time) VALUES(10, 1, ‘2015-04-01‘);
INSERT INTO tb1(rank, type, add_time) VALUES(12, 1, ‘2015-05-01‘);

SELECT A.add_time, A.rank, B.add_time, B.rank, (B.rank - A.rank) AS diff
FROM tb1 A LEFT JOIN tb1 B ON A.type = B.type
WHERE A.add_time=‘2015-03-01‘ AND B.add_time = ‘2015-04-01‘;

#报错消息
#[Err] 1690 - BIGINT UNSIGNED value is out of range in ‘(`db_test`.`b`.`rank` - `db_test`.`a`.`rank`)‘

#解决方式：
SET sql_mode=‘NO_UNSIGNED_SUBTRACTION‘;
SELECT A.add_time, A.rank, B.add_time, B.rank, (B.rank - A.rank) AS diff
FROM tb1 A LEFT JOIN tb1 B ON A.type = B.type
WHERE A.add_time=‘2015-03-01‘ AND B.add_time = ‘2015-04-01‘;

#这样diff的值可以使负数了,具体sql_mode的用法请参考
http://tech.it168.com/a2012/0822/1388/000001388401.shtml

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。