Mysql useful tips

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