自MySQL 5.5起BIGINT超出范围错误 [英] BIGINT Out-of-range Error since MySQL 5.5

问题描述

我正在为CMS使用嵌套集，但是由于MySQL 5.5，我无法移动节点.
引发以下错误:

I'm working with nested sets for my CMS but since MySQL 5.5 I can't move a node.
The following error gets thrown:

重新排序docs时出错:MySQL-DB中的错误:无效的SQL:

Error while reordering docs:Error in MySQL-DB: Invalid SQL:

 SELECT baum2.id AS id,
 COUNT(*) AS level
 FROM elisabeth_tree AS baum1,
 elisabeth_tree AS baum2
 WHERE baum2.lft BETWEEN baum1.lft AND baum1.rgt
 GROUP BY baum2.lft
 ORDER BY ABS(baum2.id - 6);

错误:BIGINT UNSIGNED值超出'(lektoren.baum2.id-6)'中的范围
错误编号:1690

error: BIGINT UNSIGNED value is out of range in '(lektoren.baum2.id - 6)'
error number: 1690

有人解决了这个问题吗?我已经尝试过铸造一些零件，但是没有成功.

Has anyone solved this Problem? I already tried to cast some parts but it wasn't successful.

推荐答案

BIGINT UNSIGNED是未签名的，不能为负.

BIGINT UNSIGNED is unsigned and cannot be negative.

如果id小于6，则表达式ABS(lektoren.baum2.id - 6)将使用负​​中间值.

Your expression ABS(lektoren.baum2.id - 6) will use a negative intermediate value if id is less than 6.

大概是较早的版本隐式转换为SIGNED.您需要进行投射.

Presumably earlier versions implicitly converted to SIGNED. You need to do a cast.

尝试

ORDER BY ABS(CAST(lectoren.baum2.id AS SIGNED) - 6)

这篇关于自MySQL 5.5起BIGINT超出范围错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！