Hi, Joerg,
Thank you very much for your clear reply. Now I completely understand
what is going on behind the screen.
Also a big thanks to all the people who gave me kind reply.
Regards,
Zhe
Joerg Bruehe wrote:
> Hi!
>
> Am Mo, den 28.02.2005 schrieb Hank um 17:09: [top-posting reordered!]
> > On Mon, 28 Feb 2005 10:05:32 -0500, Zhe Wang <zwang@stripped> wrote:
> > > Hi, there,
> > >
> > > I have MySQL 4.1.10. I need to add a timestamp column to an existing
> > > table. I am having a problem of setting the newly added column to
> be the
> > > current time.
> > >
> > > This is what I did:
> > >
> > > CREATE TABLE try (id INTEGER); INSERT INTO try VALUES(1), (2), (3);
> > > ALTER TABLE try ADD timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
> > > SELECT * FROM try;
> > >
> > > +------+---------------------+
> > > | id | timestamp |
> > > +------+---------------------+
> > > | 1 | 0000-00-00 00:00:00 |
> > > | 2 | 0000-00-00 00:00:00 |
> > > | 3 | 0000-00-00 00:00:00 |
> > > +------+---------------------+
> > >
> > > I've read the on-line manual regarding the change in timestamp,
> still
> > > couldn't figure out why 0's got inserted instead of a meaningful
> current
>
> They did not get inserted - see below.
>
> > > time stamp. I would greatly appreciate if someone can let me know
> what
> > > the correct way is. Unfortunately I cannot recreate the table.
> > >
> > > [[...]]
> > >
> > I'd suggest not using the keyword "timestamp" as a column name. I'd
> > suggest using "ts" or "tstamp" or something like that.
>
> I second that - avoid using keywords as identifiers!
>
> >
> > To update the records to the current timestamp....:
> >
> > update try set tstamp=null;
> >
> > should do it. I don't know why the default isn't working, though.
>
> The "default" is a value that is used when a row is inserted but no
> value for this columnt is provided. It is applied at insert time, but
> not at select time - here simply the existing value is returned.
> "ALTER TABLE ADD" creates the new column, but it does no inserts.
>
> The general procedure for such situations is:
> 1) Add the new column to the table.
> 2) Add code handling this column to all your triggers, stored
> procedures, web interfaces, application programs, ... that enter data
> into your DB.
> 3) Use an Update command to set the default value into this column in
> all rows where it is still empty or NULL. (These are the rows inserted
> before the respective application provided a value.)
> 4) Add code handling this column to all data output functions. (Now you
> are sure all DB contents is valid.)
>
> If necessary: Repeat steps 2), 3), and 4) if you missed an application,
> or if your logic demanded you to have valid contents immediately.
>
> HTH,
> Joerg
>
> --
> Joerg Bruehe, Senior Production Engineer
> MySQL AB, www.mysql.com
>
> Are you MySQL certified? www.mysql.com/certification
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
