BIGINT UNSIGNED value is out of range in..的解决方法

时间:2022-08-25 16:39:12

今天在做一个功能的时候,用到两个时间戳相减来做查询条件,由于其两个字段都是unsigned的,并两个的大小是不一样。所以直接相减查询的时候,

就出现ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..的错误,因为结果可能会出现负数。。。。

过后查资料,发现可以通过下面的方法来解决:

[sql] view plaincopyBIGINT UNSIGNED value is out of range in..的解决方法BIGINT UNSIGNED value is out of range in..的解决方法
  1. mysql> select lastvisit, lastactivity from pre_common_member_status limit 1;  
  2. +------------+--------------+  
  3. | lastvisit  | lastactivity |  
  4. +------------+--------------+  
  5. | 1199200260 |   1198336989 |  
  6. +------------+--------------+  
  7. 1 row in set (0.01 sec)  
  8.   
  9. mysql> select lastvisit-lastactivity from pre_common_member_status limit 1;  
  10. +------------------------+  
  11. | lastvisit-lastactivity |  
  12. +------------------------+  
  13. |                 863271 |  
  14. +------------------------+  
  15. 1 row in set (0.05 sec)  
  16.   
  17. mysql> select abs(lastvisit-lastactivity) from pre_common_member_status limit 1;  
  18.   
  19. +-----------------------------+  
  20. abs(lastvisit-lastactivity) |  
  21. +-----------------------------+  
  22. |                      863271 |  
  23. +-----------------------------+  
  24. 1 row in set (0.03 sec)  
  25.   
  26. mysql> select lastactivity-lastvisit from pre_common_member_status limit 1;  
  27. ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`discuz`.`pre_c  
  28. ommon_member_status`.`lastactivity` - `discuz`.`pre_common_member_status`.`las  
  29. tvisit`)'  
  30. mysql> select cast(lastactivity as signed)-cast(lastvisit as signed) from pre_co  
  31. mmon_member_status limit 1;  
  32. +--------------------------------------------------------+  
  33. cast(lastactivity as signed)-cast(lastvisit as signed) |  
  34. +--------------------------------------------------------+  
  35. |                                                -863271 |  
  36. +--------------------------------------------------------+  
  37. 1 row in set (0.02 sec)  
  38.   
  39. mysql> select abs(cast(lastactivity as signed)-cast(lastvisit as signed)) from p  
  40. re_common_member_status limit 1;  
  41. +-------------------------------------------------------------+  
  42. abs(cast(lastactivity as signed)-cast(lastvisit as signed)) |  
  43. +-------------------------------------------------------------+  
  44. |                                                      863271 |  
  45. +-------------------------------------------------------------+  
  46. 1 row in set (0.00 sec)  
  47.   
  48. mysql>