2

Hi I am facing one issue, Post mysql 5.7 information_schema.GLOBAL_STATUS is deprecated instead of this they are using performance_schema.global_status.

So I would like to know depending on MySQL version how to query a particular table.

I know Select @@version gives me the version of mysql.

  SELECT * FROM performance_schema.global_status
            WHERE VARIABLE_VALUE REGEXP '^-?[0-9]+$'.

Thanks in advance.

Martin
  • 22,212
  • 11
  • 70
  • 132
user149621
  • 529
  • 2
  • 8
  • 12
  • 1
    Ideally we update the queries as per breaking changes in a new version! – Anil May 04 '17 at 11:48
  • What is the context in which you need to run this query? A stored proc, a function, a script? Clearly, you could execute the correct query from the command line by yourself. – Tim Biegeleisen May 04 '17 at 11:55
  • 1
    One option is to use a [14.5 Prepared SQL Statement Syntax](https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html). See [db-fiddle](https://www.db-fiddle.com/f/3PMNx6vwxNerug3DDW6Fiy/1) example. – wchiquito May 04 '17 at 12:40
  • Not looking like procedure or function. Want SQL query. – user149621 May 04 '17 at 13:49
  • any prepared statement for this problem , my basic query is SELECT LOWER(VARIABLE_NAME) AS "p_name", VARIABLE_VALUE AS "value", FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_VALUE REGEXP "^-?[0-9]+$"'; – user149621 Sep 08 '18 at 16:23

1 Answers1

0

Assuming your querying the database from outside of a MySQL shell (say, PHP) in the context of this answer. Why don't you try adding the version number of the later release in conditional comments (Note I haven't had time to try this)? Detailed here and here.

This means the first query would look like this, but only return results on versions of MySQL after 4.1.22:

/*!40122 SELECT col FROM mytable;*/

In the case there are results then these can be used as planned in your program and if not then you could use an if to execute the earlier version syntax like so:

SELECT differentSyntaxCol FROM mytable;

Note this can be adapted to cascade down a number of versions of MySQL if working with several versions, but as there is no syntax for 'below version X', you need to start with the newest and work down.

Regards,

James

James Scott
  • 1,032
  • 1
  • 10
  • 17
  • Ya agree, but using two query for different version one will work and other fail, that is the last option i wanted to do. if we can do IF-ELSE in query , that will be great. – user149621 May 04 '17 at 16:02
  • Not an option as far as I'm aware, 1) The parser is going to error on out of date syntax, and 2) for flow control `IF ELSE` can only be used inside stored procedures. Can you post the actual query? There may be a way forward commenting specific parts using conditional comments. Note if the first query above works, the second would never be run and therefore wouldn't fail. – James Scott May 04 '17 at 16:05
  • Above 5.6 SELECT * FROM performance_schema.global_status WHERE VARIABLE_VALUE REGEXP '^-?[0-9]+$'. and below SELECT * FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_VALUE REGEXP '^-?[0-9]+$'. – user149621 May 05 '17 at 07:43