Mysql – Is it ok to have ‘view count’ column in the same table

Just as I was about to post a comment that this looked like no serialization format I've ever seen, I had a lucky flash of insight. (What programming language would make it altogether too easy to stash an array in a string like that? Ah...)

I think you may find that what you're looking at is the output of the serialize() function in PHP... the companion function is unserialize().

I'm not aware of a mechanism for deserializing this natively in MySQL (but then again, I wasn't aware of the whole thing a few minutes ago)... but if I had to do it natively in MySQL, I would go to the source code of common_schema for ideas and helper functions like get_num_tokens(), which returns a count of the number of tokens found in a given string of delimited text. There is genuine outside-the-box genius lurking in common_schema.

Untwizzling scalar values into rows is not something easily done in MySQL but prettify_message() in common_schema provides an example of how it is technically possible... by splitting the strings and writing what you find in to a new table, it seems possible and maybe even borderline practical if you're not dealing with a massive data set. Figure that part out and you could theoretically even build a trigger to keep your more-properly-structured table syncronized whenever one of those serialized columns is updated.

Or you could write something in php to read from the database, deserialize the arrays, iterate them, and stuff a table, but doing it natively in MySQL would be much more fun and at least offers the potential to keep another table always-current when you need it without an external script.

This trigger does the job:

create trigger trx_updates_atrig
after insert on trx_updates for each row begin
DECLARE updatecount INT;
  set updatecount = ( select count(*) from trx_updates where request = new.request );
  if updatecount=4
    then
      update trx_request set finished=NOW() where id = new.request;
  end if;
end//

SQLFiddle Demo.