CREATE DATABASE db_test; CREATE TABLE ' tb1 ' (' id ' int () unsigned not null auto_increment COMMENT ' auto-increment id ', ' rank ' int (ten) unsigned NOT NULL DEFA ULT ' 0 ' COMMENT ' rank ', ' add_time ' timestamp not NULL DEFAULT current_timestamp COMMENT ' current time ', PRIMARY KEY (' id ')) ENGINE =innodb DEFAULT Charset=utf8;insert into tb1 (rank, type, add_time) VALUES (1, ' 2015-03-01 '); INSERT into tb1 (rank, type, Add_time) VALUES (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_t Est '. ' 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的值可以使负数了, please refer to the use of specific Sql_mode
Http://tech.it168.com/a2012/0822/1388/000001388401.shtml
MySQL cannot ignore the Sql_mode settings