How to cast a 32-bit integer from unsigned to signed in MySQL or PHP?

Question

I have a string in PHP (came from some data source), which represents a formatted unsigned 32-bit integer. I need to store it into a MySQL database as a signed 32-bit integer, so that later I can retrieve it from PHP and use it as a (possibly negative) signed integer constant (since PHP doesn't have unsigned integers).

So, what I need is a conversion method, either for PHP or MySQL. It shouldn't be platform-dependent (no endian / 32/64-bit issues).

I know how to convert a signed integer into unsigned using MySQL:

select CAST((-1062726980 & 0xFFFFFFFF) AS UNSIGNED INTEGER);
+------------------------------------------------------+
| CAST((-1062726980 & 0xFFFFFFFF) AS UNSIGNED INTEGER) |
+------------------------------------------------------+
|                                           3232240316 | 
+------------------------------------------------------+

But I can't get it work the other way around (note: MySQL uses 64-bit arithmetic when doing casts).

Thanks.

Answer 1

If you just cast the number to an integer in PHP, it will do the trick.

echo (int)3232240316 . "\n";

gives

-1062726980

Note: if you want to cast a signed int to an unsigned int in PHP, just do this:

$number += 4294967296;

Example:

$number = -1062726980;
echo $number . "\n";
$number += 4294967296;
echo $number . "\n";

gives:

-1062726980
3232240316

Answer 2

Pure MySQL solution:

SELECT CAST((yourUnsigned<<32) AS SIGNED)/(1<<32)

gives you 32-bit complement UNSIGNED->SIGNED conversion, when yourUnsigned is in the range 0..4294967295. SIGNED->UNSIGNED is easy:

SELECT yourSigned & 0xFFFFFFFF;