php - 如何在MySQL查询中对同一张表同时执行插入和选择操作？

Question

我的数据库中有一个名为“questions”的表。为了您的引用，我在下面指定了表“问题”的结构:

question_id                   bigint(12) AUTO_INCREMENT (Primary Key)
question_parent_id            bigint(12)
question_subject_id           smallint(11)
question_topic_id             int(11)
question_directions           text
question_text                 text
question_file                 varchar(100)
question_description          text
question_difficulty_type      tinyint(4)
question_has_sub_ques         enum('0', '1')
question_picked_individually  enum('no', 'yes')
question_appeared_count       bigint(12)
question_manual               enum('0', '1')
question_site_id              varchar(10)
question_created_staff_id     varchar(32)
question_added_date           bigint(12)
question_updated_staff_id     varchar(32)
question_updated_date         bigint(12)

此表包含数千个问题。现在的场景是我从 PHP 表单中获取一些值，如下所示:

/*Following are the subject id and topic id from which I want to fetch the questions belonging to that subjet id and topic id */    
    $_POST['from_subject_id'] => 8
    $_POST['from_topic_id'] => 545
/*Following are the subject id and topic id to which I want to add the questions fetched in above query*/
    $_POST['to_subject_id'] => 8
    $_POST['to_topic_id'] => 547

所以我想添加根据 subject_id 和 topic_id 的前两个值获取的问题(即基于 $_POST['from_subject_id'] 和 $_POST['from_topic_id'] ) 到同一个表。但是所有这些新插入的问题都应该在下一条评论之后给出 subject_id 和 topic_id 值(即 $_POST['to_subject_id'] 和 $_POST['to_topic_id'])。简而言之，我想在同一张表上同时执行插入和选择操作。为了实现这一点，我尝试了很多技巧，并在谷歌上搜索了解决方案，但未能找到完美的解决方案。任何人都可以在这方面帮助我吗？我尝试使用以下 SQL 查询，但它插入了相同的问题，其中包含它们已有的主题和主题 ID 值。简而言之，问题不断重复，我不希望出现这样的结果。相反，我希望使用新的 subject_id 和新的 topic_id 插入相同的问题。为了您的引用，我在下面给出了 SQL 查询:

INSERT INTO questions (question_parent_id, question_subject_id, question_topic_id, question_directions, question_text, question_file, question_description, question_difficulty_type, question_has_sub_ques, question_picked_individually, question_manual, question_site_id, question_created_staff_id, question_added_date, question_appeared_count, question_updated_staff_id, question_updated_date)
SELECT question_parent_id, question_directions, question_text, question_file, question_description, question_difficulty_type, question_has_sub_ques, question_picked_individually, question_manual, question_site_id, question_created_staff_id, question_added_date, question_appeared_count, question_updated_staff_id, question_updated_date
FROM questions
WHERE question_subject_id='8' AND question_topic_id='545'

非常感谢

Answer 1

将您想要的值替换到查询的 select 部分:

INSERT INTO questions (question_parent_id, question_subject_id, question_topic_id, question_directions, question_text, question_file, question_description, question_difficulty_type, question_has_sub_ques, question_picked_individually, question_manual, question_site_id, question_created_staff_id, question_added_date, question_appeared_count, question_updated_staff_id, question_updated_date)
    SELECT question_parent_id,  8, 547,
           question_directions, question_text, question_file,
           question_description, question_difficulty_type, question_has_sub_ques,
           question_picked_individually, question_manual, question_site_id,
           question_created_staff_id, question_added_date, question_appeared_count,
           question_updated_staff_id, question_updated_date
FROM questions
WHERE question_subject_id = '8' AND question_topic_id = '545';

或者，select 可能会开始:

select question_parent_id, $_POST['to_subject_id'], $_POST['to_topic_id']