MySQL中能否创建带连字符的复合主键？

Great question! Let's break down your options here—there are a few cleaner ways to get that formatted primary key you want, without resorting to adding a redundant hyphen column.

MySQL 5.7 and later supports generated columns, which let you automatically create the formatted ID from your project and lesson IDs, and set that as your primary key. This keeps your data clean and avoids manual work.

Here's how you'd define the table:

CREATE TABLE lessons (
    project_id VARCHAR(10) NOT NULL,
    lesson_id INT NOT NULL,
    formatted_id VARCHAR(20) GENERATED ALWAYS AS (CONCAT(project_id, '-', lesson_id)) STORED PRIMARY KEY,
    -- Add your other columns here (lesson_content, date_added, etc.)
    UNIQUE KEY (project_id, lesson_id) -- Optional: Ensures no duplicate project-lesson pairs
);

• STORED means the value is physically stored in the table (required if you want to use it as a primary key in older MySQL versions; MySQL 8.0.13+ allows VIRTUAL generated columns as primary keys too).
• The UNIQUE constraint on (project_id, lesson_id) is optional but helpful to prevent duplicate entries for the same project-lesson pair.

When you insert data, you just provide project_id and lesson_id—the formatted_id is generated automatically:

INSERT INTO lessons (project_id, lesson_id, lesson_content)
VALUES ('PR12', 81, 'Always test database constraints before deploying');

This will create a row with formatted_id = 'PR12-81' as the primary key.

If you're using an older MySQL version that doesn't support generated columns, you can use a BEFORE INSERT trigger to populate a dedicated primary key column with the formatted string.

First, create the table:

CREATE TABLE lessons (
    formatted_id VARCHAR(20) PRIMARY KEY,
    project_id VARCHAR(10) NOT NULL,
    lesson_id INT NOT NULL,
    -- Other columns here
    UNIQUE KEY (project_id, lesson_id)
);

Then create the trigger:

DELIMITER //
CREATE TRIGGER generate_lesson_id BEFORE INSERT ON lessons
FOR EACH ROW
BEGIN
    SET NEW.formatted_id = CONCAT(NEW.project_id, '-', NEW.lesson_id);
END //
DELIMITER ;

Now when you insert data, the trigger will auto-generate the formatted_id for you, just like the generated column method.

While you want the formatted string for your office workflow, it's worth considering whether you need it as the actual primary key. Database best practices often favor composite keys (using project_id and lesson_id together) for this scenario, since they're more efficient for indexing and avoid storing redundant string data.

You can still get the formatted reference when you need it by concatenating the columns in your queries:

CREATE TABLE lessons (
    project_id VARCHAR(10) NOT NULL,
    lesson_id INT NOT NULL,
    -- Other columns here
    PRIMARY KEY (project_id, lesson_id)
);

-- Query to get the formatted lesson reference
SELECT CONCAT(project_id, '-', lesson_id) AS lesson_reference, lesson_content
FROM lessons
WHERE project_id = 'PR12' AND lesson_id = 81;

This approach keeps your database schema clean, improves query performance (integer and short varchar indexes are faster than longer string indexes), and still gives you the formatted string you need for your office use.

Your initial idea of adding a hyphen column as part of the primary key is technically possible, but it's not ideal:

• The hyphen has no actual data value—it's just formatting, so it adds unnecessary complexity to your primary key.
• Queries would require including the hyphen column, which is cumbersome (e.g., WHERE project_id = 'PR12' AND hyphen = '-' AND lesson_id = 81).
• It wastes storage space on a redundant, fixed-value column.

If you absolutely need the formatted string to be the primary key (e.g., if other systems expect this exact ID format), go with Option 1 (Generated Column)—it's the cleanest, most maintainable approach.

If you can adjust your workflow to handle the composite key behind the scenes and only use the formatted string for display, Option 3 is the better long-term choice for database performance and scalability.

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