Create field from auto increment value on INSERT

Question

Is there a way to INSERT a row into a table which contains an AUTO_INCREMENT column, and use the resulting AUTO_INCREMENT value to construct another field in the same row, in a single query?

I know it can be done in two operations.

Our product uses a 16 character numeric account ID stored as CHAR(16). We like to store the account ID as a single field as it is referenced in many tables throughout our database(s), however it is made up of the following parts:

AccountType INT(4) UNSIGNED // never less than 1000
AccountNumber INT(10) UNSIGNED AUTO_INCREMENT PRIMARY
AccountSite INT(2) UNSIGNED

Which are concatenated in order to produce the 16 character accountID field. The AccountType value is never less than 1000.

I would like to be able to perform a single INSERT operation that produces the four fields within the same query, if possible.

you can call a stored proc with the 4 parameters, or use an `after` trigger (I don't do a lot of triggers, I guess the id is avail) — Drew, Jul 04 '16 at 01:55
This could be relevant http://stackoverflow.com/questions/5222044/column-calculated-from-another-column If you have 5.7, check the newer answers — , Jul 04 '16 at 01:57
The problem is that if he wants it as an FK it can't be used. But I know what ya mean. — Drew, Jul 04 '16 at 02:00

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

I can show this with a stored procedure. You may in the end only want 1 or 2 columns in the table. But I am showing more (assuming a text user friendly string too). That part is trivial, to shrink it down in column count.

Schema:

drop table if exists tX4;
create table tX4
(   AccountType INT(4) UNSIGNED not null, -- // never less than 1000
    AccountNumber INT(10) AUTO_INCREMENT PRIMARY key,
    AccountSite INT(2) UNSIGNED not null,
    col4 varchar(50) not null,
    weWantThis char(16) not null
);
-- truncate tX4; -- used for testing

Stored Procedure:

DROP PROCEDURE IF EXISTS ins_tX4;
DELIMITER $$
CREATE PROCEDURE ins_tX4
(    p_AccountType int,
     p_AccountSite int,
     p_col4 varchar(50)
)
BEGIN
    DECLARE theAI int;
    DECLARE sConcatFix char(16);

    START TRANSACTION;
    insert tX4(AccountType,AccountSite,col4,weWantThis) values (p_AccountType,p_AccountSite,p_col4,'');
    set theAI=last_insert_id();
    set sConcatFix=concat( lpad(p_AccountType,4,'0'), lpad(theAi,10,'0'),  lpad(p_AccountSite,2,'0')  );
    update tX4 set weWantThis=sConcatFix where AccountNumber=theAI;
    COMMIT;
END$$
DELIMITER ;

Test:

call ins_tX4(1,2,'cat');

select * from tX4;
+-------------+---------------+-------------+------+------------------+
| AccountType | AccountNumber | AccountSite | col4 | weWantThis       |
+-------------+---------------+-------------+------+------------------+
|           1 |             1 |           2 | cat  | 0001000000000102 |
+-------------+---------------+-------------+------+------------------+

Visualized pieces:

0001 0000000001 02

Which is AccountType, the AI, AccountSite (widths 4,10,2) respectively.

call ins_tX4(8765,42,'Sunday');

select * from tX4;
+-------------+---------------+-------------+--------+------------------+
| AccountType | AccountNumber | AccountSite | col4   | weWantThis       |
+-------------+---------------+-------------+--------+------------------+
|           1 |             1 |           2 | cat    | 0001000000000102 |
|        8765 |             2 |          42 | Sunday | 8765000000000242 |
+-------------+---------------+-------------+--------+------------------+

Column weWantThis could be used as the target for an FK from other tables.

Manual page for LPAD() (Left Padding).

Edit

With an OUT parameter to send back the AI # assigned. Note that parameters are IN parameters by default.

Stored Procedure:

DROP PROCEDURE IF EXISTS ins_tX4;
DELIMITER $$
CREATE PROCEDURE ins_tX4
(   p_AccountType int,
    p_AccountSite int,
    p_col4 varchar(50),
    OUT AI_assigned int
)
BEGIN
    DECLARE theAI int;
    DECLARE sConcatFix char(16);

    -- AI means database-assigned AUTO INCREMENT
    set AI_assigned = -1; -- assume an Error condition
    START TRANSACTION;
        insert tX4(AccountType,AccountSite,col4,weWantThis) values (p_AccountType,p_AccountSite,p_col4,'');
        set theAI=last_insert_id();
        set sConcatFix=concat( lpad(p_AccountType,4,'0'), lpad(theAi,10,'0'),  lpad(p_AccountSite,2,'0')  );
        update tX4 set weWantThis=sConcatFix where AccountNumber=theAI;
        set AI_assigned = theAI; -- the OUT parameter is set to the AI value
    COMMIT;
END$$
DELIMITER ;

Test from mysql environment:

set @var1 = -1;

call ins_tX4(22,33,'Monday',@var1);
select @var1;
-- 3

call ins_tX4(333,79,'Tuesday',@var1);
select @var1;
-- 4

Test from a PHP environment:

Because this question is tagged PHP, see an answer like the one here from user Matteo Tassinari.

Thanks @Drew. What made you declare`sConcatFix` as `varchar(20)` instead of `varchar(16)` or `char(16)`? — khargoosh, Jul 04 '16 at 02:39
Years of `c` programming and paranoia. 16 is fine. It is just a local var to the proc anyway, not in the table. Yes, char(16) is fine for the table. — Drew, Jul 04 '16 at 02:42
I think it was the decade of programming in ms sql-server and the fixed space left debris that one would have to clean or trim. So everything is varchar's these days. But yes, yours is a char(16). Sorry about that confusion — Drew, Jul 04 '16 at 02:49
Can this be accomplished using a transaction? Take a look at the answer I've added. — khargoosh, Jul 04 '16 at 23:05
Sorry - not clear. I mean using a transaction written as a single query. — khargoosh, Jul 04 '16 at 23:07
I resolved this for you with a stored proc wrapper. It is pretty simple. A single call to the stored proc. Otherwise, look at all of the answers to this question ... http://stackoverflow.com/q/2708237 — Drew, Jul 04 '16 at 23:12

khargoosh · Answer 2 · 2016-07-05T00:24:09.950

This can be accomplished with a TRANSACTION.

START TRANSACTION; 
INSERT INTO accounts (accountType, accountSite) VALUES(1000,10); 
UPDATE accounts SET accountID = CONCAT(LPAD(accountType, 4, '0'), LPAD(accountNumber, 10, '0'), LPAD(accountSite, 2, '0')) WHERE accountNumber = LAST_INSERT_ID(); 
COMMIT;

Which could for example be executed in php as:

$mysqli = new mysqli(...);
$mysqli->begin_transaction();
$query = "INSERT INTO accounts (accountType, accountSite) VALUES(1000,10);"
$query .= "UPDATE accounts SET accountID = CONCAT(LPAD(accountType, 4, '0'), LPAD(accountNumber, 10, '0'), LPAD(accountSite, 2, '0')) WHERE accountNumber = LAST_INSERT_ID();"
$mysqli->query($query);
$mysqli->commit();

Create field from auto increment value on INSERT

2 Answers2

Edit