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?

Answer 1

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