迭代 MySQL 模式 [英] Iterate MySQL schemas

问题描述

问题描述

我有一个单租户 MySQL 数据库设置.也就是说，我对每个客户端都有一个相同的架构.

I have a single-tenant MySQL database setup. That is, I have one identical schema for each client.

现在我需要为每个客户端运行一个特定的查询.这在多租户设置中很容易(所有客户端共享一个模式).但是，通过我的设置，我需要迭代模式.更一般地说，我想访问一个名称由变量指定的模式.如何做到这一点?

Now I need to run a specific query for each client. That would be easy in a multi-tenant setting (where all clients share a single schema). With my setup however, I need to iterate the schemas. More generally, I want to access a schema whose name is given by a variable. How can that be done?

我的尝试

• 如果我尝试 USE varSchemaName(其中 varSchemaName 是 varchar变量)，我收到错误消息 ERROR 1314: USE is not allowed在存储过程中.

• If I try USE varSchemaName (where varSchemaName is a varchar variable), I get the error message ERROR 1314: USE is not allowed in stored procedures.

如果我尝试 SELECT * FROM varSchemaName.MyTable 我得到错误代码:1146.表varSchemaName.MyTable"不存在.显然 MySQL 认为 varSchemaName 是一个文字，而不是一个变量.

If I try SELECT * FROM varSchemaName.MyTable I get Error Code: 1146. Table 'varSchemaName.MyTable' doesn't exist. Apparently MySQL considers varSchemaName to be a literal, not a variable.

推荐答案

您必须先构建语句.

SET @sql = CONCAT('SELECT * FROM ', varSchemaName, '.MyTable');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

您可以阅读有关准备好的语句的更多信息 此处.

You can read more about prepared statements here.

您可能想为表名使用变量，但这不起作用.这些参数用于 where 子句等中的值.上面的路是要走的路.

You might be tempted to use variables for tablenames, but that doesn't work. Those parameters are for values in where clauses and so on. The above way is the way to go.

这篇关于迭代 MySQL 模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！