如何在MySQL中设置自增主键？并为journal表新增自增键

DevOps 小满

2026-5-26

Hey there! Let's break down how to add an auto-increment key to your journal table, plus cover the basics of setting up auto-increment primary keys in MySQL.

Adding an Auto-Increment Key to Your Existing `journal` Table

You've got a composite primary key already, so you have two options depending on whether you want to keep that composite key or replace it with an auto-increment primary key.

Option 1: Replace the Composite Primary Key with an Auto-Increment Primary Key

If you prefer a single auto-increment ID as the primary key, first drop the existing composite key, then add the new auto-increment column:

-- Drop the existing composite primary key
ALTER TABLE `journal` DROP PRIMARY KEY;

-- Add an auto-increment ID column and set it as the primary key
ALTER TABLE `journal` ADD COLUMN `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

The FIRST keyword places the id column at the start of your table (optional, but common practice for ID columns).

Option 2: Keep the Composite Primary Key, Add an Auto-Increment Unique Key

If you want to retain your original composite primary key but still have an auto-increment identifier, add the column as a unique key instead:

ALTER TABLE `journal` ADD COLUMN `id` INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE FIRST;

This ensures the id is unique and auto-generated, while your composite key remains the primary identifier for the table.

General Guide to Setting Auto-Increment Primary Keys in MySQL

Auto-increment keys are super useful for creating unique, sequential identifiers for rows. Here's how to work with them:

1. When Creating a New Table

You can define the auto-increment primary key directly in the CREATE TABLE statement:

CREATE TABLE `new_table` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- Use BIGINT for larger datasets
  `column1` VARCHAR(50) NOT NULL,
  `column2` INT DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Or shorthand it by combining the AUTO_INCREMENT and PRIMARY KEY clauses:

CREATE TABLE `new_table` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `column1` VARCHAR(50) NOT NULL,
  `column2` INT DEFAULT NULL
);

Key Notes for Auto-Increment Columns

Data Type: Always use integer types (INT, BIGINT) — UNSIGNED is recommended to avoid negative values and double the available range.
Single Auto-Increment Column: A table can only have one auto-increment column, and it must be part of a primary key or unique constraint (MySQL needs a unique way to track the next value).
Auto-Generation: When inserting rows, you don't need to specify a value for the auto-increment column — MySQL will handle it automatically. If you do specify a value, it must be unique (and will reset the auto-increment counter if it's higher than the current max).
Resetting the Counter: If you need to reset the auto-increment value (e.g., after deleting rows), use:
```
ALTER TABLE `your_table` AUTO_INCREMENT = 1;
```
Warning: Only do this if the table is empty or you're sure there won't be duplicate value conflicts.

内容的提问来源于stack exchange，提问作者Sangameshwar Kanugula