« FJAX: Ajax with Flash | Home | Playing with Ruby »
Mysql useful tips
By Michael Stepanov | June 30, 2006
I decided to put in one post several Mysql commands and tips which I used during this week.
1. Get a table structure
To get a table structure in a SQL it can be used a command
show create table <table name> \G
2. Get ID of last inserted record.
To get the ID of the last inserted record just use mysql built-in function LAST_INSERT_ID(). Perl geeks can get the ID from DBI connection property - mysql_insertid:
my $id = $dbh->{mysql_insertid};
3. Update summary counters.
To update some counters in a table you can do following:
- do UPDATE everytime but catch a case if the record is not existing and do INSERT in this case:
my $ret = $dbh->do('UPDATE counters SET hits=hits+1 WHERE IP="192.168.1.1"');
$ret = $dbh->do('INSERT INTO counters (hits, IP) VALUES(1, "192.168.1.1")) unless $ret; - use INSERT ON DUPLICATE KEY UPDATE (via MySQL Performance Blog):
my $ret = $dbh->do("INSERT INTO ipstat VALUES(inet_aton('192.168.0.1'),1,now()) ON duplicate KEY UPDATE hits=hits+1");
Note: IP should be a primary key in this case
RSS feed for comments on this post.