萬盛學電腦網

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

MySQL 建表與刪表語句詳解:CREATE TABLE / DROP TABLE 完整語法與最佳實踐

CREATE TABLE 完整語法

CREATE TABLE [IF NOT EXISTS] tbl_name (
    col_name col_type [col_attributes] [COMMENT 'comment'],
    ...,
    [PRIMARY KEY (col_list)],
    [UNIQUE KEY uk_name (col_list)],
    [INDEX idx_name (col_list)],
    [FOREIGN KEY fk_name (col_list) REFERENCES ref_tbl(ref_col) ON DELETE CASCADE],
    [CHECK (expr)]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
  [PARTITION BY ...]
  [COMMENT='table comment']
  [AUTO_INCREMENT = 1000];

實戰建表示例:教程表

CREATE TABLE `tutorials_tbl` (
    `tutorial_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
    `tutorial_title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '標題',
    `tutorial_author` VARCHAR(40) NOT NULL DEFAULT '' COMMENT '作者',
    `submission_date` DATE DEFAULT NULL COMMENT '提交日期',
    `content` TEXT COMMENT '正文',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態:1發布 0草稿',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`tutorial_id`),
    UNIQUE KEY `uk_title` (`tutorial_title`),
    INDEX `idx_author_status` (`tutorial_author`, `status`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='教程表';

關鍵字段屬性解析

屬性作用注意事項
NOT NULL禁止 NULL,優化器可優化、索引更緊湊**主鍵/外鍵/唯一索引列必加**
AUTO_INCREMENT自動遞增整數僅限整數類型,每表一列,通常作主鍵
DEFAULT插入時未指定列的默認值8.0+ 支持函數默認值 DEFAULT (UUID())
COMMENT列/表註釋文檔化、工具自動生成 ER 圖
UNSIGNED無符號,正數範圍翻倍ID、計數器、關係列建議加

命令行建表流程

mysql -u root -p
Enter password: ******

mysql> USE tutorials;
Database changed

mysql> CREATE TABLE tutorials_tbl (
    -> tutorial_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> tutorial_title VARCHAR(100) NOT NULL DEFAULT '',
    -> tutorial_author VARCHAR(40) NOT NULL DEFAULT '',
    -> submission_date DATE,
    -> PRIMARY KEY (tutorial_id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.16 sec)
提示: MySQL 客戶端以分號 ; 判斷語句結束,多行輸入時提示符變為 ->。

DROP TABLE 刪除表

-- 單表
DROP TABLE [IF EXISTS] tutorials_tbl;

-- 多表
DROP TABLE IF EXISTS t1, t2, t3;

-- 刪除前檢查外鍵依賴
SELECT TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND REFERENCED_TABLE_NAME='tutorials_tbl';

PHP 中執行 DDL

<?php
$mysqli = new mysqli("localhost", "root", "pass", "tutorials");
if ($mysqli->connect_errno) die("連接失敗: " . $mysqli->connect_error);

$sql = "CREATE TABLE IF NOT EXISTS `logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `level` VARCHAR(10) NOT NULL,
    `message` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_level_time` (`level`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if (!$mysqli->query($sql)) {
    die("建表失敗: " . $mysqli->error);
}
echo "表 logs 創建成功";
$mysqli->close();
?>

避坑與最佳實踐

  1. 顯式指定 ENGINE=InnoDB、CHARSET=utf8mb4,避免依賴服務器默認值(可能被修改)。
  2. 主鍵設計:業務無關、單調遞增(BIGINT AUTO_INCREMENT 或雪花算法),避免 UUID/字符串主鍵導致頁面分裂。
  3. 索引同步建立:建表時一併創建預期索引,避免上線後 ALTER TABLE ADD INDEX 鎖表(5.6+ Online DDL 緩解但仍有開銷)。
  4. IF NOT EXISTS / IF EXISTS 防止重複執行報錯,適合初始化腳本。
  5. 外鍵慎用:高併發場景建議應用層維護引用完整性,或用觸發器;若用外鍵,確保 ON DELETE/UPDATE 行為符合預期。
  6. 大表 DDL:使用 pt-online-schema-change 或 gh-ost 進行無鎖變更。

總結

CREATE TABLE 定義數據骨架,DROP TABLE 徹底清理。掌握完整語法、字段屬性、索引策略、引擎選型,並遵循「顯式優於隱式、約束前置、註釋完整」原則,能構建健壯、可維護的數據庫 Schema。

copyright © 萬盛學電腦網 all rights reserved