Learn / SQL Server → MySQL / Auto-Increment & Keys
Lesson 3 of 5

Auto-Increment & Keys

Every table needs a way to generate its own primary key values, and both engines automate it — but the keyword, the function you call afterward to retrieve the value you just inserted, and the scoping rules around that function are all different enough to trip up a direct port.

💡 The Bottom Line

IDENTITY(1,1) becomes AUTO_INCREMENT, and SCOPE_IDENTITY() becomes LAST_INSERT_ID(). The biggest behavioral gap isn't the keyword — it's that SCOPE_IDENTITY() is scoped per-batch/per-caller (it won't pick up an ID generated by a trigger in a different scope), while LAST_INSERT_ID() is scoped per-connection: it always returns the last auto-increment value generated by statements on that same connection, regardless of scope, which means concurrent connections never see each other's values but a trigger's insert on the same connection can shadow the one you meant to read.

Declaring an auto-incrementing primary key

SQL Server (T-SQL)
CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL
);
MySQL
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100)
        CHARACTER SET utf8mb4 NOT NULL
);

📋 Key differences

  • IDENTITY(seed, increment) lets you set both the starting value and the step size inline; MySQL's AUTO_INCREMENT takes no arguments in the column definition — you set the starting value afterward with ALTER TABLE orders AUTO_INCREMENT = 1000;, and the increment is a server-wide setting (auto_increment_increment), not a per-column one.
  • SQL Server allows exactly one IDENTITY column per table, and it doesn't have to be the primary key (though it usually is); MySQL's AUTO_INCREMENT column must be indexed — almost always as the primary key or at least part of one.
  • Both engines reject explicit inserts into the auto-generated column unless you override the behavior — SQL Server needs SET IDENTITY_INSERT Orders ON; MySQL just lets you insert an explicit value directly (including 0 or NULL to still trigger auto-generation), no special mode required.

Retrieving the value you just inserted

SQL Server (T-SQL)
INSERT INTO Orders (CustomerName)
VALUES ('Ada Lovelace');

SELECT SCOPE_IDENTITY();
MySQL
INSERT INTO orders (customer_name)
VALUES ('Ada Lovelace');

SELECT LAST_INSERT_ID();

⚠️ Gotcha

Don't reach for SQL Server's other identity functions out of habit. @@IDENTITY returns the last identity value generated in the current session, across any scope — including a value generated by a trigger — which is exactly the bug SCOPE_IDENTITY() exists to avoid. MySQL only has the one function, LAST_INSERT_ID(), and it behaves like @@IDENTITY (connection-wide, not scope-limited): if an AFTER INSERT trigger on the same connection inserts into another auto-increment table, a subsequent LAST_INSERT_ID() call can return that table's value instead of the one you expected. When this matters, capture the ID immediately after the triggering statement, before anything else runs on that connection.

Composite and non-integer keys

SQL Server and MySQL both support composite primary keys (multiple columns together) and both let a primary key be a non-integer type like UNIQUEIDENTIFIER/CHAR(36) for GUID-based keys — but only one column in a MySQL table may ever carry AUTO_INCREMENT, and it must be part of a key. A common pattern ported from SQL Server where an IDENTITY column is just a convenience surrogate alongside a separately-enforced natural key works the same way in MySQL: keep the AUTO_INCREMENT INT as the primary key, and add a separate UNIQUE index for the natural key (e.g. an order number or email).

🔧 Try It Yourself

Write a MySQL INSERT into a table with an AUTO_INCREMENT primary key, followed immediately by SELECT LAST_INSERT_ID(); in the same session. Then, in a second connection (a second terminal or client tab), insert a row into the same table and call LAST_INSERT_ID() there too — confirm each connection only ever sees its own most recent value, never the other connection's.

🙋 There Are No Dumb Questions

Q: If two people insert into the same MySQL table at the same time, can LAST_INSERT_ID() return the wrong row's ID?
A: No — that's exactly the problem LAST_INSERT_ID() is designed to avoid. It's scoped to the current connection/session, not to the table, so concurrent connections each get their own private view of "the last value I generated," even though the underlying AUTO_INCREMENT counter is shared and strictly increasing across the whole table. The danger case isn't concurrency across connections — it's a trigger firing on the same connection and quietly changing what "last" means before you read it.

✅ Quick Check

Why is LAST_INSERT_ID() generally safe to use even with many concurrent connections inserting into the same table?

Next up: schemas and keys are in place — time to look at the procedural layer, where CREATE PROCEDURE syntax and variable declarations diverge more than almost anything else between the two engines.

← Back to
Next →