问题遇到的现象和发生背景
Qt做个学校项目，用到了sql，创建一个用户库之后通过username查询userid，查询结果一直为空
QTcpServer* tcpServer = new QTcpServer(this);
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("database.db");
if (!db.open())
{
qDebug("数据库不能打开");
}
QSqlQuery query(db);
// 创建user表
if (!tableExists("user", db)) {
if (!query.exec("CREATE TABLE user ("
"user_id INT IDENTITY(1,1) PRIMARY KEY,"
"username NVARCHAR(20) NOT NULL,"
"paswd NVARCHAR(20) NOT NULL,"
"ifonline BIT NOT NULL)"
)) {
qDebug() << "创建user表失败：" << query.lastError().text();
}
} else {
qDebug() << "表user已存在，跳过创建";
}
// 创建pkm表
if (!tableExists("pkm", db)) {
if (!query.exec("CREATE TABLE pkm ("
"pokemon_id INT IDENTITY(1,1) PRIMARY KEY,"
"name NVARCHAR(20) NOT NULL,"
// "pname NVARCHAR(20) NOT NULL,"
"level INT NOT NULL,"
"experience INT NOT NULL"")"
)) {
qDebug() << "创建pkm表失败：" << query.lastError().text();
}
} else {
qDebug() << "表pkm已存在，跳过创建";
}
// 创建user_pkm_relations表
if (!tableExists("user_pkm_relations", db)) {
if (!query.exec("CREATE TABLE user_pkm_relations ("
"user_id INT NOT NULL,"
"pokemon_id INT NOT NULL,"
"PRIMARY KEY (user_id, pokemon_id),"
"FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE ON UPDATE CASCADE,"
"FOREIGN KEY (pokemon_id) REFERENCES pkm(pokemon_id) ON DELETE CASCADE ON UPDATE CASCADE)"
)) {
qDebug() << "创建user_pkm_relations表失败：" << query.lastError().text();
}
} else {
qDebug() << "表user_pkm_relations已存在，跳过创建";
}
tcpServer->listen(QHostAddress::Any, 8080);
connect(tcpServer, &QTcpServer::newConnection, [=]()
{
QTcpSocket *clientSocket = tcpServer->nextPendingConnection();
connect(clientSocket, &QTcpSocket::readyRead, [=]() {
// 当有数据可读时，此槽函数会被触发
qDebug()<<clientSocket->property("username").toString();
QDataStream in(clientSocket);
in.setVersion(QDataStream::Qt_5_15); // 确保版本与客户端一致
QDataStream out(clientSocket);
out.setVersion(QDataStream::Qt_5_15);
Request request;
in >> request;
// 处理请求
Response response;
switch (request.type) {
case RequestType::Register:
// 注册逻辑
response.success = handleRegistration(db,request.username, request.password);
response.message = 0;
break;
case RequestType::lvfight:
{
qDebug("开始lvfight");
qDebug()<<"username"<<clientSocket->property("username").toString();
QString pokemanName = request.password;
int userId = getUserIdFromUsername(db, clientSocket->property("username").toString());
```问题出在 getUserIdFromUsername函数：
```c++
int getUserIdFromUsername(QSqlDatabase db, const QString& username)
{
QSqlQuery query(db);
query.prepare("SELECT user_id FROM user WHERE username = ?");
query.addBindValue(username);
if (!query.exec()) {
qDebug() << "查询用户ID失败：" << query.lastError().text();
return -1; // 返回-1表示查询失败
}
if (query.next()) {
QVariant value = query.value(0);
qDebug() << "原始数据类型：" << value.typeName();
qDebug() << "原始数据值：" << value;
int id = value.toInt();
qDebug() << "转换后的整数值：" << id;
return id; // 返回找到的用户ID
} else {
return -1; // 如果没找到该用户，也返回-1
}
}
这个函数在我一次注册之后输出信息：表user已存在，跳过创建
表pkm已存在，跳过创建
表user_pkm_relations已存在，跳过创建
""
用户ID： 8
注册成功，赠送小精灵给用户。
开始赠送小精灵
chenggong
新插入的精灵的 ID 是： 15
chenggong
新插入的精灵的 ID 是： 16
用户ID： 8
关联成功
关联成功
0 true ""
""
denglu
登录成功，用户在线状态已更新为在线。
1 true ""
"8"
开始lvfight
username "8"
原始数据类型： QString
原始数据值： QVariant(QString, "")
转换后的整数值： 0
No associated pokemon found for the user
操作环境、软件版本等信息
Qt
尝试过的解决方法
刮破了头皮也没想明白是怎么回事
6.11进展
我又在数据库里创建了一张新表：
if (!tableExists("username_id_relations", db)) {
if (!query.exec("CREATE TABLE username_id_relations ("
"user_id INT NOT NULL,"
"username NVARCHAR(20) NOT NULL,"
"PRIMARY KEY (user_id),"
"UNIQUE (username),"
"FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE ON UPDATE CASCADE"
")"
)) {
qDebug() << "创建username_id_relations表失败：" << query.lastError().text();
}
} else {
qDebug() << "表username_id_relations已存在，跳过创建";
}
然后在getUserIdFromUsername函数里直接用这张新表就能查到正确id，用原来的user表却不能
int getUserIdFromUsername(QSqlDatabase db, const QString& username)
{
QSqlQuery query(db);
query.prepare("SELECT user_id FROM username_id_relations WHERE username = ?");
query.addBindValue(username);
if (!query.exec()) {
qDebug() << "查询用户ID失败：" << query.lastError().text();
return -1; // 返回-1表示查询失败
}
if (query.next()) {
QVariant value = query.value(0);
qDebug() << "原始数据类型：" << value.typeName();
qDebug() << "原始数据值：" << value;
int id = value.toInt();
qDebug() << "转换后的整数值：" << id;
return id; // 直接返回找到的用户ID
} else {
return -1; // 如果没找到该用户，也返回-1
}
}
我想要达到的结果
我想实现成功查询id
解释一下为什么新建一张表在注册的时候额外存储一下信息就能在这张表里查到，原来的user表查不到
