Execute Query based on database version MySQL

Question

I want to execute the queries based on Database version, this is my requirement If the MySQL database version is 5.6 i want to alter the table column and add full text otherwise if version is less i don't want to alter the table. The reason behind is i want to do LIKE %Something% against the column and the table is created using INNODB schema, as am not an expert in MySQL DB i googled for LIKE query performance for 2M+ records. Most of them are against using LIKE with double %. And also this InnoDB Fulltext support. So if i make the query to be executed based on DB version most of the users will get the benefit but who ever in the older version of DB(not willing to update) should adjust with performance. Thanks in advance.

Aman Aggarwal · Accepted Answer · 2015-06-18 10:40:37Z

2

You can do this using a stored procedure like this:

DELIMITER $$

CREATE

    PROCEDURE `test`.`temp`()

    BEGIN
declare version1 varchar(10);

set version1 = (select version());

if(version1 like '5.6%') then
----  your alter query here 
end if;

    END$$

DELIMITER ;

answered Jun 18, 2015 at 10:40

Aman Aggarwal

18.6k10 gold badges55 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MontyPython Over a year ago

this looks like a nice solution. Do people do that?

MontyPython Over a year ago

Achha! This is pretty useful then. Cool!

Aman Aggarwal Over a year ago

I mean use it.. Thanks :)

mallikarjun Over a year ago

Thanks @AmanAggarwal it helped to solve the issue. I want to add more info on alter statement inside MySQL ` SET @ddl = CONCAT('ALTER TABLE ', MYTABLE, 'ADD FULLTEXT INDEX some_index column (', COLNAME ')'); PREPARE STMT FROM @ddl; EXECUTE STMT;}`

Collectives™ on Stack Overflow

Execute Query based on database version MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related