php - How to store version number in MySQL database

Question

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.

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