CODEWITHSUNDEEP
mysqlprimary-key
Will Warren
Will Warren

Reputation: 1294

Can one obtain the value being inserted with an auto increment pk?

When inserting a row into a table with an auto increment primary key column, is there a way to get the value that is going to be assigned to that row?

To be clear, I want to use this value as a part of a string for a different column on the same row.

Doing MAX(id) + 1 doesn't seem robust enough. And doing the insert and then an update with LAST_INSERT_ID() is bad because that's 2 separate db calls.

Thanks

EDIT:

What does everyone think of this:

INSERT INTO `mydatabase`.`mytable` (`name`, `description`) 
VALUES
  (
    CONCAT(
      'name-',
      CAST(
        (SELECT 
          `auto_increment` + 1 
        FROM
          `information_schema`.`TABLES` 
        WHERE `TABLE_SCHEMA` = 'mydatabase' 
          AND `TABLE_NAME` = 'mytable') AS CHAR
      )
    ),
    'description of this thing'
  ) ;

This way, you should end up with a row with id of 5 for example, and a name of "name-5". This is kind of a messy way to go about it, but it should work, no?

Thoughts?

Upvotes: 0

Views: 79

Answers (3)

Jared Mark
Jared Mark

Reputation: 156

You should look in to the SQL command "SHOW TABLE STATUS"

Try it, and google further to find out how to pull the "auto_increment" value out of it.

Upvotes: 1

Jé Queue
Jé Queue

Reputation: 10637

Ain't that bad. If you're not generating the key externally, then no. You could put a trigger on the table and make that a non auto increment and then generate the key yourself otherwise.

Upvotes: 0

user319198
user319198

Reputation:

You can't do with auto_increment field. Instead you can manually generate UID (unique primary key) and use it for this purpose.

Upvotes: 1

Related Questions