如何将crc32存储在MySQL中？

Question

I'm creating a crc32 in PHP and need to store it in a field in a MySQL database. After reading about how there is a concern about the results on a 32-bit vs 64-bit machine, I'm wondering how this number should be stored. This is how I'm treating the crc32 in PHP to get the same result on either bitsize machine:

<?php
$checksum = crc32("The quick brown fox jumped over the lazy dog.");
// On a 32-bit system it prints -2103228862 instead of
// 2191738434 which is correct and what prints on a 64-bit system. 
// See the php.net manual page on crc32 for more information about
// 32-bit vs 64-bit.
echo "checksum without printf formatting: " . $checksum . "
";
printf("%u
", $checksum);
$string = sprintf("%u", $checksum);
echo $string . "
";
?>

Output (on a 64-bit machine is):

checksum without printf formatting: 2191738434
2191738434
2191738434

How should this number be stored on MySQL? Here are a few choices I've come up with so far:

`hash1` CHAR(10) NOT NULL ,
`hash2` varchar(32) NOT NULL,
`hash3` int unsigned NOT NULL,

It looks like I should go with:

`hash4` BIGINT UNSIGNED NOT NULL ,

Answer 1

You can store the values in MySQL as INT UNSIGNED which occupies 4 bytes (i.e. 32 bits).

To insert the values into the database, you must use sprintf() with %u format on 32 bit machines:

$hash = crc32("The quick brown fox jumped over the lazy dog.");

$stmt = $db->prepare('INSERT INTO mytable VALUES (:hash)');
$stmt->execute(array(
    ':hash' => sprintf('%u', $hash),
));

Update

You could also make sure that you're always working with int32 types (signed long) on both 32 and 64-bit platforms. Currently, you can only accomplish this by using pack() and unpack():

echo current(unpack('l', pack('l', $hash)));
// returns -2103228862 on both 32-bit and 64-bit platforms

The idea for this was contributed by mindplay.dk