连续两个DEFAULT约束的INSERT操作：如何判断跳过的属性？

Great question—this is a common point of confusion when working with DEFAULT constraints and partial INSERT statements. Let’s break this down with clear examples and actionable steps.

1. Start with Your INSERT Statement’s Column List #

The key rule here is: only the columns you explicitly list in your INSERT statement will receive the values you provide. Any columns not listed will use their DEFAULT value (if defined) or NULL (if allowed and no DEFAULT exists).

Let’s use a sample table matching your scenario:

CREATE TABLE people (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT DEFAULT 18,
    iq INT DEFAULT 100
);

Here’s how different INSERT statements map to skipped columns:

• If you run: INSERT INTO people (name) VALUES ('Alice');
  Both age and iq are skipped—they’ll use their DEFAULT values (18 and 100, respectively).
• If you run: INSERT INTO people (name, age) VALUES ('Bob', 25);
  Only iq is skipped—it’ll use the DEFAULT 100, while age gets the explicit value 25.
• If you run: INSERT INTO people (name, iq) VALUES ('Charlie', 120);
  Only age is skipped—it’ll use the DEFAULT 18, while iq gets the explicit value 120.

2. Verify the Result with a SELECT Query #

If you’re ever unsure which columns used their DEFAULT values (i.e., were skipped), just query the row you inserted to check the values:

SELECT age, iq FROM people WHERE name = 'Alice';

Compare the returned values to your DEFAULT constraints—any value matching the DEFAULT is a column that was skipped in the INSERT.

3. Pro Tip: Always Explicitly List Columns in INSERT #

To avoid confusion entirely, make it a habit to always list the columns you’re inserting into. This not only makes it crystal clear which columns are using DEFAULT values but also protects your code from breaking if the table’s column order changes later.

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