Keeping a running tally in MySQL

Linc

I have a database table in which I'm storing jobs for multiple companies. The job # needs to increment per company, which means I can't just use an auto-incrementing key. (Each company will have a job 1, 2, 3, 4, etc)

I somehow need to make sure it correctly increments +1 for each job assigned to the same company, even if multiple people are inputting jobs at the same time. Anyone know how can I accurately keep track of a running tally like that?

Kwitko

I think you're going to have to do something on the programming (stored procedure or function) side.

nsanity

If you are running MySQL 5+ just do something like this:

SELECT MAX(job_id)+1 FROM `job` WHERE `company` = 'x';

That will return the next ID for that company... you could also write a stored function.

Linc

Thanks guys. nsanity, that did the trick perfectly, cheers!

nsanity

Sorry. I must scratch this itch. I don't get nearly enough times when I can actually use these, but another possibility is the stored function:

DELIMITER %
create function next_id_cat(category varchar(255)) RETURNS int
BEGIN
  DECLARE a1 INT DEFAULT 0;
  SELECT MAX(job_id)+1 INTO a1 FROM `job` WHERE `company` = category;
  RETURN IFNULL(a1,0);
END%
DELIMITER ;

Using a function like this will get you this:

root@localhost) [test]> select next_id_cat('hr');
+-------------------+
| next_id_cat('hr') |
+-------------------+
|                 0 | 
+-------------------+
1 row in set (0.00 sec)

(root@localhost) [test]> insert into job VALUES ('hr',next_id_cat('hr'),4);
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> insert into job VALUES ('hr',next_id_cat('hr'),4);
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> insert into job VALUES ('hr',next_id_cat('hr'),4);
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> insert into job VALUES ('hr',next_id_cat('hr'),4);
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> insert into job VALUES ('hr',next_id_cat('hr'),4);
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> select * from job;
+---------+--------+------+
| company | job_id | iid  |
+---------+--------+------+
| hr      |      0 |    4 | 
| hr      |      1 |    4 | 
| hr      |      2 |    4 | 
| hr      |      3 |    4 | 
| hr      |      4 |    4 | 
+---------+--------+------+
5 rows in set (0.00 sec)

It's a lot "cleaner" and makes it so you don't have to call 2 separate queries in your code.

Khaos

nsanity wrote:

It's a lot "cleaner" and makes it so you don't have to call 2 separate queries in your code.

It is indeed cleaner, but probably overkill in this case.

1. You still execute two queries. One is just executed in the stored procedure.

2. This can simply be written as a sub-query.

For example...

INSERT INTO jobs COLS(..., job_id), VALS(..., (SELECT NextJobID=MAX(job_id)+1 FROM jobs WHERE company='%company%'))

This accomplishes the exact same thing without the need to maintain a stored procedure. Stored procedures are fine and dandy, but there is a trade-off between code maintainability and cleanliness.

As a general rule, stored procedures should only be used when they are actually functional in nature and will be used across multiple queries.

Since here you only have one use-case for this sub-query, abstracting it to a stored procedure introduces too much maintenance overhead to justify.

Also note that it is actually slower to call a stored procedure than it is to execute a trivial sub-query such as this. Function call overhead > trivial sub-query overhead.