Order large numbers that are saved as a varchar

Question

An unsigned bigint is the maximum int size in mysql as far as I'm aware. So for good measure I'm using varchar(255) since some values in my db are larger than an unsigned bigint.

I'm looking to order them though as if they were an int though. Is this possible?

Example: 999,1000,1001

Ordering this by Int goes 999,1000,1001 or 1001,1000,999

Ordering this by varchar goes 1000,1001,999 or 999,1001,1000

Since sorting via varchar compares a string one at a time

I’m wondering if it’s possible to store it via varchar but sort like an int

Answer 1

If all the values are positive integers and they never have leading 0s, you could try the following sorting method:

SELECT
  *
FROM
  MyTableOfBigNumbers
ORDER BY
  CHAR_LENGTH(IntegerStringColumn) ASC
, IntegerStringColumn              ASC
;

This way shorter string literals will correctly go before longer ones, as should be expected. Numbers with the identical number of digits will be sorted based on your active collation, which I hope already sorts the numerals in the sane way (0, 1, 2, 3, 4, 5, 6, 7, 8, 9).

To reverse the sorting direction, just replace ASC with DESC:

SELECT
  *
FROM
  MyTableOfBigNumbers
ORDER BY
  CHAR_LENGTH(IntegerStringColumn) DESC
, IntegerStringColumn              DESC
;

Answer 2

BIGINT UNSIGNED tops out at about 19 digits, and keeps precision down to "1".

Consider FLOAT. It tops out at about 38 digits, but has precision of only about 7 digits. (And it takes only 4 bytes to store).

Or DOUBLE -- 308 digits, 16 precision. 8 bytes.

Or (as already mentioned) DECIMAL(65,0) -- 65, 65, 29 bytes.

Yet another idea... Both a VARCHAR and a DOUBLE. Use the VARCHAR for storing a very long string of digits and DOUBLE for ordering and sorting. (Flaw: Two numbers with the same first 16 digits may sort incorrectly; can you live with that?)

VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin -- 255, 255, <=256

TEXT CHARACTER SET ascii COLLATE ascii_bin -- 65535, 65535, <=65537

MEDIUMTEXT CHARACTER SET ascii COLLATE ascii_bin -- ~16M-1, ~16M, <=16M+3

Consider INT UNSIGNED, etc. That gives one extra a bit's worth of range for each of the *INT types, over the default of SIGNED. (UNSIGNED is irrelevant or unnecessary for the other datatypes.)

Answer 3

An unsigned bigint is the maximum int size in mysql as far as I'm aware. So for good measure I'm using varchar(255) since some values in my db are larger than an unsigned bigint.

You're storing 255 digit numbers? Forgive me, but I don't think so.

999,1000,1001

That doesn't look like a decimal number to me (although 99,910,001,001 would).
That looks to me like an array of [three] decimal numbers stored in the same field.
That's a Bad Idea, as you've discovered.

Split these out into a [sub-]table with the primary key of the original record, plus this new value.

select * 
from main_table ; 

+----+
| pk | 
+----+
| 77 | 
+----+

select * 
from sub_table ; 

+----+-------+
| pk | value |
+----+-------+
| 77 |   999 |
| 77 |  1000 |
| 77 |  1001 |
+----+-------+

Then you can do integer sorting on a proper, Integer field.

To paraphrase one Montgomery Scott:

"Always use the Right Data Type for the Right Job".