将参数传递给 MySQL 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14098222/
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
Pass parameters to MySQL script
提问by decapo
I have a MySQL script file named query1.sqlwhich contains:
我有一个名为的 MySQL 脚本文件query1.sql，其中包含：
select * FROM $(tblName) LIMIT 10;
I am in MySQL console, how do I pass the parameter to the script? This does not forward the variable:
我在 MySQL 控制台中，如何将参数传递给脚本？这不会转发变量：
mysql> \. query1.sql -v tblName=Users
采纳答案by MvG
You can use user variablesto achieve the behaviour you describe. As you use the variable as a schema identifier, not a data value, you'll have to use a prepared statement so you can compose the query dynamically.
您可以使用用户变量来实现您所描述的行为。当您将变量用作模式标识符而不是数据值时，您必须使用准备好的语句，以便您可以动态地编写查询。
query1.sql:
query1.sql：
SET @query = CONCAT('Select * FROM ', @tblName, ' LIMIT 10');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Invoked as
调用为
mysql> SET @tblName = 'Users'; \. query1.sql
回答by user4150422
The command line call from vidyadhar worked for me, but only with a small modification :
来自 vidyadhar 的命令行调用对我有用，但只有一个小的修改：
mysql -u root -p -e"set @temp=1; `cat /home/mysql/Desktop/a.sql`"
回答by vidyadhar
This may work for you
这可能对你有用
mysql -u root -p -e"set @temp=1;" < /home/mysql/Desktop/a.sql
and
和
mysql> set @temp=some_value;
mysql> source file.sql
this almost similar to your problem just try it
这几乎与您的问题相似，请尝试一下
回答by Dave P
The answer from @user4150422 above almost worked from me, but catgave me permission issues so I had to use a slight alternative. Weirdly sourcecommand didn't work in place of cat either, but \.did
上面@user4150422 的答案几乎对我有用，但cat给了我权限问题，所以我不得不使用一个轻微的替代方法。古怪的source命令没有到位猫的工作，要么，但\.没
mysql -u root -p -e"set @temp=1; `\. /home/mysql/Desktop/a.sql`"
回答by Chris F Carroll
For bash scripting:
对于 bash 脚本：
tblName=
searchFor="%something%"
echo "select * FROM $tblName LIMIT 10;
select * From $tblName where somecolumn like '$searchFor';
" | mysql
For powershell:
对于 PowerShell：
$tblName="MyTable"
$searchFor="%something%"
"select * FROM $tblName LIMIT 10;
select * From $tblName where somecolumn like '$searchFor';
" | mysql
回答by Almaz Gareev
The advanced solution for use bash variables via mysql variables like @tblNamein order to INSERT values into specific table:
通过 mysql 变量使用 bash 变量的高级解决方案，例如@tblName将值插入特定表：
The core file insert.preps included prepared statementsprocedural (SET > PREPARE > EXECUTE > DEALLOCATE):
核心文件 insert.preps 包括准备好的语句程序（SET > PREPARE > EXECUTE > DEALLOCATE）：
SET @query = CONCAT("INSERT INTO ", @tblName, " (id, time, file, status) VALUES (?, ?, ?, ?)");
PREPARE stmt FROM @query;
SET @id = '0';
EXECUTE stmt USING @id, @time, @file, @status;
DEALLOCATE PREPARE stmt;
*SET @id = '0';in the file predefined because idis usually is INT AUTO_INCREMENT PRIMARY KEY. It will auto-assign proper idfor all lines.
*SET @id = '0';在预定义的文件中，因为id通常是INT AUTO_INCREMENT PRIMARY KEY. 它将id为所有行自动分配正确的。
Invoking it in the shell or in bash script with using variables:
在 shell 或 bash 脚本中使用变量调用它：
mysql --defaults-extra-file=<(echo $'[client]\npassword='"$db_password") --user=$db_user $db_name -e "
SET @tblName = '$table';
SET @time = '$timeStamp';
SET @file = '$file';
SET @status = '$status';
`cat /path/to/insert.preps`"
