There is a table:

  1. id - auto_increment
  2. body
  3. thread

There is a query to the database

INSERT INTO `post` SET `body` = '123', `thread` = 'id'

It would be necessary for example in the thread field to have the same value as auto_increment id
Is it possible without a second request and without php?

  • I'm afraid to just do a trigger, which if thread is not set will equate it to ID (NEW.thread = NEW.id) - Mike
  • @Mike, so I did not succeed: (after the insert is impossible, but before - it is unknown - splash58
  • I even tried to do it through “one place” - ru.stackoverflow.com/questions/502875/… - it still didn't work out :) As a result, in the null field I return id - splash58
  • @ splash58 And in MySQL, you can trigger an update on the same table to which the trigger is triggering AFTER FOR EACH ROW. Well, let the column type be NULL and in the trigger give the update tabX set thread=id where id=NEW.id and thread is null - Mike
  • I'm afraid to lie, but in my opinion the same table should not be touched. I had to try exactly that option - splash58

2 answers 2

The AUTO_INCREMENT mechanism has a restriction; you can only provide one column of the table with it and you have it assigned to the id column. To duplicate a value, the easiest way is to use a trigger:

 CREATE TRIGGER id_to_thread BEFORE INSERT ON post FOR EACH ROW BEGIN SET @id := (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'post' AND TABLE_SCHEMA=DATABASE() ); SET NEW.thread = @id; END//

In the trigger, the information scheme determines which value will be assigned to the id id by the AUTO_INCREMENT mechanism. The resulting value is assigned to the thread field before inserting an entry into the post table.

In order for the analyzer to separate the end of the request from the semicolons in the body of the trigger, you need to change the sign of the end of the request (for example, to //). In the mysql console, a special DELIMITER command is used for this.

 DELIMITER //

Most other mysql clients also have the ability to flag the end of a query.

    Use mysql LAST_INSERT_ID. Something like this:

     INSERT INTO `post` (`id`, `body`) VALUES (NULL, '123'); SET @lastID := LAST_INSERT_ID(); UPDATE `post` SET `thread`=@lastID WHERE `id`=@lastID;
    • And how will work on duplicate key when creating a known new entry? (in case I don’t understand something, I checked your request, it inserted NULL into the thread) - Mike
    • dev.mysql.com/doc/refman/5.7/en/ ... LAST_INSERT_ID (). - Dmitry Kalinin
    • And? What is written there? last_insert_id () returns the ID of the last successful query, but unfortunately after the insert has been executed. those. A second request is needed to use this function. And on duplicate there is nothing to do with it, it will only work if the record that they are trying to insert already exists - Mike
    • LAST_INSERT_ID save to variable and do +1 - Dmitry Kalinin
    • @DmitryKalinin no one promises that the next one will be +1 - splash58