MySQL中特殊字符编码的最佳实践：为什么推荐utf8mb4

-- 创建数据库时指定utf8mb4字符集和排序规则
CREATE DATABASE mydatabase
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- 使用该数据库
USE mydatabase;

-- 创建表时指定utf8mb4字符集和排序规则
CREATE TABLE mytable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

<?php
$host = 'localhost';
$db   = 'mydatabase';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$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, $user, $pass, $options);
    echo "数据库连接成功，字符集设置为utf8mb4。<br>";

    // 示例：插入包含特殊字符的数据
    $stmt = $pdo->prepare("INSERT INTO mytable (name, description) VALUES (?, ?)");
    $name_with_special_chars = 'éššede+á ?';
    $description = '这是一个包含特殊字符和表情符号的描述。';
    $stmt->execute([$name_with_special_chars, $description]);
    echo "数据插入成功。<br>";

    // 示例：查询包含特殊字符的数据
    $stmt = $pdo->prepare("SELECT * FROM mytable WHERE name = ?");
    $stmt->execute([$name_with_special_chars]);
    $result = $stmt->fetch();
    if ($result) {
        echo "查询结果：<br>";
        echo "ID: " . $result['id'] . "<br>";
        echo "Name: " . $result['name'] . "<br>";
        echo "Description: " . $result['description'] . "<br>";
    } else {
        echo "未找到匹配的数据。<br>";
    }

} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>

<?php
$mysqli = new mysqli("localhost", "your_username", "your_password", "mydatabase");

// 检查连接
if ($mysqli->connect_errno) {
    echo "连接失败: " . $mysqli->connect_error;
    exit();
}

// 设置连接字符集为utf8mb4
$mysqli->set_charset("utf8mb4");

echo "数据库连接成功，字符集设置为utf8mb4。<br>";

// 示例：插入包含特殊字符的数据
$name_with_special_chars = 'éššede+á ?';
$description = '这是一个包含特殊字符和表情符号的描述。';
$stmt = $mysqli->prepare("INSERT INTO mytable (name, description) VALUES (?, ?)");
$stmt->bind_param("ss", $name_with_special_chars, $description);
$stmt->execute();
echo "数据插入成功。<br>";

// 示例：查询包含特殊字符的数据
$stmt = $mysqli->prepare("SELECT * FROM mytable WHERE name = ?");
$stmt->bind_param("s", $name_with_special_chars);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
    echo "查询结果：<br>";
    echo "ID: " . $row['id'] . "<br>";
    echo "Name: " . $row['name'] . "<br>";
    echo "Description: " . $row['description'] . "<br>";
} else {
    echo "未找到匹配的数据。<br>";
}

$stmt->close();
$mysqli->close();
?>