Can You Access the Auto Increment Value in MySQL Within One Statement

Can you access the auto increment value in MySQL within one statement?

there's no need to create another table, and max() will have problems acording to the auto_increment value of the table, do this:

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   DECLARE next_id;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
   SET NEW.field = next_id;
END

I declare the next_id variable because usually it will be used in some other way(*), but you could do straight new.field=(select ...)

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   SET NEW.field=(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
END

Also in cases of (SELECT string field) you can use CAST value;

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   SET NEW.field=CAST((SELECT aStringField FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl') AS UNSIGNED);
END

(*) To auto-name an image:

SET NEW.field = CONCAT('image_', next_id, '.gif');

(*) To create a hash:

SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );

Access Auto-Increment Value During INSERT INTO Statement

I would keep the id and imgname independent of each other and combine the two on SELECT when needed. If the need is frequent enough, create a view.

MySQL: re-use auto-increment during insert

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:

• Concatenating a string and primary key Id while inserting
• Two autoincrements columns or autoincrement and same value in other column

Get current AUTO_INCREMENT value for any table

You can get all of the table data by using this query:

SHOW TABLE STATUS FROM `DatabaseName` WHERE `name` LIKE 'TableName' ;

You can get exactly this information by using this query:

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';

MySQL: Auto increment temporary column in select statement

This will give you a consecutive row number with 3.

SELECT
    (@cnt := @cnt + 1) AS rowNumber,
    t.rowID
FROM myTable AS t
  CROSS JOIN (SELECT @cnt := 0) AS dummy
WHERE t.CategoryID = 1
ORDER BY t.rowID ;

Result

| ROWNUMBER | ROWID |
---------------------
|         1 |     1 |
|         2 |    25 |
|         3 |    33 |
|         4 |   150 |

MySQL can I insert specific ids into an auto-increment column

You can, just by setting the field when INSERTing. Autoincrement only works if no value is specified for the field.

Can I assume the highest auto increment value is always the newest entry?

Will SELECT MAX(ID) FROM ... always work?

No, it will not. If you have more than one copy of your program connected to your database, and one program does what you suggest, and another program then performs an insert, the first program's MAX(ID) value will become stale. It's a race condition. Those are very difficult to detect and debug, because they don't crop up until a system is under heavy load.

If all you care about is a snapshot of MAX(ID) you'll be OK. But you cannot use this MAX(ID) value as the value for the next insertion, unless you never will have more than one program instance inserting data. It's unwise to rely on that kind of never.

If not, is there a more reliable way to get the newest entry?

Immediately after you do an INSERT operation you can do SELECT LAST_INSERT_ID() to retrieve the autoincrementing ID just used. Various APIs, like mysqli, PDO, and JDBC return this ID to a calling program when an INSERT operation completes.

LAST_INSERT_ID() and the related API functions work on a connection-by-connection basis. Therefore, if multiple connections are inserting rows, each connection will get its own id values.

---

Related Topics