MySql:插入一行并获取内容 [英] MySql: Insert a row and get the content
本文介绍了MySql:插入一行并获取内容的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以插入一行并获取插入到同一查询中的值?
Is it possible to insert a row and get the values inserted in the same query?
类似...
INSERT INTO `items` (`item`, `number`, `state`)
(SELECT '3', `number`, `state` FROM `item_bug` WHERE `id`='3')
然后,获取ID并执行
SELECT * FROM `items` WHERE `id`='$id'
但是仅使用一个查询.
推荐答案
您可以调用存储过程,该过程将执行插入操作,并在一次调用中将结果集从应用程序层返回到mysql:
you can call a stored procedure which will perform the insert and return a resultset in a single call from your app layer to mysql:
存储过程调用
mysql> call insert_user('bar');
+---------+----------+
| user_id | username |
+---------+----------+
| 1 | bar |
+---------+----------+
1 row in set (0.02 sec)
$sqlCmd = sprintf("call insert_user('%s')", ...);
简单示例:
drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null
)
engine=innodb;
drop procedure if exists insert_user;
delimiter #
create procedure insert_user
(
in p_username varchar(32)
)
begin
declare v_user_id int unsigned default 0;
insert into users (username) values (p_username);
set v_user_id = last_insert_id();
-- do more stuff with v_user_id e.g. logs etc...
select * from users where user_id = v_user_id;
end#
delimiter ;
call insert_user('bar');
这篇关于MySql:插入一行并获取内容的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文