数据库分表：多个keys关联一个value

目的：为了解决重复value的问题，节约存储内存 。

qt版本：

#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    // 创建 SQLite 数据库文件
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("example.db");

    if (!db.open()) {
        qDebug() << "Error: Unable to open database";
        return -1;
    }

    // 创建 key 表
    QSqlQuery query;
    if (!query.exec("CREATE TABLE IF NOT EXISTS keys (keyName TEXT)")) {
        qDebug() << "Error: Unable to create keys table";
        return -1;
    }

    // 创建 value 表
    if (!query.exec("CREATE TABLE IF NOT EXISTS values (id INTEGER PRIMARY KEY, value TEXT)")) {
        qDebug() << "Error: Unable to create values table";
        return -1;
    }

    // 创建关联表
    if (!query.exec("CREATE TABLE IF NOT EXISTS key_value_relation (keyName TEXT, valueId INTEGER)")) {
        qDebug() << "Error: Unable to create key_value_relation table";
        return -1;
    }

    // 插入示例数据
    if (!query.exec("INSERT INTO keys (keyName) VALUES ('key1')")) {
        qDebug() << "Error: Unable to insert data into keys table";
        return -1;
    }

    if (!query.exec("INSERT INTO keys (keyName) VALUES ('key2')")) {
        qDebug() << "Error: Unable to insert data into keys table";
        return -1;
    }

    if (!query.exec("INSERT INTO values (value) VALUES ('value1')")) {
        qDebug() << "Error: Unable to insert data into values table";
        return -1;
    }

    if (!query.exec("INSERT INTO key_value_relation (keyName, valueId) VALUES ('key1', 1)")) {
        qDebug() << "Error: Unable to insert data into key_value_relation table";
        return -1;
    }

    if (!query.exec("INSERT INTO key_value_relation (keyName, valueId) VALUES ('key2', 1)")) {
        qDebug() << "Error: Unable to insert data into key_value_relation table";
        return -1;
    }

    // 查询示例数据
    if (!query.exec("SELECT keys.keyName, values.value FROM keys INNER JOIN key_value_relation ON keys.keyName = key_value_relation.keyName INNER JOIN values ON key_value_relation.valueId = values.id")) {
        qDebug() << "Error: Unable to select data from tables";
        return -1;
    }

    while (query.next()) {
        QString key = query.value(0).toString();
        QString value = query.value(1).toString();
        qDebug() << "Key: " << key << ", Value: " << value;
    }

    // 关闭数据库连接
    db.close();

    return a.exec();
}

C++版本：

#include <iostream>
#include <sqlite3.h>

int main() {
    sqlite3* db;
    char* errMsg = 0;
    int rc = sqlite3_open("example.db", &db);

    if (rc) {
        std::cerr << "Error: Unable to open database" << std::endl;
        return -1;
    }

    rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS keys (keyName TEXT)", 0, 0, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to create keys table" << std::endl;
        sqlite3_free(errMsg);
        return -1;
    }

    rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS values (id INTEGER PRIMARY KEY, value TEXT)", 0, 0, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to create values table" << std::endl;
        sqlite3_free(errMsg);
        return -1;
    }

    rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS key_value_relation (keyName TEXT, valueId INTEGER)", 0, 0, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to create key_value_relation table" << std::endl;
        sqlite3_free(errMsg);
        return -1;
    }

    rc = sqlite3_exec(db, "INSERT INTO keys (keyName) VALUES ('key1')", 0, 0, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to insert data into keys table" << std::endl;
        sqlite3_free(errMsg);
        return -1;
    }

    rc = sqlite3_exec(db, "INSERT INTO keys (keyName) VALUES ('key2')", 0, 0, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to insert data into keys table" << std::endl;
        sqlite3_free(errMsg);
        return -1;
    }

    rc = sqlite3_exec(db, "INSERT INTO values (value) VALUES ('value1')", 0, 0, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to insert data into values table" << std::endl;
        sqlite3_free(errMsg);
        return -1;
    }

    rc = sqlite3_exec(db, "INSERT INTO key_value_relation (keyName, valueId) VALUES ('key1', 1)", 0, 0, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to insert data into key_value_relation table" << std::endl;
        sqlite3_free(errMsg);
        return -1;
    }

    rc = sqlite3_exec(db, "INSERT INTO key_value_relation (keyName, valueId) VALUES ('key2', 1)", 0, 0, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to insert data into key_value_relation table" << std::endl;
        sqlite3_free(errMsg);
        return -1;
    }

    sqlite3_stmt* stmt;
    rc = sqlite3_prepare_v2(db, "SELECT keys.keyName, values.value FROM keys INNER JOIN key_value_relation ON keys.keyName = key_value_relation.keyName INNER JOIN values ON key_value_relation.valueId = values.id", -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        std::cerr << "Error: Unable to select data from tables" << std::endl;
        return -1;
    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        std::cout << "Key: " << sqlite3_column_text(stmt, 0) << ", Value: " << sqlite3_column_text(stmt, 1) << std::endl;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return 0;
}