是否有办法将INSERT
一行放入包含AUTO_INCREMENT
列的表中,并使用所得AUTO_INCREMENT
值在同一行中构建另一个字段,在单个查询中?
我知道这可以通过两次操作来完成。
我们的产品使用存储为CHAR(16)
的16个字符数字帐户ID。我们喜欢将帐户ID存储为单个字段,因为它在数据库中的许多表中被引用,但是它由以下部分组成:
AccountType INT(4) UNSIGNED // never less than 1000
AccountNumber INT(10) UNSIGNED AUTO_INCREMENT PRIMARY
AccountSite INT(2) UNSIGNED
它们被连接起来以产生16个字符的accountID字段。AccountType的值不能小于1000。
如果可能的话,我希望能够执行单个INSERT
操作,在同一查询中产生四个字段。
我可以用一个存储过程来展示这一点。您最终可能只需要表中的1或2列。但我显示更多(假设一个文本用户友好的字符串)。这部分是微不足道的,缩小它的列数
模式:
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
存储过程:
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 ;
测试:
call ins_tX4(1,2,'cat');
select * from tX4;
+-------------+---------------+-------------+------+------------------+
| AccountType | AccountNumber | AccountSite | col4 | weWantThis |
+-------------+---------------+-------------+------+------------------+
| 1 | 1 | 2 | cat | 0001000000000102 |
+-------------+---------------+-------------+------+------------------+
可视化作品:
0001 0000000001 02
分别是AccountType、AI、AccountSite(宽度为4、10、2)
call ins_tX4(8765,42,'Sunday');
select * from tX4;
+-------------+---------------+-------------+--------+------------------+
| AccountType | AccountNumber | AccountSite | col4 | weWantThis |
+-------------+---------------+-------------+--------+------------------+
| 1 | 1 | 2 | cat | 0001000000000102 |
| 8765 | 2 | 42 | Sunday | 8765000000000242 |
+-------------+---------------+-------------+--------+------------------+
列weWantThis
可以用作其他表中FK的目标。
LPAD()
手册页(左距)。
编辑
使用OUT
参数发送回分配的AI #。注意,参数默认为IN
参数。
:
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 ;
测试mysql环境:
set @var1 = -1;
call ins_tX4(22,33,'Monday',@var1);
select @var1;
-- 3
call ins_tX4(333,79,'Tuesday',@var1);
select @var1;
-- 4
从PHP环境中测试:
因为这个问题被标记为PHP
,所以请参见用户Matteo Tassinari的回答。
这可以通过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;
例如,在php中可以这样执行:
$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();