How should crc32 be stored in MySQL?

Question

I am creating crc32 in PHP and should store it in a field in a MySQL database. After reading about how a problem arises with the results on a 32-bit or 64-bit machine, I wonder how this number should be stored. This is how I handle crc32 in PHP to get the same result on a machine with bitrate:

<?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 . "\n"; printf("%u\n", $checksum); $string = sprintf("%u", $checksum); echo $string . "\n"; ?> 

Output (on a 64-bit machine):

 checksum without printf formatting: 2191738434 2191738434 2191738434 

How should this number be stored in MySQL? Here are a few options that I have come up with so far:

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

Looks like I should go with:

 `hash4` BIGINT UNSIGNED NOT NULL , 

Answer 1

You can save the values ​​in MySQL as INT UNSIGNED , which takes 4 bytes (i.e. 32 bits).

To insert values ​​into the database, you must use the sprintf() format with %u 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 can also make sure that you always work with int32 types (signed for a long time) on both 32 and 64-bit platforms. Currently, you can only do this with pack() and unpack() :

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

The idea behind this was introduced by mindplay.dk