BeetleJuice BeetleJuice - 5 days ago 5
MySQL Question

MySQL: re-use auto-increment during insert

I'm designing a comments MySQL db, and my comments table has fields:


  • id
    primary key, auto-incr

  • thread
    int, not null

  • content



All replies to the same comment, in addition to that root comment must share the same
thread
. This is simple when a user is replying to a comment but what about when a new root comment is posted? I figured I would set
thread=id
for root comments.

Problem is, I don't know how to write a query that will reuse the just created
id
value within the same query when filling
thread
. Is this even possible?

I've tried

INSERT INTO `comments`
VALUES (NULL, LAST_INSERT_ID(), 'hi there')


This gives me the id from the previous insert, not the current one. Do I have to use 2 queries?

Answer

Thanks to Michael's answer I started looking into triggers; basically event-handlers that run some code when something happens. Michael's trigger didn't work for me, but the following does:

USE `my_db_name`;
DELIMITER $$
CREATE TRIGGER comments_bi 
BEFORE INSERT ON `comments`
FOR EACH ROW
BEGIN
  DECLARE nextID INT DEFAULT 0;

  SELECT AUTO_INCREMENT INTO nextID FROM information_schema.tables
  WHERE table_name = 'comments' AND table_schema = DATABASE();

  IF NEW.`thread` IS NULL OR NEW.`thread` = 0 THEN
    SET NEW.`thread` = nextID;
  END IF;

END $$
DELIMITER ;

One big caveat: because this trigger requires access to the information_schema, only the root account could define it.

Thanks to this answer for inspiration