MySQL不容忽視SQL_MODE的設定

標籤：

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 diffFROM tb1 A LEFT JOIN tb1 B ON A.type = B.typeWHERE 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 diffFROM tb1 A LEFT JOIN tb1 B ON A.type = B.typeWHERE 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

 

 

MySQL不容忽視SQL_MODE的設定