How To Store Version Number In Mysql Database

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.

Solution 1:

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 isselect major ||'.'|| minor ||'.'|| patch AS version,
         major *1000000+ minor *1000+ patch AS sortorder from tversion;

Solution 2:

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

Solution 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 multiple 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 in head as to what the numbers represent.

Solution 4:

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.