Recipe 9.10. Writing Applications That Adapt to the MySQL Server Version

Problem

You want to use a given feature that is only available in a particular version of MySQL.

Solution

Ask the server for its version number. If the server is too old to support a given feature, maybe you can fall back to a workaround, if one exists.

Discussion

Each version of MySQL adds features. If you're writing an application that requires certain features, check the server version to determine if they are present; if not, you must perform some sort of workaround (assuming there is one).

To get the server version, issue a SELECT VERSION⁠(⁠ ⁠ ⁠) statement. The result is a string that looks something like 5.0.13-rc or 4.1.10a. In other words, it returns a string consisting of major , minor, and "teeny" version numbers, possibly some letter at the end of the "teeny" version, and possibly some suffix. The version string can be used as is for presentation purposes if you want to produce a status display for the user. However, for comparisons, it's simpler to work with a numberin particular, a five-digit number in Mmmtt format, in which M, mm, tt are the major, minor, and teeny version numbers. The conversion can be performed by splitting the string at the periods, stripping off from the third piece the suffix that begins with the first nonnumeric character, and then joining the pieces. For example, 5.0.13-rc. becomes 50013, and 4.1.10a becomes 40110.

Here's a Perl DBI function that takes a database handle argument and returns a two-element list that contains both the string and numeric forms of the server version. The code assumes that the minor and teeny version parts are less than 100 and thus no more than two digits each. That should be a valid assumption, because the source code for MySQL itself uses the same format.

sub get_server_version { my $dbh = shift; my ($ver_str, $ver_num); my ($major, $minor, $teeny);   # fetch result into scalar string   $ver_str = $dbh->selectrow_array ("SELECT VERSION()");   return undef unless defined ($ver_str);   ($major, $minor, $teeny) = split (/\./, $ver_str);   $teeny =~ s/\D*$//; # strip any nonnumeric suffix if present   $ver_num = $major*10000 + $minor*100 + $teeny;   return ($ver_str, $ver_num); } 

To get both forms of the version information at once, call the function like this:

my ($ver_str, $ver_num) = get_server_version ($dbh); 

To get just one of the values, call it as follows:

my $ver_str = (get_server_version ($dbh))[0]; # string form my $ver_num = (get_server_version ($dbh))[1]; # numeric form 

The following examples demonstrate how to use the numeric version value to check whether the server supports certain features:

my $ver_num = (get_server_version ($dbh))[1]; printf "Quoted identifiers: %s\n", ($ver_num >= 32306 ? "yes" : "no"); printf "UNION statement:    %s\n", ($ver_num >= 40000 ? "yes" : "no"); printf "Subqueries:         %s\n", ($ver_num >= 40100 ? "yes" : "no"); printf "Views:              %s\n", ($ver_num >= 50001 ? "yes" : "no"); printf "Strict SQL mode:    %s\n", ($ver_num >= 50002 ? "yes" : "no"); printf "Events:             %s\n", ($ver_num >= 50106 ? "yes" : "no");