A lot of developers use the MySQL client’s affected rows count to determine how many rows the last executed statement affected (update, insert, delete). This is especially handy for updates, and specifically, updates where you want to make sure the data you are updating is the data you had in your “hand” before.
Say, you have a table like below:
create table a (id int(11) auto_increment, name varchar(100) null, primary key (id));
In your application you present a record to your user Joe who wants to edit the name of the selected record. User Jim also accesses the same record before Joe saves. User Jim actually saves the record first. Now, Joe has no way of knowing that Jim made changes and Joe’s changes are going to overwrite Jim’s. If in your application you wanted Joe to be aware of Jim’s changes prior to saving/confirming his, you’d run an SQL like:
update a set name = 'Edited by Joe' where id = 15 and name = 'Original Value';
This way, the affected row count on this update statement is going to return 0, and your application can let Joe know the value had changed while Joe was looking at his screen, and confirm to overwrite the new value set by Jim.
The above works fine for updates, but what about inserts? Say, you have the same table `a` as above. As you can see, the name column is not unique and you may have several records with the same name. However, consider that in some cases – i.e. not globally – you’d like to enforce that if there is an entry with the given name, the application should use that, instead of creating a new record.
So, naturally, you would need to first run the select from the table:
select count(*) from a where name = 'New Unique Record';
Then, if this returned 0, you’d run an insert that would create the new record.
This would work as long as you didn’t have any concurrent processes or threads executing this same code. If you had, say 2 concurrent processes, you would end up with 2 records since they’d both execute the select statement first, finding 0 records; then they’d both execute the inserts.
So, then you would think of ways to serialize the process, probably using transactions or lock the table, depending whether you are using InnoDB or MyISAM, introducing another level of complexity. You could be thinking that there must be a simpler way to do this, like with the update statements, you should be able to check whether the record existed, and only insert if it didn’t – all in a single SQL statement. Then know what happened with the affected row count.
Well, there is a way:
insert into a (name) select x.name from (select 'New Unique Record' as name) x left outer join a on x.name = a.name where a.name is null;
The above “insert … select …” format would only yield a row if the name match was not found in the `a` table; and you could use the affected row count of the insert to find out what happened.
