Using views with variables in MySQL

Posted June 18th, 2007 in Databases by Olexandr Melnyk

One of MySQL’s restriction when using views is that they cannot refer to local variables. So, for example, this statement will produce an error:

create view thematical_books as
select title
     , author
  from books
 where subject = @book_subject

A workaround for this limitation is quite simple: to use a function which returns variable value. For example:

create function book_subject()
returns varchar(64) as
return @book_subject;

create view thematical_books as
select title
     , author
  from books
 where subject = book_subject()

Share and Enjoy: