MySQL 变量数据库名称语法错误

Question

我正在尝试为MySQL查询设置数据库名称。我已经能够让我的第一个查询工作，但第二个我正在努力弄清楚我的语法错误在哪里。我猜它与变量有关，但我被卡住了。

SET @db = CONCAT('SELECT client_databases.database_name
FROM client_databases
JOIN jobs ON jobs.organisation_id = client_databases.organisation_id
WHERE jobs.transaction_reference="K01-REC0000001"');
SET @q = CONCAT('SELECT receipts_lines.id, product_code, product_name, receipts_lines.is_putaway
FROM ', @db ,'.receipts
JOIN ', @db ,'.receipts_lines ON receipts_lines.receipt_id = receipts.id
WHERE reference_number="K01-REC0000001"');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

你应该做这样的事情。选择@db数据库名称并使用它来构建 stmt 的位置

SELECT client_databases.database_name INTO @db
FROM client_databases
JOIN jobs ON jobs.organisation_id = client_databases.organisation_id
WHERE jobs.transaction_reference="K01-REC0000001";
SET @q = CONCAT('SELECT receipts_lines.id, product_code, product_name, receipts_lines.is_putaway
FROM ', @db ,'.receipts
JOIN ', @db ,'.receipts_lines ON receipts_lines.receipt_id = receipts.id
WHERE reference_number="K01-REC0000001"');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;