第二章：数据库、表、行、列 —— 总账楼里的层级到底是什么

在 MySQL 里，开发者最常操作的层级是：

text

MySQL Server
└── database（账本 / schema）
    ├── table（账册）
    │   ├── row（登记）
    │   └── column（栏目）
    └── index / view / procedure ...

先建一套“城市户籍总账”：

sql

CREATE DATABASE city_registry
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE city_registry;

在 MySQL 语境里，database 和 schema 往往可以近似理解成一回事。你日常开发时，可以先把它简单记成“一个业务库”。

接着建几张核心账册：

sql

CREATE TABLE districts (
  district_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  district_name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE households (
  household_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  district_id BIGINT UNSIGNED NOT NULL,
  household_no VARCHAR(50) NOT NULL UNIQUE,
  address VARCHAR(200) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_household_status
    CHECK (status IN ('active', 'merged', 'closed')),
  CONSTRAINT fk_households_district
    FOREIGN KEY (district_id) REFERENCES districts(district_id)
) ENGINE=InnoDB;

CREATE TABLE citizens (
  citizen_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  household_id BIGINT UNSIGNED NOT NULL,
  national_id CHAR(18) NOT NULL UNIQUE,
  full_name VARCHAR(100) NOT NULL,
  gender CHAR(1) NOT NULL,
  birth_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_gender
    CHECK (gender IN ('M', 'F')),
  CONSTRAINT chk_citizen_status
    CHECK (status IN ('active', 'moved', 'deceased')),
  CONSTRAINT fk_citizens_household
    FOREIGN KEY (household_id) REFERENCES households(household_id)
) ENGINE=InnoDB;

这时你要形成一个稳定心智模型：

text

districts   -> 行政区总册
households  -> 户号总册
citizens    -> 市民登记总册

表里的一行，就是一条真实登记；列，就是这条登记的栏目。

例如：

sql

INSERT INTO districts (district_name)
VALUES ('浦东新区');

INSERT INTO households (district_id, household_no, address)
VALUES (1, 'HD-2026-0001', '世纪大道 100 号');

INSERT INTO citizens (household_id, national_id, full_name, gender, birth_date)
VALUES (1, '310101199806120011', '林远', 'M', '1998-06-12');

查一条户籍记录：

sql

SELECT citizen_id, full_name, status
FROM citizens
WHERE national_id = '310101199806120011';