我有这个MySQL表:
CREATE TABLE web_media_com (
web_media_id INT AUTO_INCREMENT PRIMARY KEY,
web_media_position INT(12),
web_media_headline VARCHAR(76),
web_media_description VARCHAR(680)
);
我想在一个INSERT INTO查询中将主id添加到"web_media_position"中。因此,当"web_media_id"得到id 234时,"web_mmedia_position"应该得到相同的234数字。
现在我有这个:
$mysql_query = "INSERT INTO `web_media_com` (web_media_position, web_media_headline, web_media_description) VALUES
(LAST_INSERT_ID(), '".$web_media_headline."', '".$web_media_description."')";
但这并不奏效。有人能告诉我怎么做吗?谢谢
使用事务来防止并发插入之间的冲突。
在直接(非PHP)sql中,它看起来像:
START TRANSACTION;
INSERT INTO web_media_com(columns)....
SET @entry_id = LAST_INSERT_ID();
UPDATE web_media_com set web_media_position = @entry_id
where web_media_id = @entry_id;
COMMIT;
(注意:我没有尝试这是一个小提琴。YMMV…)
最适合您的解决方案
$mysql_query =
"INSERT INTO web_media_com
(web_media_position, web_media_headline, web_media_description)
select MAX('id')+1,'".$web_media_headline."','".$web_media_description."'
from web_media_com";