Things you might not know about Insert in MySQL

Anyone who’s worked with MySQL databases in their code has run into the INSERT statement. Turns out not everyone, even experienced developers, knows how to use its full functionality. Using two common tasks as examples, I want to walk you through some of the finer points of insert.

Task 1.

Need to build a visit counter by IP address. If the IP isn’t in the table yet, add it; if it is - bump the page view count. The table structure:

CREATE TABLE IF NOT EXISTS 'statTable' (
'ip' varchar(15) NOT NULL,
'visits' int(11) unsigned NOT NULL,
UNIQUE KEY 'ip' ('ip')
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Most developers solve this like so:

$obj=$ourMysqli->query("select ip from statTable where ip='$ip'");
If ($obj->num_rows)
    $ourMysqli ->query("update statTable set visits=visits+1 where ip='$ip'");
else
   $ourMysqli ->query("insert into statTable (ip,visits) values('$ip',1)");

If you skip the check for an existing row and just run Insert, you get a "duplicate value in a UNIQUE index or PRIMARY KEY" error, and update without a prior existence check can mean the new row never gets created at all.

The ON DUPLICATE KEY UPDATE construct solves this in a single query. Use it like this:

$ourMysqli ->query("insert into statTable (ip,visits) values('$ip',1) on duplicate key update visits=visits+1");

MySQL runs the operations in sequence. First it tries to insert a new row. If a field with an auto-increment or unique index already exists, it runs the update instead. Update statTable set visits=visits+1 where ip='$ip'

This construct has two gotchas. Let’s go through them.

Quirk 1:
Change the table structure like this:

CREATE TABLE IF NOT EXISTS `ipstat` (
  `ip` varchar(15) NOT NULL,
  `ref` varchar(100) NOT NULL,
  `visits` int(11) unsigned NOT NULL,
  PRIMARY KEY (`ref`),
  UNIQUE KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Now the table has two unique indexes, ip and ref. Running this query

$ourMysqli ->query("insert into statTable (ip,visits,ref) values('$ip',1,'$ref') on duplicate key update visits=visits+1");

turns into this update:

update statTable set visits=visits+1 where ip='$ip' or ref='$ref' limit 1

Note that only the first row matching ip='$ip' or ref='$ref' gets updated.

Quirk 2:

The table looks like this:

CREATE TABLE IF NOT EXISTS `statTable` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(15) NOT NULL,
  `visits` int(11) unsigned NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ip` (`ip`),
  KEY `Id` (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1;

On top of the unique IP index we’ve also added a primary key, an auto-increment Id. The query stays the same.

$ourMysqli ->query("insert into statTable (ip,visits) values('$ip',1) on duplicate key update visits=visits+1");

Run this query from the console and you’ll get a message saying two rows were changed, not one.

The reason for this seemingly odd MySQL behavior is simple. It first tries to run the Insert and bumps auto_increment by one ahead of time. Since the insert fails, it runs the update instead, but the auto-increment counter is already incremented by one.

This is especially bad on a high-load project, since the auto-increment values get burned through twice as fast.

Task 2.

Need to add a row to a table with a UNIQUE or PRIMARY KEY index, only if a row with that unique value doesn’t already exist.

The usual approach looks like this:

$obj=$ourMysqli->query("select ip from statTable where ip='$ip'");
If (!$obj->num_rows)
   $ourMysqli ->query("insert into statTable set ip='$ip'");

If you don’t check for an existing row with that unique value beforehand, the script execution stops, since Insert throws a duplicate value in a UNIQUE index or PRIMARY KEY error.

The INSERT IGNORE construct solves that. Ends up looking like this:

$ourMysqli ->query("insert ignore into statTable set ip='$ip'");

MySQL handles it all for us 🙂

Original