在C程序中如何确保含UTF-8字符的字符串正确写入MariaDB？

尝试过程及问题 #

1. 初始代码与wctombs转换失败 #

最初尝试插入包含Unicode值177（对应字符±）的字符串，代码如下：

wchar_t wcs = L"INSERT INTO text(drawing, eID, txt) VALUES(9,14063,'\261\065\061\067\071')";

调用wctombs转换为多字节字符串：

int ret;
ret = wctombs(querybuffer, wcs, 60);

返回值ret=-1，提示存在无法转换为有效多字节字符的宽字符。

2. 转义Unicode编码后插入结果不符 #

按照建议修改代码，将非ASCII字符转义为\u00xx格式：

while(txt[i])
{
  c=cleaveMControl(txt,&i,j);
  if(c){
    if(c<128)
      query[j++]=c;
    else{
      query[j++]=92;
      query[j++]=92;
      sprintf(query+j,"u00%x",c);
      j=strlen(query);
    }
  }
}
query[j++]=39;
query[j++]=41;
query[j++]=59;
query[j]=0;
mysql_query(sqlconnect,query);

执行后查询结果：

MariaDB [D8]> select * from text where eID=14063;
+---------+-------+------------+
| drawing | eID   | txt        |
+----------+-------+------------+
|       9 | 14063 | \u00b15179 |
+---------+-------+------------+

期望结果应为±5179，但实际存储了转义后的字符串。

3. 手动构造UTF-8字符后字符缺失 #

尝试手动构造UTF-8编码：

wchar_t c;
char uni[5];
uni[i++]=192+((c>>6)&15);
uni[i++]=128+(c&63);

生成的SQL语句：

INSERT INTO text(drawing, eID, txt) VALUES(9,14063,'\302\261\065\061\067\071');

执行无错误，但插入后查询结果缺失±字符：

+----------+-------+------------+
|        9 | 14063 | 15179      |
+----------+-------+------------+

解决方法 #

1. 确保数据库、表、字段的字符集为UTF-8 #

检查并修改数据库、表和txt字段的字符集为utf8mb4（支持全部Unicode字符）：

-- 修改数据库字符集
ALTER DATABASE D8 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改表字符集
ALTER TABLE text CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 单独修改字段（如果需要）
ALTER TABLE text MODIFY COLUMN txt TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 建立数据库连接时指定UTF-8字符集 #

在调用mysql_real_connect后，设置连接字符集，确保客户端与服务器通信编码一致：

mysql_set_character_set(sqlconnect, "utf8mb4");

3. 使用参数化查询（推荐） #

避免手动拼接SQL字符串，改用预处理语句彻底解决字符转义与编码问题，同时避免SQL注入风险：

MYSQL_STMT *stmt;
MYSQL_BIND bind[3];
int drawing = 9;
int eID = 14063;
char txt[] = "±5179";
unsigned long txt_len = strlen(txt);

stmt = mysql_stmt_init(sqlconnect);
if (!stmt) {
    // 处理初始化错误
}

// 准备预处理语句
if (mysql_stmt_prepare(stmt, "INSERT INTO text(drawing, eID, txt) VALUES(?, ?, ?)", strlen("INSERT INTO text(drawing, eID, txt) VALUES(?, ?, ?)"))) {
    // 处理准备错误
}

// 绑定参数
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].buffer = &drawing;
bind[1].buffer_type = MYSQL_TYPE_LONG;
bind[1].buffer = &eID;
bind[2].buffer_type = MYSQL_TYPE_STRING;
bind[2].buffer = txt;
bind[2].buffer_length = txt_len;
bind[2].length = &txt_len;

if (mysql_stmt_bind_param(stmt, bind)) {
    // 处理绑定错误
}

// 执行语句
if (mysql_stmt_execute(stmt)) {
    // 处理执行错误
}

// 清理资源
mysql_stmt_close(stmt);

4. 修正手动UTF-8编码的问题（若坚持拼接SQL） #

如果必须手动构造SQL字符串，确保直接使用UTF-8字节序列，而非转义字符，同时保证连接字符集已设置为utf8mb4：

// 直接写入UTF-8编码的字符串，无需转义
char query[100];
snprintf(query, sizeof(query), "INSERT INTO text(drawing, eID, txt) VALUES(9, 14063, '%s')", "±5179");
mysql_query(sqlconnect, query);

内容的提问来源于stack exchange，提问作者Dave Coventry