如何解决mysql insert乱码的问题

下面是详细的攻略。

问题描述

在使用 MySQL 数据库进行数据插入时，如果插入的数据中包含了中文、日语等非 ASCII 字符，有可能会出现乱码的情况。这是因为在 MySQL 中默认使用的是 latin1 编码，而非 utf8 编码。那么，如何才能够解决这个问题呢？

解决方案

解决MySQL insert乱码的问题，需要在多个方面进行设置和调整。下面我们分别来看一下这些方面的具体情况。

1. 数据库、表和字段的编码设置

首先要确保数据库、表和字段的编码设置都是采用 utf8mb4 编码的。可以使用如下的 SQL 语句进行设置：

/* 设置数据库的编码 */
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 COLUMN `your_column_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

其中，your_database_name、your_table_name、your_column_name 都是需要替换成相应的实际表名和字段名。

2. 连接参数设置

接下来，需要在连接 MySQL 数据库时设置连接参数，确保字符集和连接质量等参数的正确设置。可以参考下面的 PHP 代码：

$dsn = "mysql:host=localhost;dbname=your_database_name;charset=utf8mb4";
$username = "your_username";
$password = "your_password";

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'",
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    throw new PDOException($e->getMessage(), (int)$e->getCode());
}

3. 数据插入时的编码设置

最后，还需要在执行插入数据的 SQL 语句时，显式地指定编码方式，确保插入的数据能够正确地被识别和存储。可以使用如下的 SQL 语句进行设置：

INSERT INTO `your_table_name`(`your_column_name`) VALUES (_utf8mb4('你好'));

其中 _utf8mb4() 是一个自定义的函数，代码如下：

DROP FUNCTION IF EXISTS `_utf8mb4`;
DELIMITER $$
CREATE FUNCTION `_utf8mb4`(
  input VARCHAR(255)
) RETURNS VARBINARY(255) CHARSET utf8mb4
BEGIN
  RETURN CONVERT(input USING utf8mb4);
END
$$
DELIMITER ;

这个函数的作用就是将输入的字符串转换成 utf8mb4 编码的二进制序列。

示例说明

下面，我们来看一下如何在实际的开发中应用上述的解决方案。

示例一

在 PHP 应用中，我们可以使用 PDO 扩展来连接 MySQL 数据库，然后执行插入操作，代码如下：

try {
    $pdo = new PDO('mysql:host=localhost;dbname=your_database_name;charset=utf8mb4', 'your_username', 'your_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'",
    ]);

    $sql = "INSERT INTO `your_table_name`(`your_column_name`) VALUES (?)";
    $stmt = $pdo->prepare($sql);

    $str = "你好";
    $stmt->bindParam(1, $str, PDO::PARAM_STR);
    $stmt->execute();

    echo "插入成功！";
} catch (PDOException $e) {
    echo "插入失败：" . $e->getMessage();
}

在这个示例中，我们首先使用了 PDO 扩展来连接 MySQL 数据库，使用支持参数绑定的方式插入数据。通过设置连接参数以及在 SQL 语句中使用 ? 占位符，并使用 bindParam() 方法将字符串参数绑定到占位符上，确保了插入数据时的编码正确。

示例二

在 Java 应用中，我们可以使用 JDBC API 来连接 MySQL 数据库，然后执行插入操作，代码如下：

String url = "jdbc:mysql://localhost/your_database_name?useUnicode=true&characterEncoding=utf8";
String username = "your_username";
String password = "your_password";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement stmt = conn.prepareStatement("INSERT INTO `your_table_name`(`your_column_name`) VALUES (?)")
) {
    conn.prepareStatement("SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';").execute();
    stmt.setString(1, "你好");
    stmt.executeUpdate();
    System.out.println("插入成功！");
} catch (SQLException ex) {
    System.out.println("插入失败：" + ex.getMessage());
}

在这个示例中，我们使用了 JDBC API 来连接 MySQL 数据库，使用支持参数绑定的方式插入数据。在连接数据库时使用了 useUnicode=true&characterEncoding=utf8 的参数来指定使用 utf-8 编码，然后使用 SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'; 的 SQL 语句来设置连接编码。通过在 SQL 语句中使用 ? 占位符，并使用 setString() 方法将字符串参数设置到占位符中，确保了插入数据时的编码正确。

总结

通过上述的解决方案，我们可以有效地解决 MySQL insert 乱码的问题。其中，关键的是要确保数据库、表和字段都采用了 utf8mb4 编码，并在连接 MySQL 数据库时设置了相应的连接参数和字符集。这样，即使插入了包含非 ASCII 字符的数据，也可以正确地存储和读取。