CODEWITHSUNDEEP
mysql
BeetleJuice
BeetleJuice

Reputation: 40946

MySQL: re-use auto-increment during insert

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

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?

Upvotes: 3

Views: 3465

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562951

Do I have to use 2 queries?

Yes. As you discovered, the id value hasn't been generated yet in a BEFORE INSERT trigger. But you can't change your NEW.thread value in an AFTER INSERT trigger.

You can't rely on reading the INFORMATION_SCHEMA, because you can cause a race condition.

You'll just have to do the INSERT, and then immediately execute:

UPDATE comments SET thread=id WHERE id=LAST_INSERT_ID() AND thread IS NULL;

If it's a root comment.

See also my past answers on the similar topic:

Upvotes: 3

BeetleJuice
BeetleJuice

Reputation: 40946

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

Upvotes: 0

Connor Gurney
Connor Gurney

Reputation: 627

You can simply set thread to NULL to signify that the comment is a root comment rather than attached to a thread.

Upvotes: -1

Related Questions