jump to navigation

MySQL views gotcha October 23, 2007

Posted by Olexandr Melnyk in : Databases , trackback

Let’s suppose we have a simple posts table and a view, corresponding to the posts of the current week (let’s keep values hardcoded, for the sake of simplicity):

create table posts (
  id integer not null auto_increment,
  title varchar(128) not null,
  content text not null,
  submit_time datetime not null,
  primary key (id),
  key (submit_time)
);

create view posts_this_week as
  select *
    from posts
    where submit_time >= '2007-10-21 00:00:00'
      and submit_time < '2007-10-28 00:00:00';

So, say, you want to add an extra field to the posts table:

alter table posts
  add last_edit_time datetime not null;

You expect the field to be fetched by view as well, right? Wrong. In MySQL, the list of the fields to be fetched by a view is made up on view definition, rather than once the view used.

Often, such behaviour can lead to wrong assumptions (much due to classical explanation of views as query aliases), such as: server being out-of-sync when using replication, thus the table not containing the new field. So, when designing your application, keep in mind, that views, which should contain all fields from the table, have to be redifened every time table structure is altered.

Technorati Tags: , ,

Comments»

1. Brik - December 19, 2007

WOW - I did not know that!!! I was about to mess up a bunch of stuff!

I am sort of new to mySQL and my DB experience is pretty slim in general. I am forced to work on a data collection app… long story.

Anyway - I have been researching how to create views with database triggers. Seems I also want to update my view(s) with a trigger if an underlying table changes!

Any examples for me? One example of a trigger that creates a view, using a fieldname as the viewname and also a fieldname in the where clause for the view? TIA. How about a trigger that keeps track and updates all views? (I need that less)

2. Olexandr Melnyk - December 22, 2007

Hello Brik,

Seems I also want to update my view(s) with a trigger if an underlying table changes!

MySQL doesn’t support schema-level triggers, so you’ll have to handle that in your application.

Any examples for me? One example of a trigger that creates a view, using a fieldname as the viewname and also a fieldname in the where clause for the view? TIA.

I don’t understand this part, could you elaborate on that?

How about a trigger that keeps track and updates all views? (I need that less)

When you change data in a table, a related view gets automatically updated. Please, read my post again carefully: view doesn’t get updated on table structure changes.

3. ???????? - April 15, 2008

Yo man , ???? ????????????