How to negate integer value in MySQL 5.7?

I am performing a Select on 5.7.32 where I want to negate the values:

SELECT (val * -1) AS new_value

This results in the error:

Data truncation: BIGINT UNSIGNED value is out of range i

How can this be overcome?

Answer

You can skip rows where it is out of range:

select -val as new_value from foo where val <= 9223372036854775807

or you can just treat it as a string:

select concat('-',val) as new_value from foo;

casting to signed will not work, since it will change values larger than 9223372036854775807 to be negative numbers, which when negated will be positive.

You could convert it back to a number with:

select 0+concat('-',val) as new_value from foo;

but that will lose precision for large numbers.

fiddle