20

I want to store version number of my application in MySQL database for ex:

version 1.1.0 1.1.1 1.1.2

Which data type should I use.

Improve this question
4
  • 4
    You can use varchar Commented Mar 10, 2014 at 9:04
  • But if in future I want to sort these data that will not be possible Commented Mar 10, 2014 at 9:05
  • Why wouldn't it be sortable, add an index and it will sort just fine? Commented Mar 10, 2014 at 9:06
  • 1
    Since version numbers are allways ordered from major on the left to minor on the right, sorting is just fine. Only suggestion would be to store suffixes (alpha, beta, RC, etc...) and stable flag in seperate fields. Commented Mar 10, 2014 at 9:22

5 Answers 5

Reset to default
23

You have 2 options:

  1. Use varchar

  2. Use three numeric fields, Major, Minor, Patch

  3. Use both.

Each option has its advantages and disadvantages.

Option 1 is only one field, so it's easy to get the version. But it isn't necessarily sortable, since 2.0.0 will be lexicographically higher than 10.0.0.

Option 2 will be easily sortable, but you have to get three fields.

Option 3 Can be implemented using a view:

Table tversion (
  major NUMBER(3),
  minor NUMBER(3),
  patch NUMBER(3)
)

View vversion is 
  select major || '.' || minor || '.' || patch AS version,
         major * 1000000 + minor * 1000 + patch AS sortorder from tversion;
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Option 2 is easily sortable? What would a query selecting rows with version < 10.4.2 look like? Because that query would need to return version 9.9.9, for example.
Ok, it wouldn't be too complicated: (major = 10 and minor < 4 and patch < 2) or (major < 10). But I feel like it SHOULD be simpler :)
@JeromeJaglale If you add the view suggested by option 3 you can do like this: vversion.sortorder < 1004002
Ok, thank you. We could store that "optimized" version number directly in the table but the view makes it more maintainable I think.
@JeromeJaglale That was my thought as well.
|
3

Or, why not just use a number? eg:

210061

That would be 21.0.6 Beta 1

That is just how I did it.

But the principle is to multiply the sections of the version number in such a way that you end up with a integer representation of your version and store that.

These numbers will naturally be sortable.

When viewing the results you could break it down as to what the numbers represent.

Improve this answer

2 Comments

How would I know where to split the number to turn it back into the version? 210061 could very well be v2.10.06 beta 1.
@fazouth it is up to you to establish your own rules / limits.
2

I think using integer columns for each part (major, minor, revision and build) is the best solution. But for some reason, if you have to use one varchar column, then you can do this;

Format the version data 1.3.5.79 like this 001.003.005.00079

In this way, comparing and sorting problems are solved.

Improve this answer

Comments

1

You can use varchar() or even you can use INET_NTOA.Try this LINK

Improve this answer

2 Comments

Wouldnt NTOA limit his version parts to be 1-255 only?
@ToBe What version number do you know that is that long! 😀
-3

It's always better to use VARCHAR while storing different version numbers.

Improve this answer

1 Comment

This is ok if you only want to show the version number. If you want to order by version numbers, you will need to find another solution.

Your Answer

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