萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 建庫建表實戰:從零構建規範化數據庫結構

一鍵建庫建表腳本

-- 1. 刪除舊庫(謹慎!生產環境禁用)
DROP DATABASE IF EXISTS school;

-- 2. 創建數據庫(指定字符集排序規則)
CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 3. 切換上下文
USE school;

-- 4. 創建教師表
CREATE TABLE teacher (
    id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵ID',
    name CHAR(10) NOT NULL COMMENT '姓名',
    address VARCHAR(50) DEFAULT '深圳' COMMENT '地址',
    year DATE COMMENT '入職年份'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教師信息表';

-- 5. 插入測試數據
INSERT INTO teacher (name, address, year) VALUES 
('allen', '大連一中', '1976-10-10'),
('jack', '大連二中', '1975-12-23');

腳本執行兩種方式

方式一:命令行管道導入(適合自動化部署)

# 將上述 SQL 保存為 school.sql
mysql -uroot -p密碼 < school.sql

方式二:MySQL 客戶端 source 命令(適合交互調試)

mysql> SOURCE /path/to/school.sql;

表結構設計細節解析

字段類型約束設計理由
idINT(3)NOT NULL AUTO_INCREMENT PRIMARY KEY業務主鍵,自增避免衝突,顯示寬度 3 足夠
nameCHAR(10)NOT NULL定長,姓名長度固定,查詢略快
addressVARCHAR(50)DEFAULT '深圳'變長,大多數教師在深圳,減少存儲
yearDATE無純日期類型,支持日期運算

進階建表最佳實踐

  1. 字符集統一 utf8mb4:支持 Emoji、生僻字,COLLATE utf8mb4_general_ci 或 0900_ai_ci(MySQL 8.0)。
  2. 引擎統一 InnoDB:支持事務、行鎖、外鍵、崩潰恢復。
  3. 字段加 COMMENT:數據字典自動生成依賴。
  4. 表加 COMMENT:業務含義一目了然。
  5. 主鍵盡量用 BIGINT:防止未來 ID 耗盡(INT 上限 21 億)。
  6. 時間字段用 DATETIME/TIMESTAMP:避免 VARCHAR 存時間,利於範圍查詢與索引。
  7. 軟刪除欄位:deleted TINYINT DEFAULT 0 + deleted_at DATETIME NULL,物理刪除改邏輯刪除。
  8. 版本樂觀鎖:version INT DEFAULT 0,更新時 WHERE version = old_version。

常見 DDL 陷阱

  • INT(3) 中的 3 僅是顯示寬度,不限制存儲範圍(仍是 -21億~21億)。
  • CHAR vs VARCHAR:定長 CHAR 適合長度固定(MD5、手機號),變長 VARCHAR 適合不定長,節省空間但需 1-2 字節長度前綴。
  • DEFAULT 只能是常量或函數(NOW()、CURRENT_TIMESTAMP),不能引用其他列。

總結

規範的建庫建表是系統穩定的基石。遵循utf8mb4 + InnoDB + 顯式主鍵 + 字段註釋 + 軟刪除 + 樂觀鎖模板,配合版本化 SQL 腳本管理,讓數據庫結構演進可控、可追溯、可回滾。

copyright © 萬盛學電腦網 all rights reserved