On Apr 2, 2009, at 12:51 AM, Steve Edberg wrote:
At 9:35 AM +0200 4/2/09, Andreas Pardeike wrote:
Hi,
I have a table 'test'
+---------+--------------+------+-----+-------------------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+---------+--------------+------+-----+-------------------
+----------------+
| id | int(11) | NO | PRI | NULL |
auto_increment |
| foo | varchar(255) | NO | | NULL
| |
| bar | varchar(255) | NO | | NULL
| |
| payload | longblob | YES | | NULL
| |
+---------+--------------+------+-----+-------------------
+----------------+
where 'payload' size usually is quite big!
Now I want to manipulate a number of rows in a non-trivial way and
at the
same time set 'id' for those rows to a new number in the sequence.
I.e.
UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo)
WHERE bar ...
My motivation is that this table has several consumers in other
applications
that keep track of the biggest 'id' they have seen between polls
and thus
can keep track of new and changed rows.
Right now, I solve this problem by copying the rows to a temporary
table,
then delete them and insert them from the temporary table but this
moves my
huge payload around which I really want to avoid.
How can I solve this in a better way?
/Andreas Pardeike
Add a column of type timestamp which, by default, will be updated
every time a record is inserted or updated. Then the other
applications can simply select records with timestamp >
last_poll_time.
My same suggestion as well. I go a far as to have at least, three
standard fields to any table I make:
CREATE TABLE IF NOT EXISTS `foobar` (
`id` int(11) NOT NULL auto_increment,
`udpated` timestamp NOT NULL default '0000-00-00 00:00:00' on
update CURRENT_TIMESTAMP,
`created` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
You never know when you want to know time and date, and this makes it
brainless. So any new record you add you will set "created = NOW();",
outside of that, never worry about the updated, field, it gets set
automatically any time there is any change to the record.
I am near certain, there is one small thing to note, which is for
example, if you "update foobar set something = 'test' where id = 1"
and something was already at "test", since no real update/change
happened the time-stamp is not going to get updated.
* There is a version of mysql 4, that going forward, had a change to
how `timestamp` was defined. If your old data is in version four, and
you plan to move to a new version, look out for that issue.
Thanks for "welcome" :)
--
Scott * If you contact me off list replace talklists@ with scott@ *
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
