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 [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
$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();
?>
ALTER TABLE ADD INDEX 鎖表(5.6+ Online DDL 緩解但仍有開銷)。IF NOT EXISTS / IF EXISTS 防止重複執行報錯,適合初始化腳本。ON DELETE/UPDATE 行為符合預期。pt-online-schema-change 或 gh-ost 進行無鎖變更。CREATE TABLE 定義數據骨架,DROP TABLE 徹底清理。掌握完整語法、字段屬性、索引策略、引擎選型,並遵循「顯式優於隱式、約束前置、註釋完整」原則,能構建健壯、可維護的數據庫 Schema。