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

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

CREATE DATABASE your_database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

ALTER DATABASE your_database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

ALTER TABLE your_table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE your_table_name
    MODIFY your_column_name VARCHAR(255)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

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

// 现在可以安全地执行包含特殊字符的查询
$sql = "SELECT * FROM your_table_name WHERE name = 'éššede+á'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 结果";
}

$conn->close();
?>

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";

try {
    $dsn = "mysql:host=$servername;dbname=$dbname;charset=utf8mb4";
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4" // 显式设置字符集
    ]);

    // 现在可以安全地执行包含特殊字符的查询
    $stmt = $pdo->prepare("SELECT * FROM your_table_name WHERE name = :name");
    $stmt->execute([':name' => 'éššede+á']);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }

} catch (PDOException $e) {
    die("连接失败: " . $e->getMessage());
}
?>