2

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

6
  • 1
    Could you please provide some example numbers?
    – J.D.
    Oct 10, 2021 at 13:09
  • 1
    @J.D. hey JD, I’ve updated the question. Let me know if this helps and/or if I should be more specific. Thanks! Oct 10, 2021 at 13:31
  • 3
    You can use the DECIMAL data type, specifying a precision up to 65 digits, and a scale from 0 to 30 digits after the decimal. Do you really need to support up to 255 digit integers? Or would 65 digits of DECIMAL(65,0) suffice?
    – AMtwo
    Oct 10, 2021 at 13:53
  • 2
    All you need is leading '0's.
    – mustaccio
    Oct 10, 2021 at 13:59
  • 2
    Please explain the use case. That is, what do these numbers represent?
    – Rick James
    Oct 10, 2021 at 17:47

3 Answers 3

7

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
;
1
  • The collation for that column may as well be ascii_bin. Do not store leading zeros, it will mess up the ORDER BY. If the table is big, the Optimizer will necessarily scan the entire table when doing a range test.
    – Rick James
    Oct 10, 2021 at 18:02
3

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.)

0

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".

4
  • 1
    I think you missed the Point. With 999,1000,1001 OP means that there are three rows, each with one number and this is how they are sorted.
    – marstato
    Oct 12, 2021 at 8:49
  • If there are three rows, each with a number, then they will be sorted as numbers. If there are three rows, each with a string, then they will be sorted as strings. If the OP wants things sorted as numbers, then they need to be stored as numbers. Period. Implicit Type Coercion has some nasty side-effects, not least of which it slows everything down.
    – Phill W.
    Oct 12, 2021 at 11:26
  • 1
    OP has realized that, too. But the regular integer fields don't work for them, so they are asking what else to do. But as it stands this post answers a question that hasn't been asked.
    – marstato
    Oct 12, 2021 at 14:29
  • Personally, I'm still not convinced that the O.P. is /actually/ trying to store numbers that are more than 20 digits in length (BitInt's top out at 2^64, which is about 1.8x10^19) but, if that really /is/ the case, then zero-padding each value is probably their only other solution. Floating Point Data Types can handle the numerical /size/ that they require, but not necessarily the integral granularity of their Requirement.
    – Phill W.
    Oct 15, 2021 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.