PHP MySQL 创建数据库
Running CREATE DATABASE via PDO, choosing a charset, and least privilege.
Key PointsAlways specify `utf8mb4` — the default charset is often `latin1`.
Creating a database
PHP
<?php
// 建库时还没指定 dbname，所以 DSN 里不能带
$dsn = "mysql:host=127.0.0.1;charset=utf8mb4";
$pdo = new PDO($dsn, "root", "secret", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$sql = "CREATE DATABASE IF NOT EXISTS demo
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci";
$pdo->exec($sql);
echo "数据库 demo 已就绪\n";
$dbs = $pdo->query("SHOW DATABASES")->fetchAll(PDO::FETCH_COLUMN);
echo in_array("demo", $dbs, true) ? "已确认存在" : "不存在", "\n";Output
数据库 demo 已就绪
已确认存在Charset and collation
| Setting | Bytes/char | Emoji | Recommendation |
|---|---|---|---|
| utf8（别名 utf8mb3） | 最多 3 | 不支持 | 不要用 |
| utf8mb4 | 最多 4 | 支持 | 推荐 |
| latin1 | 1 | 不支持 | 只适合纯英文 |
| Collation | Case | Accent | Use |
|---|---|---|---|
| utf8mb4_general_ci | 不敏感 | 不敏感 | 旧默认，够快 |
| utf8mb4_unicode_ci | 不敏感 | 不敏感 | Unicode 规则更准 |
| utf8mb4_0900_ai_ci | 不敏感 | 不敏感 | MySQL 8 默认，最快 |
| utf8mb4_bin | 敏感 | 敏感 | 需要严格区分时 |
_ci means case-insensitive: WHERE name = 'tom' matches Tom. For tokens or invite codes use utf8mb4_bin or add BINARY.Checking existing databases
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
// 查库是否存在（用预处理，别拼字符串）
$stmt = $pdo->prepare("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?");
$stmt->execute(["demo"]);
$exists = $stmt->fetchColumn();
echo $exists ? "库已存在: $exists" : "库不存在", "\n";
// 查库的字符集
$stmt = $pdo->prepare("SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?");
$stmt->execute(["demo"]);
print_r($stmt->fetch());Output
库已存在: demo
Array
(
[DEFAULT_CHARACTER_SET_NAME] => utf8mb4
[DEFAULT_COLLATION_NAME] => utf8mb4_unicode_ci
)Database names cannot be bound
CREATE DATABASE ? is a syntax error — DDL identifiers cannot be bound. Database, table and column names must be interpolated, so validate against an allowlist.
PHP
<?php
function safeIdent(string $name): string {
if (!preg_match('/^[A-Za-z_][A-Za-z0-9_]{0,63}$/', $name)) {
throw new InvalidArgumentException("非法标识符: $name");
}
return "`" . $name . "`";
}
foreach (["demo", "my_db2", "bad-name", "1abc", "x`; DROP TABLE users; --"] as $n) {
try {
echo safeIdent($n) . "\n";
} catch (InvalidArgumentException $e) {
echo "拒绝: " . $e->getMessage() . "\n";
}
}Output
`demo`
`my_db2`
拒绝: 非法标识符: bad-name
拒绝: 非法标识符: 1abc
拒绝: 非法标识符: x`; DROP TABLE users; --The last input is identifier injection. Only a strict allowlist regex stops it — letters, digits, underscore, max 64 characters.
Least privilege
SQL
-- 应用账号：只给需要的权限，不给 DROP
CREATE USER 'app'@'127.0.0.1' IDENTIFIED BY '强密码';
GRANT SELECT, INSERT, UPDATE, DELETE
ON demo.* TO 'app'@'127.0.0.1';
-- 需要建表时再加
-- GRANT CREATE, ALTER, INDEX ON demo.* TO 'app'@'127.0.0.1';
FLUSH PRIVILEGES;Never connect as root. A successful injection with root means reading every database, writing files, even executing commands via INTO OUTFILE and UDFs.
建库
PHP
<?php
// 建库时还没指定 dbname，所以 DSN 里不能带
$dsn = "mysql:host=127.0.0.1;charset=utf8mb4";
$pdo = new PDO($dsn, "root", "secret", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$sql = "CREATE DATABASE IF NOT EXISTS demo
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci";
$pdo->exec($sql);
echo "数据库 demo 已就绪\n";
$dbs = $pdo->query("SHOW DATABASES")->fetchAll(PDO::FETCH_COLUMN);
echo in_array("demo", $dbs, true) ? "已确认存在" : "不存在", "\n";Output
数据库 demo 已就绪
已确认存在字符集与排序规则
| 设置 | 每字符字节 | Emoji | 建议 |
|---|---|---|---|
| utf8（别名 utf8mb3） | 最多 3 | 不支持 | 不要用 |
| utf8mb4 | 最多 4 | 支持 | 推荐 |
| latin1 | 1 | 不支持 | 只适合纯英文 |
| 排序规则 | 大小写 | 重音 | 用途 |
|---|---|---|---|
| utf8mb4_general_ci | 不敏感 | 不敏感 | 旧默认，够快 |
| utf8mb4_unicode_ci | 不敏感 | 不敏感 | Unicode 规则更准 |
| utf8mb4_0900_ai_ci | 不敏感 | 不敏感 | MySQL 8 默认，最快 |
| utf8mb4_bin | 敏感 | 敏感 | 需要严格区分时 |
_ci 是 case-insensitive，WHERE name = 'tom' 能匹配到 Tom。要区分大小写（比如 token、邀请码）就用 utf8mb4_bin，或者在查询里加 BINARY。检查已存在的库
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
// 查库是否存在（用预处理，别拼字符串）
$stmt = $pdo->prepare("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?");
$stmt->execute(["demo"]);
$exists = $stmt->fetchColumn();
echo $exists ? "库已存在: $exists" : "库不存在", "\n";
// 查库的字符集
$stmt = $pdo->prepare("SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?");
$stmt->execute(["demo"]);
print_r($stmt->fetch());Output
库已存在: demo
Array
(
[DEFAULT_CHARACTER_SET_NAME] => utf8mb4
[DEFAULT_COLLATION_NAME] => utf8mb4_unicode_ci
)库名不能参数化
CREATE DATABASE ? 是语法错误——DDL 里的标识符不能用占位符。所以库名、表名、列名只能拼字符串，必须用白名单校验。
PHP
<?php
function safeIdent(string $name): string {
if (!preg_match('/^[A-Za-z_][A-Za-z0-9_]{0,63}$/', $name)) {
throw new InvalidArgumentException("非法标识符: $name");
}
return "`" . $name . "`";
}
foreach (["demo", "my_db2", "bad-name", "1abc", "x`; DROP TABLE users; --"] as $n) {
try {
echo safeIdent($n) . "\n";
} catch (InvalidArgumentException $e) {
echo "拒绝: " . $e->getMessage() . "\n";
}
}Output
`demo`
`my_db2`
拒绝: 非法标识符: bad-name
拒绝: 非法标识符: 1abc
拒绝: 非法标识符: x`; DROP TABLE users; --最后那条
x\; DROP TABLE users; --` 就是「标识符注入」。它绕不过去，只能靠严格的正则白名单——只允许字母数字下划线，长度上限 64。权限最小化
SQL
-- 应用账号：只给需要的权限，不给 DROP
CREATE USER 'app'@'127.0.0.1' IDENTIFIED BY '强密码';
GRANT SELECT, INSERT, UPDATE, DELETE
ON demo.* TO 'app'@'127.0.0.1';
-- 需要建表时再加
-- GRANT CREATE, ALTER, INDEX ON demo.* TO 'app'@'127.0.0.1';
FLUSH PRIVILEGES;应用连数据库绝对不要用 root。SQL 注入一旦成功，root 权限意味着攻击者能读所有库、写文件、甚至执行系统命令（INTO OUTFILE + UDF）。