BIGINT 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:

我正在为我的CMS使用嵌套集,但是从MySQL 5.5起我无法移动节点。抛出以下错误:

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

重新排序文档时出错:MySQL-DB中出错: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);

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

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

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

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

3 个解决方案

#1

BIGINT UNSIGNED is unsigned and cannot be negative.

BIGINT UNSIGNED是无符号的,不能为负数。

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

如果id小于6,您的表达式ABS(lektoren.baum2.id - 6)将使用负中间值。

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

据推测,早期版本隐式转换为SIGNED。你需要做一个演员。

Try

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

#2

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

Call this before the query is executed.

在执行查询之前调用它。

#3

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

按ABS排序(CAST(lectoren.baum2.id AS BIGINT SIGNED) - 6)

That change would be mysql only.

那个改变只是mysql。

instead, do

ORDER BY ABS(- 6 + baum2.id);

#1