MySQL: Changing length of blob

[HyperHacker]

I've just started looking at MySQL, it seems easy enough. I hope to make a simple forum-like system with it. I've laid out the basic plans but there's one part I'm not sure about. Rather than member groups or power levels, each user's info has an INT field whose bits determine what the user can do (post, edit profile, access admin stuff, etc). This works fine as it is but it means I can't rely on the member group/power level to determine who can do what in what forum. The solution I've come up with is to have a blob containing one byte per forum (indexed by forum ID) that tells what the user can do in each forum (view, post, start threads, etc), stored as a blob. This still presents a problem, though - if I add or remove a forum I have to change the length of this blob. This shouldn't be difficult to do, but the only method I can think of (drop and re-create the field with the new size) will result in its content being lost. Is it possible to do this without losing the contents, or is there a better way?

[edit] I thought of a better way - I'll just make a table with one column for the user ID and one per forum for the flags. However, I'd still like to know if there's an easy way to resize a blob and not lose its contents.

Edited November 16, 2005 by HyperHacker

[Rhelic]

Last I used MySQL the best way is to add a new field with the correct datatype. Then do (my syntax might be a bit off)...

UPDATE table SET tempcolumn = oldcolumn

Then drop the oldcolumn and re-create it with the same name and new size/datatype

UPDATE table SET newcolumn = tempcolumn

Then drop the tempcolumn. Now you have all your data in a newly defined column with no missing data.

And in case you're wondering, no, last I knew, you cannot change the order of the columns. Also, if this breaks your program then YOU CODED IT TOTALLY WRONG as the truth is, database column order is NOT set in stone so anybody who assumes column 1 is this and column 2 is that is shooting themselves in the foot. Always refer to columns by their column name.