Numeric keys in MySQL are much faster than string keys. You need to make decimal numbers from any hash.

On the stack I found a solution like this:

hexdec(substr(md5($element_id),-15,15)));

Are there more elegant methods for obtaining decimal hash?

PS more elegant methods of maintaining the database so far do not need to offer. The first line is given only for orientation, for example, in length, or so that no objection arises that a hexadecimal number is also a number.

  • You can also try this: bindec (md5 ($ string, true)); - ReinRaus
  • Not okay. It does not understand the number of such length, but if you cut it down, then see my version. =) MD4 will not save either. And the base will understand if the result will be 19 digits (or 20 unsigned). - knes
  • As an option, it is to cut the raw string md5 to 8 characters, and the bindek to it. Essentially the same as in the question, but much faster should be. - ReinRaus
  • And how can you trim a raw string? O_o - knes
  • Unclearly expressed. bindec (substr (md5 ($ string, true), -8, 8) - ReinRaus

3 answers 3

  • Personally, it seems to me that this task is unsolvable, since all variants with the selection of a fragment of a unique hash automatically lose their uniqueness property and cannot be used as a key.

Something similar to your version, by the way, is done here (based on the sha1 hash).

  • That is, you, of course, can use your option (and any other option with converting some significant part of a unique hash) , but you risk running into trouble if there are a lot of records in the database or if the stars are unfavorable :)

Moreover, I think that if such a conversion would be possible, it would have been implemented a long time ago on the side of popular databases that allow string keys.

    I do not really understand why this is an intractable conflict. Let's look calmly:

    1. There is a set of bytes representing some kind of hash.
    2. A set of bytes is uniquely and always (mutually reversible) translated into an integer value - the only question is in the length of the set of bytes.

    Example: there is a valid hash of 8 bytes in length - this can always and mutually reversibly be passed on to a long (Java).

    The whole question only rests on the length of the whole. In Java, this problem does not exist - because you can always: new BigInteger(byte[] val) and get an integer of arbitrary length.

    • In Java, BigInteger (byte [] val) // definitely thanks for that. =) What concerns this method for the database, for muscular, for example, such a trick is no longer a ride, alas. - knes

    Isn't it easier to calculate the hash yourself? https://code.google.com/p/boyanov/wiki/FNVHash

    • @VadimTukaev, this method is unlikely to work. As far as I understand, the problem is that everything must be solved at the SQL level. Although, in question there is a label PCP ... - avp
    • I do not really know the specifics of SQL. What happens if, in a query waiting for a number, to send a string of letters and numbers? If he filters the letters, and turns the remaining numbers into a number, then this may be enough. Obviously, with this we can get a number with the number of digits from 0 to 32, but on average it will be a 20-digit number. More than enough, IMHO. - VadimTukaev