AUTO_INCREMENT

-- ===== Basic =====
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(254) UNIQUE NOT NULL
) ENGINE=InnoDB;

INSERT INTO users (email) VALUES ('a@x.io');
-- id auto-assigned (1)

-- ===== Choose BIGINT for new tables =====
-- INT UNSIGNED maxes at ~4.3 billion. BIGINT UNSIGNED at 18 quintillion.
-- BIGINT is 8 bytes vs INT's 4 — for a primary key, it is worth the extra bytes.

-- ===== Reading the last-inserted ID =====
INSERT INTO users (email) VALUES ('b@x.io');
SELECT LAST_INSERT_ID();        -- 2 (per-session)

-- In Node mysql2:
const [r] = await conn.execute('INSERT INTO users (email) VALUES (?)', [email]);
console.log(r.insertId);

-- ===== Setting / resetting =====
ALTER TABLE users AUTO_INCREMENT = 1000;
-- The next insert uses id = 1000 (or the next free above that).
-- You CANNOT set it lower than the current MAX(id); the engine bumps it.

-- ===== Gaps are normal =====
-- AUTO_INCREMENT is not gap-free:
-- - ROLLBACK consumed an id
-- - REPLACE INTO consumed an id
-- - server crash + restart can skip
-- - InnoDB caches a range per connection (innodb_autoinc_lock_mode)
-- Build apps that DO NOT depend on contiguous ids.

-- ===== Multi-master / replication =====
-- For multi-write topologies, configure offsets to avoid collisions:
-- auto_increment_increment = N
-- auto_increment_offset = M
-- Or use UUIDs / Snowflake IDs.

-- ===== UUID / Snowflake alternatives =====
-- UUID v4 stored as BINARY(16) for compact + indexable:
CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), TRUE)),
  ...
);
-- Pros: distributed-friendly, no central authority.
-- Cons: larger keys, slightly less sequential locality.

-- Snowflake / ULID:
-- 64-bit time-ordered IDs; great for distributed systems.

-- ===== Composite keys =====
-- For many-to-many tables, prefer composite primary keys over a surrogate id:
CREATE TABLE post_tags (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
) ENGINE=InnoDB;

-- ===== Inspecting + diagnostics =====
SHOW CREATE TABLE users;
SELECT MAX(id), COUNT(*) FROM users;

-- ===== Patterns to internalise =====
-- - BIGINT UNSIGNED AUTO_INCREMENT for almost every new table
-- - LAST_INSERT_ID per session for read-after-insert
-- - Tolerate gaps; never use AUTO_INCREMENT for business identifiers
-- - UUID / Snowflake when you need distributed-friendly IDs

-- ===== Pitfalls =====
-- - INT INT UNSIGNED running out at 4.3B (and you suddenly can not insert)
-- - Depending on consecutive IDs (gaps will appear)
-- - Resetting AUTO_INCREMENT to a value already used (engine bumps anyway)
-- - Using AUTO_INCREMENT in a multi-write topology without offsets

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    total DECIMAL(10,2)
);
ALTER TABLE orders AUTO_INCREMENT = 1000;