Mysql useful tips
June 30th, 2006
No comments
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
