mysql stored procedure versus script for mysql command line tool

I have to do a lot of batch calculations , filtering data, making summary tables in MySql.

Should I use stored procedures or a bash script calling MySql command line?

They are about equivalent, as far as I can tell, but the advantage of the bash script is I could use shell functions as macros to re-run big statements with small changes.

By a bash script I mean:

#!/bin/bash
#
arg1=$1
arg2=$2

mysql user pw db << EOF > output.out

  load table ....
  update xx set y = $arg1 if ...
  insert into xxx select a, b, avg(c) from xx group by a, b ...
  quit
EOF

The only advantage I can see to a stored procedure is that it checks the types of values you give its arguments. But a shell script would be much more powerful for re-using code. i could pass in a table name as an argument, or change a Where clause from

where name < 'Run14' 

to

where name regexp 'RunZ23..[A-M]' OR name = 'Run14'

by passing it as an argument.

Performance is not a problem.

Thanks tt

---

Added years l开发者_运维百科ater:

This is somewhat similar to the question MySQL stored procedures or php code? . Though there is disagreement, a lot of people preferred PHP and I feel the reasons given in Who Needs Stored Procedures, Anyways? are good ones. Now I prefer PHP functions instead of MySql stored procs. Of course PHP is better for programming than bash. The main advantage I see to Stored Procs is speed, and that is not a concern for me most of the time.

On Jul 11, 2012 I commented:

I see now a big advantage of stored procedures is they support IF, CASE, and CURSOR LOOP statements.

I meant: you can do these things in Bash, but you have to exit MySql, get its information into a shell variable, make your decision, and start MySql again to process further.

I don't think it is such an advantage if you use PHP, because in PHP it is easier to get SQL results into variables and even arrays, and you do not have to open a whole new session for each statement.

However it can be much faster to do many SQL statements in a procedure than from Bash or PHP, especially when there is looping.

---

If I'm going to do some work in the database and that database provides procedural programming constructs, I'll put the procedural code in the database.

The code will be robust (the database offers the right tools for the job - constraints, automated error handling etc.), it will be more efficient - fewer context switches between the MySQL engine and the host, once created the procedure will be compiled and will perform better than "uncompiled" SQL statements, it will be easy to invoke that procedure from within the database (and from your application), the database will offer a different (and probably more flexible) security.

I really don't believe that variable parameters will be an issue :)