List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:April 2 2009 7:51am
Subject:Re: Auto increment?
View as plain text  
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.

	steve

-- 
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg                                http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center                            sbedberg@stripped |
| Bioinformatics programming/database/sysadmin             (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
Thread
Auto increment?Andreas Pardeike2 Apr
  • Re: Auto increment?Steve Edberg2 Apr
    • Re: Auto increment?Scott Haneda2 Apr
      • Re: Auto increment?Andreas Pardeike2 Apr
  • Re: Auto increment?Scott Haneda3 Apr