How do I go about removing the on update for my TIMESTAMP fields? When I create a TIMESTAMP field it always, be default has on update attached which I do not want.
Shouldn't this be part of the properties of the field?
Thanks
on update CURRENT_TIMESTAMP
You need to specify CURRENT_TIMESTAMP as default value. If you don't want a default value then use 0.
This is the manual page that explains the feature:
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
This is the manual page that explains the feature:
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
So if I specify nothing it will default to on update CURRENT_TIMESTAMP?
I have always left it blank in the past which seems to auto change to CURRENT_TIMESTAMP.
If I try to specify this "CURRENT_TIMESTAMP" It still goes to on update when I export the Create SQL for each table.
I have always left it blank in the past which seems to auto change to CURRENT_TIMESTAMP.
If I try to specify this "CURRENT_TIMESTAMP" It still goes to on update when I export the Create SQL for each table.
If I try to specify this "CURRENT_TIMESTAMP" It still goes to on update when I export the Create SQL for each table.
Works for me (MySQL Server 4.1.13) with both HeidiSQL 3.20 and latest snapshot.
CREATE TABLE `foo` (
`id` int(10) unsigned NOT NULL auto_increment,
`bar` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
Do you get a different CREATE TABLE statement if you run this query?
SHOW CREATE TABLE your_table
Hi, I did that and it came up with this:
CREATE TABLE `products`
(
`PID` int(11) NOT NULL auto_increment,
`PCode` varchar(30) NOT NULL,
`PName` varchar(255) NOT NULL,
`PSDesc` text,
`PDesc` longtext NOT NULL,
`POrder` varchar(50) default '1',
`PImg` varchar(250) NOT NULL,
`PImgTN` varchar(250) NOT NULL,
`PImgWidth` varchar(50) default NULL,
`PImgHeight` varchar(50) default NULL,
`PWeight` double(6,3) NOT NULL,
`PPrice` double(11,4) NOT NULL,
`PTaxIsInclusive` varchar(3) NOT NULL default 'Yes',
`PTaxCode` varchar(10) NOT NULL default 'GST',
`PStock` double(12,3) NOT NULL default '0.000',
`PStockAlert` double(10,3) NOT NULL default '0.000',
`POnline` varchar(50) default 'No',
`PLatestRelease` varchar(3) NOT NULL default 'No',
`PHop` varchar(1) default 'N',
`PGrain` varchar(1) default 'N',
`OLDPID` varchar(10) NOT NULL,
`PDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`PMYOBChangeControl` varchar(10) NOT NULL,
`POnHold` varchar(1) NOT NULL default 'N',
`display_order` varchar(50) default '0000000001',
`display_order_online` varchar(1) default 'N',
`product_view_type` varchar(1) default 'P',
PRIMARY KEY (`PID`),
UNIQUE KEY `PID` (`PID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
I also took a screenshot of the table properties in Heidi.
CREATE TABLE `products`
(
`PID` int(11) NOT NULL auto_increment,
`PCode` varchar(30) NOT NULL,
`PName` varchar(255) NOT NULL,
`PSDesc` text,
`PDesc` longtext NOT NULL,
`POrder` varchar(50) default '1',
`PImg` varchar(250) NOT NULL,
`PImgTN` varchar(250) NOT NULL,
`PImgWidth` varchar(50) default NULL,
`PImgHeight` varchar(50) default NULL,
`PWeight` double(6,3) NOT NULL,
`PPrice` double(11,4) NOT NULL,
`PTaxIsInclusive` varchar(3) NOT NULL default 'Yes',
`PTaxCode` varchar(10) NOT NULL default 'GST',
`PStock` double(12,3) NOT NULL default '0.000',
`PStockAlert` double(10,3) NOT NULL default '0.000',
`POnline` varchar(50) default 'No',
`PLatestRelease` varchar(3) NOT NULL default 'No',
`PHop` varchar(1) default 'N',
`PGrain` varchar(1) default 'N',
`OLDPID` varchar(10) NOT NULL,
`PDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`PMYOBChangeControl` varchar(10) NOT NULL,
`POnHold` varchar(1) NOT NULL default 'N',
`display_order` varchar(50) default '0000000001',
`display_order_online` varchar(1) default 'N',
`product_view_type` varchar(1) default 'P',
PRIMARY KEY (`PID`),
UNIQUE KEY `PID` (`PID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
I also took a screenshot of the table properties in Heidi.
I just had the same issue, where I manually created a table and left the Default value blank. After executing a query, the Default value automatically changed itself to CURRENT_TIMESTAMP, and it went to NOT NULL.
All I did was re-open the table and cleared the Default and un-checked the not null box so it would accept null.
Once I did this, I re-executed a few more queries and it didn't go back to a default of CURRENT_TIMESTAMP.
I have no idea why I had to tell it again to do what I wanted, but it 'seemed' to work.
Now, I of course have an alternate solution, which works perfectly for only timestamping when you want. Instead of making it a TIMESTAMP field, make it a VARCHAR field with the default length of 50. Presto. For example:
And there you have it. CURRENT_TIMESTAMP used in a VARCHAR field is a wonderful thing. :)
All I did was re-open the table and cleared the Default and un-checked the not null box so it would accept null.
Once I did this, I re-executed a few more queries and it didn't go back to a default of CURRENT_TIMESTAMP.
I have no idea why I had to tell it again to do what I wanted, but it 'seemed' to work.
Now, I of course have an alternate solution, which works perfectly for only timestamping when you want. Instead of making it a TIMESTAMP field, make it a VARCHAR field with the default length of 50. Presto. For example:
CREATE TABLE /*!32312 IF NOT EXISTS*/ `mytable` (
`capturedDate` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO myTable (capturedDate) VALUES (CURRENT_TIMESTAMP);
And there you have it. CURRENT_TIMESTAMP used in a VARCHAR field is a wonderful thing. :)
Please login to leave a reply, or register at first.