在 MySQL 脚本中设置当前数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1820971/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set Current Database in MySQL Script
提问by afilbert
I have a script file for MySQL that needs to be run on about 30 databases. Here's a snippet:
我有一个 MySQL 脚本文件，需要在大约 30 个数据库上运行。这是一个片段：
ALTER TABLE <whatever_database>.`tblusers` ADD COLUMN `availability` ...
ALTER TABLE <whatever_database>.`tblusers` ADD COLUMN `reliability` INTEGER ...
There are many more lines in this script and I'd like to automate it in a loop of current databases to be updated. I've got the list and the loop down using cursors, but here's where I'm running into trouble:
此脚本中有更多行，我想在要更新的当前数据库循环中自动执行它。我已经使用游标获得了列表和循环，但这是我遇到麻烦的地方：
When I'm on a particular database in the cursor, that database name is in a variable. I can't just say ALTER TABLE curschema.tblusersbecause the script complains that there is no database named curschema (the name of the variable containing the database name that I'd like to run operations on). I've been able to work around this by creating and executing a statement using parameters:
当我在游标中的特定数据库上时，该数据库名称在一个变量中。我不能只说 ALTER TABLE curschema。tblusers因为脚本抱怨没有名为 curschema 的数据库（包含我想要运行操作的数据库名称的变量名称）。我已经能够通过使用参数创建和执行语句来解决这个问题：
SET @curschema = curschema;
SET @query = NULL;
SET @email = emailAddress;
SET @pass = pass;
SET @statement = CONCAT('SELECT userid INTO @query FROM ', @curschema, '.tbluser
PREPARE stmt FROM @statement;
EXECUTE stmt;
The problem is, setting up executable strings (like above) will become an extremely tedious task with the dozens of lines of code that I have to run. I was hoping that there was a way that I could set the current database for operations and then just reset that database each loop pass so my generic statements might be run:
问题是，设置可执行字符串（如上）将成为一项极其繁琐的任务，我必须运行数十行代码。我希望有一种方法可以为操作设置当前数据库，然后在每次循环通过时重置该数据库，以便可以运行我的通用语句：
(start of my loop)
（我的循环开始）
SET DATABASE database0 -- (through to database29)
SET DATABASE database0 --（到database29）
-- run statements with database0 .... 29 being implied with the above command
-- 使用 database0 运行语句 .... 29 隐含在上述命令中
ALTER TABLE `tblusers` ADD COLUMN `availability` TINYINT(1) UNSIGNED ...
ALTER TABLE `tblusers` ADD COLUMN `reliability` INTEGER UNSIGNED NOT ...
(end of my loop)
（我的循环结束）
Does such a command exist to set the current database on which operations may be performed? If no such command exists, is there an easier way to accomplish my task? I figure that at this juncture it's just easier to do a find/replace on all the database names 30 times than it is to rewrite my entire script file to be executable/parameter-ized strings.
是否存在这样的命令来设置可以在其上执行操作的当前数据库？如果不存在这样的命令，是否有更简单的方法来完成我的任务？我认为此时对所有数据库名称进行 30 次查找/替换比将整个脚本文件重写为可执行/参数化字符串要容易得多。
Thanks!
谢谢！
回答by Jacob Relkin
Did you try USE?
你试了USE吗？
USE db_name
The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued.
USE db_name 语句告诉 MySQL 使用 db_name 数据库作为后续语句的默认（当前）数据库。数据库将保持默认状态，直到会话结束或发出另一个 USE 语句。
Example:
例子：
USE db1;
SELECT COUNT(*) FROM mytable; # selects from db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable; # selects from db2.mytable
