At 17:55 -0400 4/19/04, Stormblade wrote:
On Sun, 18 Apr 2004 14:18:40 -0500, Paul DuBois wrote:
At 13:30 -0400 4/18/04, Stormblade wrote:
On Sun, 18 Apr 2004 12:17:00 -0400, Michael Stassen wrote:
Stormblade wrote:
Hey all,
I'm currently converting a SQLServer 2000 database over to
MySQL. I have a
web application that currently uses SQLServer but will be
using MySQL soon
as I can get this done.
I was able to match data types but so far I have not found a
way to let the
database handle setting the default value of a datetime field to the
current date/time. Is this possible?
Defaults must be constants, not functions, in MySQL. So, except for the
TIMESTAMP type, the answer is no.
I suspected as much. I wonder if they plan to add this in the future or if
there is some technical reason that they did not allow for this.
Actually, in MySQL 4.1.2, you'll be able to do this. Currently, the
first TIMESTAMP column is set to the current timestamp when a record
is created by default *and* updated when any other column is changed.
In 4.1.2, you'll be able to decouple this (for one TIMESTAMP column in
the table), so that the column can be set to the current timestamp for
record creation, but not changed afterward unless you change it explicitly.
I am a bit confused. You say that in MySQL 4.1.2 you'll be able to do this.
What is "this?" I ask because what you replied to was me wondering about
the ability to use functions/macros as default values in MySQL but then you
continue on but talk about timestamp and what you can do with it.
Are you talking about two different things here? Or are you telling me that
yes we will be able to assign functions for default values and in addition
to that timestamp can be used thusly..?
Sorry if I wasn't clear. One of the things you wanted to do was have
a column that is set automatically to record-creation time when the
record is created, but not updated automatically when the record is
updated later. You will be able to do this in 4.1.2 with TIMESTAMP,
which is what the example below demonstrates.
This applies only to TIMESTAMP. You will not otherwise be able to specify
functions as default values.
For example:
mysql> create table t (ts timestamp default current_timestamp, i int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t (i) values(1);
Query OK, 1 row affected (0.18 sec)
mysql> select * from t;
+---------------------+------+
| ts | i |
+---------------------+------+
| 2004-04-18 14:15:08 | 1 |
+---------------------+------+
1 row in set (0.04 sec)
mysql> update t set i = i + 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+---------------------+------+
| ts | i |
+---------------------+------+
| 2004-04-18 14:15:08 | 2 |
+---------------------+------+
1 row in set (0.00 sec)
Note that the ts column did not change when the i column was updated.
> This isn't documented in the manual yet, because I am still working on it.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
