Loop through result set of mysql in Bash - routines

xesrikrc 于 2022-12-22 发布在 Mysql

关注(0)|答案(2)|浏览(131)

I want to make specific .sql file for each of my routines.

#!/bin/bash

 routine_names=$(mysql mydb  --execute="SELECT 
    *
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE' OR routine_type ='FUNCTION'
        AND routine_schema = 'mydb';"|cut -f1)

for routine in "$routine_names"
do
    if [ -e "${routine}.sql" ]
     then
    echo "ok"
    else
     content_procedure=$(mysql mydb--execute="SHOW CREATE PROCEDURE $routine;")
     echo "$content_procedure" >> masoud.txt
   fi   
done

my routine_names variable is a list of my procedures. like this:

SP_ONE
SP_TWO
SP_THREE

I want to loop of these result. but I think the result is not an array. because routine variable has all content.

mysql

来源：https://stackoverflow.com/questions/60257939/loop-through-result-set-of-mysql-in-bash-routines

2条答案

按热度按时间

wko9yo5t1#

wrap your mysql mydb ... command with ()

routine_names=($(mysql mydb  --execute="SELECT 
    *
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE' OR routine_type ='FUNCTION'
        AND routine_schema = 'mydb';"|cut -f1))

I was curious and got the idea from here .

赞(0）回复(0）举报 2022-12-22

xqkwcwgp2#

Just another case how I fix my problem

#!/bin/bash
mysql -u USER -p -h localhost -D database1 -e "SELECT ID FROM prodTable WHERE display=1 AND new=1 AND exDate<DATE_SUB(CURDATE(),INTERVAL 2 YEAR)" | while read ID;
do 
      echo "The following product has been moved: $ID"
done

赞(0）回复(0）举报 2022-12-22

我来回答

Loop through result set of mysql in Bash - routines

2条答案

相关问题

热门标签

最新问答