SQLite3在C++程序中插入长文本后查询返回乱码问题

在C++面向对象作业中使用SQLite3，创建了包含文本字段的Restaurant表。当向文本列插入约20字符的长值后，执行SELECT查询会返回乱码；但短值则正常，20字符长度不应引发此类问题。

DB类代码 #

#include "doof/DB.h"

DB::DB() {
  if (sqlite3_open("./db/doof.db", &db) != SQLITE_OK) {
    cerr << "Error opening database: " << sqlite3_errmsg(db) << "\n";
  }

  DB::execute("PRAGMA foreign_keys = ON;");
  DB::execute("PRAGMA encoding = 'UTF-8';");
}

DB::~DB() {
  sqlite3_close(db);
}

sqlite3*& DB::getDb() {
  return db;
}

bool DB::execute(const string& sql) const {
  char* errorMessage;
  if (sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errorMessage) != SQLITE_OK) {
    cerr << "SQL Error: " << errorMessage << "\n";
    sqlite3_free(errorMessage);
    return false;
  }

  return true;
}

void DB::createTables() {
  DB::execute(
    "CREATE TABLE IF NOT EXISTS Restaurant("
    "r_id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "r_name TEXT NOT NULL,"
    "r_email TEXT UNIQUE NOT NULL,"
    "r_password TEXT NOT NULL,"
    "r_address TEXT,"
    "r_contact TEXT,"
    "r_type INTEGER NOT NULL CHECK (r_type in (0, 1, 2)));"
  );

  DB::execute(
    "CREATE TABLE IF NOT EXISTS Food("
    "f_id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "f_name TEXT NOT NULL,"
    "f_price REAL NOT NULL,"
    "f_available INTEGER NOT NULL CHECK (f_available in (0, 1)),"
    "r_id INTEGER NOT NULL,"
    "FOREIGN KEY(r_id) REFERENCES Restaurant(r_id),"
    "UNIQUE(f_name, r_id));"
  );
}

bool DB::insertRestaurant(Restaurant& restaurant) const {
  bool success = true;

  sqlite3_stmt* stmt;
  const char* sql = "INSERT INTO Restaurant (r_name, r_email, r_password, r_address, r_contact, r_type) VALUES (?, ?, ?, ?, ?, ?);";

  if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
    cerr << "Error in preparing restaurant insert statement: " << sqlite3_errmsg(db) << "\n";
    success = false;
    sqlite3_finalize(stmt);

    return success;
  }

  sqlite3_bind_text(stmt, 1, restaurant.getName().c_str(), -1, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, restaurant.getEmail().c_str(), -1, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 3, restaurant.getPassword().c_str(), -1, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 4, restaurant.getAddress().c_str(), -1, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 5, restaurant.getContact().c_str(), -1, SQLITE_STATIC);
  sqlite3_bind_int(stmt, 6, static_cast<int>(restaurant.getType()));

  if (sqlite3_step(stmt) != SQLITE_DONE) {
    cerr << "Error executing restaurant insert statement: " << sqlite3_errmsg(db) << "\n";
    success = false;
    sqlite3_finalize(stmt);

    return success;
  }

  const int restaurant_id = static_cast<int>(sqlite3_last_insert_rowid(db));
  restaurant.setId(restaurant_id);

  cout << "Data inserted successfully! Last inserted ID: " << restaurant_id << "\n";

  sqlite3_finalize(stmt);

  return success;
}

void DB::getRestaurants() const {
  sqlite3_stmt* stmt;
  const char* sql = "SELECT r_id, r_name, r_email, r_password, r_address, r_contact, r_type FROM Restaurant;";

  if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
    cerr << "Error preparing SELECT Restaurant statement: " << sqlite3_errmsg(db) << "\n";
    sqlite3_finalize(stmt);
    return;
  }

  while (sqlite3_step(stmt) == SQLITE_ROW) {
    int id = sqlite3_column_int(stmt, 0);
    string name = (char*)sqlite3_column_text(stmt, 1);
    string email = (char*)sqlite3_column_text(stmt, 2);
    string password = (char*)sqlite3_column_text(stmt, 3);
    string address = (char*)sqlite3_column_text(stmt, 4);
    string contact = (char*)sqlite3_column_text(stmt, 5);
    RestaurantType type = static_cast<RestaurantType>(sqlite3_column_int(stmt, 6));

    cout << "ID: " << id << "\n";
    cout << "Name: " << name << "\n";
    cout << "Email: " << email << "\n";
    cout << "Password: " << password << "\n";
  }
}

测试版本及输出 #

测试版本1（长文本值） #

#include "doof/Restaurant.h"
#include "doof/DB.h"

using namespace std;

int main() {
  DB db;
  db.execute("DROP TABLE Restaurant;");
  db.createTables();

  Restaurant res("Big Big Big Big Name", "bigbigemail@gmail.com", "password", "5121435", "Address", RESTAURANT_TYPE_FAST_FOOD);

  db.insertRestaurant(res);
  db.getRestaurants();
}

输出：

Data inserted successfully! Last inserted ID: 1
ID: 1
Name: p@▒┴☺
Email: p@▒┴☺
Password: password

测试版本2（短文本值） #

#include <iostream>
#include "doof/Restaurant.h"
#include "doof/DB.h"

using namespace std;

int main() {
  DB db;
  db.execute("DROP TABLE Restaurant;");
  db.createTables();

  Restaurant res("Small Name", "small@gmail.com", "password", "5121435", "Address", RESTAURANT_TYPE_FAST_FOOD);

  db.insertRestaurant(res);
  db.getRestaurants();
}

输出：

Data inserted successfully! Last inserted ID: 1
ID: 1
Name: Small Name
Email: small@gmail.com
Password: password

补充：Restaurant类和User类代码 #

Restaurant类 #

#include "doof/Restaurant.h"

Restaurant::Restaurant() {}

Restaurant::Restaurant(const string& _name, const string& _email, const string& _pass, const string& _contact, const string& _addr, RestaurantType _type) : User(_name, _email, _pass, _contact, _addr) {
  type = _type;
}

Restaurant::~Restaurant() {
  for (int i = 0; i < menu.size(); i++) {
    delete menu[i];
  }
}

// getters
RestaurantType Restaurant::getType() const {
  return type;
}

// setters
void Restaurant::setType(const RestaurantType& _type) {
  type = _type;
}

User类 #

#include "doof/User.h"

User::User() {}

User::User(const string& _name, const string& _email, const string& _pass, const string& _contact, const string& _addr) {
  name = _name;
  email = _email;
  password = _pass;
  contact = _contact;
  address = _addr;
}

// getters
int User::getId() const {
  return id;
}

string User::getName() const {
  return name;
}

string User::getEmail() const {
  return email;
}

string User::getPassword() const {
  return password;
}

string User::getAddress() const {
  return address;
}

string User::getContact() const {
  return contact;
}

// setters
void User::setId(const int _id) {
  id = _id;
}

void User::setName(const string& _name) {
  name = _name;
}

void User::setEmail(const string& _email) {
  email = _email;
}

void User::setPassword(const string& _password) {
  password = _password;
}

void User::setAddress(const string& _addr) {
  address = _addr;
}

void User::setContact(const string& _contact) {
  contact = _contact;
}

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