萬盛學電腦網

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

PHP MySQL 建立資料庫與資料表完整實作指南

建立資料庫

SQL 語法

CREATE DATABASE database_name
    [CHARACTER SET charset_name]
    [COLLATE collation_name];

PHP 實作 (mysqli 物件導向)

<?php
$host = 'localhost';
$user = 'root';
$pass = 'password';
$dbname = 'my_db';

$mysqli = new mysqli($host, $user, $pass);
if ($mysqli->connect_errno) {
    die('連接失敗: ' . $mysqli->connect_error);
}

$mysqli->set_charset('utf8mb4');

// 建立資料庫 (若不存在)
$sql = "CREATE DATABASE IF NOT EXISTS `$dbname` 
        CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci";

if ($mysqli->query($sql)) {
    echo "資料庫 '$dbname' 建立成功或已存在\n";
} else {
    die('建立失敗: ' . $mysqli->error);
}

// 選擇資料庫 (後續操作必須先選擇)
$mysqli->select_db($dbname);

$mysqli->close();
?>

建立資料表

SQL 語法

CREATE TABLE table_name (
    column_name1 data_type [constraints],
    column_name2 data_type [constraints],
    ...,
    [INDEX index_name (column_list)],
    [PRIMARY KEY (column_list)],
    [FOREIGN KEY (column_list) REFERENCES ref_table (ref_column_list)]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

完整範例:會員資料表

<?php
$mysqli = new mysqli('localhost', 'root', 'password', 'my_db');
if ($mysqli->connect_errno) die('連接失敗: ' . $mysqli->connect_error);
$mysqli->set_charset('utf8mb4');

$sql = "
CREATE TABLE IF NOT EXISTS `members` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
    `username` VARCHAR(50) NOT NULL COMMENT '使用者名稱',
    `email` VARCHAR(100) NOT NULL COMMENT '電子郵件',
    `password_hash` VARCHAR(255) NOT NULL COMMENT '密碼雜湊',
    `nickname` VARCHAR(50) DEFAULT NULL COMMENT '暱稱',
    `avatar` VARCHAR(255) DEFAULT NULL COMMENT '頭像URL',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態: 1=正常, 0=停用',
    `last_login_at` DATETIME DEFAULT NULL COMMENT '最後登入時間',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_email` (`email`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='會員資料表';
";

if ($mysqli->query($sql)) {
    echo "資料表 'members' 建立成功\n";
} else {
    die('建立失敗: ' . $mysqli->error);
}

$mysqli->close();
?>

主鍵與 AUTO_INCREMENT 詳解

主鍵特性

  • 唯一識別每一列記錄
  • 不能為 NULL
  • 每個表只能有一個主鍵 (可為複合主鍵)
  • 自動建立唯一索引

AUTO_INCREMENT 自動遞增

-- 定義主鍵並自動遞增
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)

-- 指定起始值
ALTER TABLE members AUTO_INCREMENT = 10000;

常用資料類型選擇指南

業務場景推薦類型範例
主鍵 ID、外鍵INT UNSIGNED / BIGINT UNSIGNEDAUTO_INCREMENT
狀態、類型、性別 (有限值)TINYINT UNSIGNED / ENUMstatus TINYINT(1), gender ENUM('M','F')
金額、價格、匯率DECIMAL(10,2) / DECIMAL(18,4)price DECIMAL(10,2)
短字串 (名稱、代碼)VARCHAR(50-100)username VARCHAR(50)
長文本 (文章、描述)TEXT / MEDIUMTEXT / LONGTEXTcontent TEXT
日期 (生日、入職)DATEbirthday DATE
日期時間 (建立、更新、操作時間)DATETIME / TIMESTAMPcreated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
二進制資料 (圖片、檔案)BLOB 系列 (建議存檔案路徑)avatar VARCHAR(255) 存路徑
IP 位址INT UNSIGNED (INET_ATON/INET_NTOA)ip INT UNSIGNED
JSON 結構化資料JSON (MySQL 5.7+)metadata JSON

重要提醒

  1. 必須先選擇資料庫:$mysqli->select_db($dbname) 或連接時指定 $dbname
  2. VARCHAR 必須指定長度:VARCHAR(255),無長度會報錯
  3. 使用反引號:`table_name`、`column_name` 避免保留字衝突
  4. 明確指定 ENGINE=InnoDB:獲得事務、外鍵、崩潰恢復支援
  5. 統一字元集:資料庫、資料表、連接皆用 utf8mb4
  6. 加上註解:COMMENT '說明' 利於維護

驗證建立結果

SHOW TABLES;
DESCRIBE members;
SHOW CREATE TABLE members\G
copyright © 萬盛學電腦網 all rights reserved