Mac 作為開發者首選平台,搭建高效的 PHP + MySQL 環境是入門必修課。本文涵蓋 MAMP Pro 可視化方案、Homebrew 命令行方案、數據庫基礎操作、PHP 連接 MySQL 完整流程。
/Applications/MAMP/htdocshttps://localhost:8888# 安裝 PHP (自動安裝依賴)
brew tap shivammathur/php
brew install shivammathur/php/php@8.2
# 安裝 MySQL
brew install mysql
brew services start mysql
# 安全初始化
mysql_secure_installation
# PHP 路徑
/usr/local/bin/php # 或 /opt/homebrew/bin/php (Apple Silicon)
<?php
// 1. 設置編碼
header('Content-Type: text/html; charset=utf-8');
// 2. 定義配置常量(安全、可維護)
define('DB_HOST', '127.0.0.1');
define('DB_USER', 'root');
define('DB_PASS', 'your_password');
define('DB_NAME', 'my_database');
define('DB_PORT', 3306);
define('DB_CHARSET', 'utf8mb4');
// 3. 建立連接 (mysqli 面向對象)
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);
if ($mysqli->connect_errno) {
die("連接失敗: " . $mysqli->connect_error);
}
// 4. 設置字符集(防止中文亂碼)
$mysqli->set_charset(DB_CHARSET);
// 5. 執行查詢
$sql = "SELECT id, username, email FROM users WHERE status = 1";
$result = $mysqli->query($sql);
if ($result) {
// 6. 遍歷結果集
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['id']}, 用戶: {$row['username']}, 郵箱: {$row['email']}
";
}
$result->free(); // 釋放結果集
}
// 7. 預處理語句(防 SQL 注入)
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$username = "new_user";
$email = "user@example.com";
$stmt->execute();
echo "新用戶 ID: " . $stmt->insert_id;
$stmt->close();
$mysqli->close();
?>
| 分類 | 類型 | 說明 |
|---|---|---|
| 整數 | TINYINT | 1 字節,-128~127 |
| SMALLINT | 2 字節 | |
| INT | 4 字節,常用主鍵 | |
| BIGINT | 8 字節,大數據 ID | |
| 浮點/定點 | FLOAT | 單精度 |
| DOUBLE | 雙精度 | |
| DECIMAL(M,D) | 精確十進制,財務必用 | |
| 字符串 | CHAR(n) | 固定長度,高性能 |
| VARCHAR(n) | 變長,節省空間 | |
| 日期時間 | DATETIME | 日期+時間,8 字節 |
| TIMESTAMP | 時間戳,4 字節,時區感知 | |
| DATE | 僅日期 | |
| 文本 | TINYTEXT | 255 字節 |
| TEXT | 64KB | |
| LONGTEXT | 4GB |
-- 數據庫操作
SHOW DATABASES;
CREATE DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE my_db;
-- 表操作
SHOW TABLES;
DESCRIBE users; -- 表結構
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 數據操作
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
SELECT * FROM users WHERE id = 1;
UPDATE users SET email = 'new@example.com' WHERE id = 1;
DELETE FROM users WHERE id = 1;
-- 編碼設置
SET NAMES utf8mb4; -- 連接層編碼
<?php
$dsn = "mysql:host=127.0.0.1;dbname=my_database;charset=utf8mb4";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
// 查詢
$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch()) {
print_r($row);
}
// 預處理插入
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(['jane', 'jane@example.com']);
echo "ID: " . $pdo->lastInsertId();
} catch (PDOException $e) {
die("數據庫錯誤: " . $e->getMessage());
}
?>
mysql_* 函數(PHP 7.0+ 已移除)mysqli 或 PDO 並啟用預處理