PHP MySQL 创建数据表
CREATE TABLE, choosing column types, keys and indexes.
Key PointsToo-small types force migrations, too-large waste space; `VARCHAR(255)` is not a default.
CREATE TABLE
SQL
CREATE TABLE IF NOT EXISTS users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age TINYINT UNSIGNED NULL,
status TINYINT NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_email (email),
KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$sql = file_get_contents(__DIR__ . "/create_users.sql");
$pdo->exec($sql);
echo "表 users 已创建\n";
$cols = $pdo->query("SHOW COLUMNS FROM users")->fetchAll();
echo "字段数: " . count($cols) . "\n";
foreach ($cols as $c) {
echo $c["Field"] . " " . $c["Type"] . "\n";
}Output
表 users 已创建
字段数: 7
id int unsigned
name varchar(50)
email varchar(100)
age tinyint unsigned
status tinyint
created_at datetime
updated_at datetimeChoosing column types
| Need | Type | Note |
|---|---|---|
| 主键 | BIGINT UNSIGNED AUTO_INCREMENT | INT caps at ~2.1B; use BIGINT for large tables |
| 短字符串 | VARCHAR(n) | 按实际长度定，别一律 255 |
| 长文本 | TEXT / LONGTEXT | 不能有默认值，索引要前缀 |
| 金额 | DECIMAL(10,2) 或整数分 | **绝不用 FLOAT** |
| 布尔 | TINYINT(1) | MySQL 没有真正的 bool |
| 时间 | DATETIME / TIMESTAMP | TIMESTAMP 有时区转换，DATETIME 没有 |
| JSON | JSON | MySQL 5.7+，可建函数索引 |
| 枚举 | VARCHAR + 应用层校验 | ENUM 改值要 ALTER TABLE |
FLOAT/DOUBLE for money accumulates rounding error. **Use DECIMAL or integer cents** — an accounting rule.Indexes
SQL
-- 主键：唯一且非空，聚簇索引
PRIMARY KEY (id)
-- 唯一索引：除了加速还保证唯一
UNIQUE KEY uk_email (email)
-- 普通索引：加速 WHERE / ORDER BY
KEY idx_status_created (status, created_at)
-- 联合索引：最左前缀原则
-- (status, created_at) 能加速 status=1，也能加速 status=1 AND created_at>...
-- 但不能加速只用 created_at 的查询Column order in a composite index matters: put the most selective first. (status, created_at) works as a prefix even though status has few values.
Altering tables
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
// 加字段
$pdo->exec("ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email");
echo "已加字段 phone\n";
// 加索引
$pdo->exec("ALTER TABLE users ADD KEY idx_phone (phone)");
echo "已加索引 idx_phone\n";
$cols = $pdo->query("SHOW COLUMNS FROM users")->fetchAll(PDO::FETCH_COLUMN);
echo "字段数: " . count($cols) . "\n";Output
已加字段 phone
已加索引 idx_phone
字段数: 8ALTER TABLE on a big table locks or hammers IO. MySQL 8's ALGORITHM=INPLACE, LOCK=NONE helps, but adding indexes still costs. Rehearse on a replica first.Table checklist
- Always a primary key —— InnoDB creates a hidden one you cannot control
- **
utf8mb4** —— consistent across database, table and connection - **
NOT NULLwith defaults** —— nullable columns index poorly and invite bugs - **
created_at/updated_at** —— invaluable when debugging - Foreign keys or app-level checks —— FKs cost locks under load; many teams validate in the app
建表语句
SQL
CREATE TABLE IF NOT EXISTS users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age TINYINT UNSIGNED NULL,
status TINYINT NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_email (email),
KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$sql = file_get_contents(__DIR__ . "/create_users.sql");
$pdo->exec($sql);
echo "表 users 已创建\n";
$cols = $pdo->query("SHOW COLUMNS FROM users")->fetchAll();
echo "字段数: " . count($cols) . "\n";
foreach ($cols as $c) {
echo $c["Field"] . " " . $c["Type"] . "\n";
}Output
表 users 已创建
字段数: 7
id int unsigned
name varchar(50)
email varchar(100)
age tinyint unsigned
status tinyint
created_at datetime
updated_at datetime字段类型怎么选
| 需求 | 类型 | 说明 |
|---|---|---|
| 主键 | BIGINT UNSIGNED AUTO_INCREMENT | INT 约 21 亿就满，大表用 BIGINT |
| 短字符串 | VARCHAR(n) | 按实际长度定，别一律 255 |
| 长文本 | TEXT / LONGTEXT | 不能有默认值，索引要前缀 |
| 金额 | DECIMAL(10,2) 或整数分 | **绝不用 FLOAT** |
| 布尔 | TINYINT(1) | MySQL 没有真正的 bool |
| 时间 | DATETIME / TIMESTAMP | TIMESTAMP 有时区转换，DATETIME 没有 |
| JSON | JSON | MySQL 5.7+，可建函数索引 |
| 枚举 | VARCHAR + 应用层校验 | ENUM 改值要 ALTER TABLE |
FLOAT/DOUBLE 存金额会产生舍入误差，0.1 + 0.2 可能存成 0.30000000000000004。**金额用 DECIMAL 或整数分**，这是财务系统的铁律。索引
SQL
-- 主键：唯一且非空，聚簇索引
PRIMARY KEY (id)
-- 唯一索引：除了加速还保证唯一
UNIQUE KEY uk_email (email)
-- 普通索引：加速 WHERE / ORDER BY
KEY idx_status_created (status, created_at)
-- 联合索引：最左前缀原则
-- (status, created_at) 能加速 status=1，也能加速 status=1 AND created_at>...
-- 但不能加速只用 created_at 的查询联合索引的列顺序很重要：区分度高的列放前面。(status, created_at) 里 status 只有几个值，区分度低，但作为前缀列仍然有效。
改表
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
// 加字段
$pdo->exec("ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email");
echo "已加字段 phone\n";
// 加索引
$pdo->exec("ALTER TABLE users ADD KEY idx_phone (phone)");
echo "已加索引 idx_phone\n";
$cols = $pdo->query("SHOW COLUMNS FROM users")->fetchAll(PDO::FETCH_COLUMN);
echo "字段数: " . count($cols) . "\n";Output
已加字段 phone
已加索引 idx_phone
字段数: 8大表
ALTER TABLE 会锁表或长时间占用 IO。MySQL 8 的 ALGORITHM=INPLACE, LOCK=NONE 能在线改，但加索引仍有代价。上线前在从库或影子表上先试一遍。建表检查清单
- 主键必备 —— 没主键的 InnoDB 表会隐式建一个，你还控制不了
- **
utf8mb4** —— 库、表、连接三处都要一致 - **
NOT NULL+ 默认值** —— 允许 NULL 的列索引效率低，逻辑也更容易出错 - **
created_at/updated_at** —— 排查问题时最有用 - 外键还是应用层保证 —— 高并发下外键有锁开销，很多团队选择应用层校验