mysql配置中关于编码格式的理解

欢迎转载！转载时请注明出处：http://blog.youkuaiyun.com/nfer_zhuang/article/details/46521207

先确认一下我的mysql版本：

$ mysql -V
mysql  Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.2

前言

在一个 android->jni->网络通信->C++ Server->mysql 的应用场景下，如何保证：

1. 从client发送到server的数据是UTF-8编码格式
2. 存储在数据库中的数据是UTF-8编码格式
3. server和数据库交互使用的是UTF-8格式

注：无需考虑数据在网络传输的过程中编码格式的问题，可以简单的理解对于网络传输来讲任何的数据格式都可以认为是纯二进制数据。

从Client端到Server端

client端接受用户输入是在Android的Java部分，然后通过JNI函数将参数传递到C层，然后C层将数据打包封装后发送到Server端。

那么问题来了，如何保证Server端收到的数据是UTF-8编码的呢？

我们上退一步，如何保证Java通过JNI传递到C层的参数一定是UTF-8编码呢？

这里的关键就是：在JNI中要获取Java层传递下来的jstring类型数据，需要使用GetStringUTFChars()函数，具体可以参考下例：

JNIEXPORT void JNICALL Native_testFunc(JNIEnv* env, jobject object, jstring jData)
{
    const char* cData = env->GetStringUTFChars(jData, NULL);
    testFunc(cData);
    env->ReleaseStringUTFChars(jData, cData);
}

在上面的代码中，我们通过env->GetStringUTFChars(jData, NULL)得到一个const char *类型的数据，然后将这个数据打包封装后通过网络传输
到Server端。因此，我们要确保env->GetStringUTFChars(jData, NULL)函数返回的一定要是UTF-8编码格式的数据。

关于GetStringUTFChars()函数，oracle的官方文档有如下解释：

const jbyte* GetStringUTFChars(JNIEnv *env, jstring string,jboolean *isCopy);
Returns a pointer to an array of bytes representing the string in modified UTF-8 encoding. This array is valid until it is released by ReleaseStringUTFChars().

从这里我们可以了解到，JNI机制已经帮我们确保了从Java传递到C层的参数一定是UTF-8编码。

数据库中的数据编码格式

mysql默认的编码格式是latin1，可以通过下述命令获取：

mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
1 row in set (0.00 sec)

如果使用默认的命令创建和操作数据库，那么在遇到中文时，会有下述的表现：

mysql> DROP DATABASE IF EXISTS db_test;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE db_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE db_test;
Database changed
mysql> CREATE  TABLE t_test (
    ->   f_username        VARCHAR(128) NOT NULL,
    ->   f_nickname        VARCHAR(128) NOT NULL,
    ->   UNIQUE KEY (f_username)
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_test (f_username,f_nickname) VALUES('test','测试');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> SELECT * FROM t_test;
+------------+------------+
| f_username | f_nickname |
+------------+------------+
| test       | ??         |
+------------+------------+
1 row in set (0.00 sec)

mysql> 

注意，上面写入的是中文"测试"，但是读取后的内容却是乱码"??"，那么如何指定编码格式呢？

在mysql的帮助文档《10.1.5 Configuring the Character Set and Collation for Applications》中有如下说明：

If applications require data storage using a different character set or collation, you can configure character set information several ways:

• Specify character settings per database.
• Specify character settings at server startup. This causes the server to use the given settings for all applications that do not make other arrangements.
• Specify character settings at configuration time, if you build MySQL from source.

方法一：对每一个数据库单独设置编码格式

方法二：修改mysql的全局配置文件，指定编码格式

方法三：使用指定的编码格式重新编译mysql

重新编译源码的方式肯定不合适，修改mysql的全局配置文件也不太好，会影响到所有的数据库，那么最优的解决方案就是“在创建数据库时指定好编码格式”。

针对上面的测试，我们只修改一行：

CREATE DATABASE db_test DEFAULT CHARACTER SET utf8;

再次使用select查询后，结果如下：

mysql> SELECT * FROM t_test;
+------------+------------+
| f_username | f_nickname |
+------------+------------+
| test       | 测试       |
+------------+------------+
1 row in set (0.00 sec)

关于DEFAULT CHARACTER SET，官方的文档中是这么描述的：

Specify character settings per database
To create a database such that its tables will use a given default character set and collation for data storage, use a CREATE DATABASE statement like this:

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

Tables created in the database will use utf8 and utf8_general_ci by default for any character columns.

注：上面命令中还提到了DEFAULT COLLATE和utf8_general_ci，其大致意思是：指定数据库的排序规则，具体的内容请自行搜索。对于UTF-8编码，默认的排序规则就是utf8_general_ci。

使用api和数据库交互使用的数据格式

通过控制台我们得到了正确的输出结果，那么在程序中通过api操作数据库呢。下面就通过一个简单的测试程序读来取并打印t_test表中的内容：

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>

int main(void)
{
    MYSQL conn;
    mysql_init(&conn);
 
    if (!mysql_real_connect(&conn, "localhost", "db_test",
        "db_test", "db_test", 0, NULL, 0)) {
        printf("%s\n", mysql_error(&conn));
        return 1;
    }
 
    if (mysql_query(&conn, "SELECT * FROM t_test;")) {
         printf("%s\n", mysql_error(&conn));
        return 1;
    }

    MYSQL_RES * res = mysql_store_result(&conn);
    if (res && mysql_num_rows(res) > 0) {
        MYSQL_ROW row;
        while ((row = mysql_fetch_row(res)))
            printf("%s %s\n", row[0], row[1]);
    }
    else {
        printf("mysql_query failed, error:%s\n", mysql_error(&conn));
    } 
    mysql_free_result(res);

    mysql_close(&conn);
    return 0;
}

注：上面是通过用户名db_test@'localhost'，密码db_test，来访问db_test数据库，因此在上面创建完成数据库后，需要给db_test@'localhost'用户赋权限：

GRANT ALL ON db_test.* TO db_test@'localhost' IDENTIFIED BY 'db_test';

编译并运行程序，输出结果如下：

$ g++ mysqlclient.cpp -o mysqlclient -lmysqlclient
$ ./mysqlclient
test ??

咦？怎么是乱码？？？我们用控制台查询明明是好的。不过这也说明了数据库中存储的是正确的，问题应该只是出在我们的测试代码上了。

我们在上面的测试程序中添加一行代码：

    mysql_query(&conn, "set names utf8;");

再次编译后运行：

$ g++ mysqlclient.cpp -o mysqlclient -lmysqlclient
$ ./mysqlclient
test 测试

输出正常了。。。

总结

在涉及到mysql的操作时，如果要确保写入/读出以及保存到数据库中的数据均按照指定格式进行编码，那么需要以下步骤：

• 在创建数据库时，指定编码格式，e.g.: CREATE DATABASE db_test DEFAULT CHARACTER SET utf8;
• 在使用api连接数据库后，通过SET NAMES指定交互的编码格式，e.g.: mysql_query(&conn, "set names utf8;");

思考问题

1. 如果在创建数据库时，没有指定编码格式，而在连接数据库后，设置了特定的编码格式，能否正确的读出之前写入的数据？
2. 在上面的基础上，先使用特定的编码格式写入一条数据，能否使用该编码格式正确的读出？

以上两个问题，请自行思考并验证。