I know that with mysql you can write SQL statements into a .sql file and run the file from the mysql command line like this:
我知道使用mysql你可以将SQL语句写入.sql文件并从mysql命令行运行该文件,如下所示:
mysql> source script.sql
How do I pass a variable to the script? For example, if I want to run a script that retrieves all the employees in a department, I want to be able to pass in the number of the department as a variable.
如何将变量传递给脚本?例如,如果我想运行一个检索部门中所有员工的脚本,我希望能够将部门的编号作为变量传递。
I am not trying to run queries through a shell script. There are simple queries I run from the mysql command line. I'm tired of retyping them all the time, and writing a shell script for them would be overkill.
我不是试图通过shell脚本运行查询。我从mysql命令行运行简单的查询。我已经厌倦了一直重新输入它们,为它们编写一个shell脚本会有点过分。
3 个解决方案
#1
17
Like this:
喜欢这个:
set @department := 'Engineering';
Then, reference @department
wherever you need to in script.sql:
然后,在script.sql中的任何地方引用@department:
update employee set salary = salary + 10000 where department = @department;
#2
10
#!/bin/bash
#verify the passed params
echo 1 cmd arg : $1
echo 2 cmd arg : $2
export db=$1
export tbl=$2
#set the params ... Note the quotes ( needed for non-numeric values )
mysql -uroot -pMySecretPaassword \
-e "set @db='${db}';set @tbl='${tbl}';source run.sql ;" ;
#usage: bash run.sh isg_pub_en MetaEntity
#
#eof file: run.sh
--file:run.sql
SET @query = CONCAT('Select * FROM ', @db , '.' , @tbl ) ;
SELECT 'RUNNING THE FOLLOWING query : ' , @query ;
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
--eof file: run.sql
you can re-use the whole concept from from the following project
您可以从以下项目中重用整个概念
#3
-5
you really should be looking at a more appropriate way of doing this. i'm going to guess that you're trying to run mysql queries via a shell script. you should instead be using something like PERL or PHP.
你真的应该考虑一种更合适的方式。我猜你正试图通过shell脚本运行mysql查询。你应该使用像PERL或PHP这样的东西。
#1
17
Like this:
喜欢这个:
set @department := 'Engineering';
Then, reference @department
wherever you need to in script.sql:
然后,在script.sql中的任何地方引用@department:
update employee set salary = salary + 10000 where department = @department;
#2
10
#!/bin/bash
#verify the passed params
echo 1 cmd arg : $1
echo 2 cmd arg : $2
export db=$1
export tbl=$2
#set the params ... Note the quotes ( needed for non-numeric values )
mysql -uroot -pMySecretPaassword \
-e "set @db='${db}';set @tbl='${tbl}';source run.sql ;" ;
#usage: bash run.sh isg_pub_en MetaEntity
#
#eof file: run.sh
--file:run.sql
SET @query = CONCAT('Select * FROM ', @db , '.' , @tbl ) ;
SELECT 'RUNNING THE FOLLOWING query : ' , @query ;
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
--eof file: run.sql
you can re-use the whole concept from from the following project
您可以从以下项目中重用整个概念
#3
-5
you really should be looking at a more appropriate way of doing this. i'm going to guess that you're trying to run mysql queries via a shell script. you should instead be using something like PERL or PHP.
你真的应该考虑一种更合适的方式。我猜你正试图通过shell脚本运行mysql查询。你应该使用像PERL或PHP这样的东西。