SQL Data Type to store build versions

What data type should i use for a SQL column to store Product version eg.

V开发者_如何学编程ersion
0.1
0.1.1
0.2
1.1
1.1.647
2.0
.....

In query i should be able to sort them based on version number and i want an optimal query to find highest number.

Thanks

I would consider storing each part of the number in a separate TINYINT/SMALLINT field.

Perhaps have three or four numeric fields to the version table:

Major, Minor, Revision, Build

A good solution would be to use an integer building the value to store like so:

MAJOR * 10000 + MINOR * 100 + Revision

Assuming each one can range from 0..99. If you want to go 0..999 use

MAJOR * 1000000 + MINOR * 1000 + Revision

This will sort properly, will query easily, is compact (1 int column), is easily decomposed and can even be decomposed visually.

Storing in separate numeric fields is a good idea. Storing as a string in a single field will break sorting when one of the parts reaches 1000. For example, 1.2.999 will appear before (or shown as newer than) 1.2.1000 when it should appear after.

You may want to store the versions in two separate columns. One as VARCHAR which would store the version value verbatim (to display in UI) and one as DECIMAL for sorting.

How to store a version as a DECIMAL in MySQL which is sortable

Assuming each part of a version can be a maximum of 4 digits, a version can be stored as:

"#{major}.#{format(minor)}#{format(revision)}"

Where the format() function is defined as:

sprintf("%0.4d", i)[0,4]

This value can be stored in a DECIMAL(12,8) column.

E.g., version 2021.7.115 would be stored as 2021.00070115.

If each part of a version can be maximum 5 digits (sprintf("%0.5d", i)[0,5]), above format would require a DECIMAL(15,10) column.

E.g., version 2021.7.115 would be stored as 2021.0000700115.

Why DECIMAL and not INTEGER data type?

The advantage of DECIMAL is that more digits can be appended to the right side in future without affecting sorting, which is not possible for INTEGER columns. E.g., {major}.{minor}{revision} can be changed to {major}.{minor}{revision}{morestuff} without affecting sorting (which wouldn't be possible for INTEGER columns without updating existing values).

PS: How to store a version as VARCHAR in MySQL which is sortable

Tricks from above can also applied to store the version in a string column.

E.g., a format

"#{format(major)}#{format(minor)}#{format(revision)}"

can store version values as a string in a VARCHAR column. Storing versions in a VARCHAR column would require more storage space than DECIMAL but it might be a necessity if version values include non-numeric characters.

E.g.,

• Version 1.2.4 would be stored as "000100020004".
• Version 2021.7.115 would be stored as "202100070115".