Using auto_increment values as part of compound UID's - efficient query methods?

Question

I have the following simple table:

Employees:

Name (VARCHAR), Increment (INT, auto increment), Uid (INT)

For example, the Uid may be composed of the Increment value concatenated with another identifier (e.g. mac address of the server).

Currently my node.js application (using node-mysql bindings) creates a new row to generate the auto increment value, then takes this value:

var first_payload = {
   name: "Example"
}
connection.query("INSERT INTO employees SET ?", first_payload, function(error, result){
  var auto_increment_value = result.insertId; //Capture auto increment value
});

Then I take the auto increment value, concatenate this with another identifier and insert back into the row as the Uid:

var second_payload = {
  Uid: concatenated_auto_increment,
}
connection.query("INSERT INTO employees SET ? WHERE Increment = '"+auto_increment_valiue+"' ", second_payload, function(error, result){
  //...
})

Is there a more efficient way of executing this operation?

Such as generate the auto increment value and then create the Uid within the same query? Or perhaps the initial query creating the auto increment could return a direct pointer to that row, to remove the need for the second query to search the table?

[There is](http://stackoverflow.com/questions/5972446/insert-autoincrement-into-second-column) a good example for such query. — aring, Nov 12 '16 at 20:12
@aring the page you've linked is not helpful -- its not specific to node-mysql and it fails to show how you would complete the operation I've described in my question in a single query, as opposed to two. — Babra Cunningham, Nov 13 '16 at 16:42
Just FYI, the line `connection.query("INSERT INTO employees SET ? WHERE Increment = '"+auto_increment_valiue+"' ", second_payload, function(error, result){` could be `connection.query("INSERT INTO employees SET ? WHERE Increment = ?", [ second_payload, auto_increment_valiue ], function(error, result){`. Also if you're looking for a way to do what you want in a single query, I don't think that's possible. And you might want to wrap those two SQL commands in a transaction, if your underlying engine supports it. You probably don't want to keep a half-finished row. — Dave, Nov 16 '16 at 20:17

score 2 · Accepted Answer · edited May 23 '17 at 11:45

Can your concatenated_auto_increment be calculated by the MySql server? If so, you could set up a trigger in the database to run when a new record is created.

CREATE TABLE employees (`name` varchar(255), `increment` int auto_increment primary key, `uid` int);

DELIMITER //
CREATE TRIGGER add_uid BEFORE INSERT ON employees FOR EACH ROW
BEGIN
   DECLARE next_id INT;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employees');
   SET NEW.uid = CONCAT(next_id, 39839483);
END;//
DELIMITER ;

Then when you call insert statements like this

INSERT INTO employees SET name = 'Dave';
INSERT INTO employees SET name = 'Joe';

MySQL will automatically fill in the uid field:

SELECT * FROM test.employees;
+------+-----------+-----------+
| name | increment | uid       |
+------+-----------+-----------+
| Dave |         1 | 139839483 |
| Joe  |         2 | 239839483 |
+------+-----------+-----------+
2 rows in set (0.00 sec)

Note: credit to Resegue for the trigger code

Using auto_increment values as part of compound UID's - efficient query methods?

1 Answers1