mysql批量操作shell脚本

这两天写了个mysql操作的shell脚本，主要用于mysql数据到内存数据库的批量transfer。

当然适当修改可用于构造测试数据等~

 

#!/bin/sh

 

if [ $# -ne 6 ]

then

echo "usage:"

echo "$0 {db_user} {db_passwd} {mdb_table} {tablenum} {mdbfile_dir} {odbc_conn}"

echo "eg: ./route_transfer.sh wqj 123 SYS_RT_ACCOUNT 10 $OB_REL/mdbroute wqj/123@dsn_name"

echo "      "

exit

fi

 

SCHEMA="zd"

USER=$1

PASSWD=$2

TABLE_NAME=$3

TABLE_NUM=$4

MDB_DIR=$5

DB_CONN=$6

 

DB_IP="127.0.0.1"

DB_PORT="3306"

 

echo "Connect to db ${DB_IP}:${DB_PORT} identified by ${USER} ${PASSWD}"

CONNECT_MYSQL="mysql -h ${DB_IP} -P ${DB_PORT} -u${USER} -p${PASSWD}"

 

SETUTF8="set names utf8;"

 

VALIDDATEFMT="UNIX_TIMESTAMP((CASE WHEN 

ifnull(valid_date, date_format('19700101080000', '%Y%m%d%H%i%s')) > date_format('20371231235959', '%Y%m%d%H%i%s')

   THEN 

              date_format('20371231235959', '%Y%m%d%H%i%s') 

             ELSE 

          date_format(ifnull(valid_date, date_format('19700101080000', '%Y%m%d%H%i%s')),'%Y%m%d%H%i%s') 

                    END)) as aimdb_valid_date"

#echo ${VALIDDATEFMT}

 

EXPIREDATEFMT="UNIX_TIMESTAMP((CASE WHEN

                        ifnull(expire_date, date_format('19700101080000', '%Y%m%d%H%i%s')) > date_format('20371231235959', '%Y%m%d%H%i%s')

                    THEN

                        date_format('20371231235959', '%Y%m%d%H%i%s')

                    ELSE

                        date_format(ifnull(expire_date, date_format('20371231235959', '%Y%m%d%H%i%s')),'%Y%m%d%H%i%s')

                    END)) as aimdb_expire_date"

#echo ${EXPIREDATEFMT}

 

########################SYS_RT_ACCOUNT begin#############################

 

DST_ACCT_TABLE="sys_rt_account"

SRC_ACCT_TABLE_PREFIX="ad.ca_account"

 

ACCT_TABLE_DROP="drop table if exists ${SCHEMA}.${DST_ACCT_TABLE};"

ACCT_TABLE_CREATE="create table ${SCHEMA}.${DST_ACCT_TABLE} as "

ACCT_SELECT_BASE="select CAST(XXX AS UNSIGNED) as XXX_id, CAST(XXXX as UNSIGNED) as XXXX_id, \

                  CAST(0 as SIGNED) as XXXX_code, ${VALIDDATEFMT}, ${EXPIREDATEFMT} from ${SRC_ACCT_TABLE_PREFIX}"

ACCT_TABLE_SHOW="select * from ${SCHEMA}.${DST_ACCT_TABLE};"

 

tableIdx=0

ACCT_TABLE_SELECT=${ACCT_SELECT_BASE}

while [ $tableIdx -lt $(($TABLE_NUM - 1)) ]

do

#     echo ${tableIdx}

    ACCT_TABLE_SELECT="${ACCT_TABLE_SELECT}_${tableIdx} union all ${ACCT_SELECT_BASE}"

# echo ${ACCT_TABLE_SELECT}

    tableIdx=$(( $tableIdx + 1 ))

done

ACCT_TABLE_SELECT="${ACCT_TABLE_SELECT}_${tableIdx};"

 

 

#echo ${ACCT_TABLE_CREATE} ${ACCT_TABLE_SELECT}

 

######create table and dump to mdb file#####

if [ $TABLE_NAME = "SYS_RT_ACCOUNT" ]

then

${CONNECT_MYSQL} << EOF

${SETUTF8}

${ACCT_TABLE_DROP}

${ACCT_TABLE_CREATE} ${ACCT_TABLE_SELECT}

${ACCT_TABLE_SHOW}

EOF

echo "the table SYS_RT_ACCOUNT create end"

 

MDBFILE="ROUTE_CSysRtAccount.mdb"

echo "mdb_transfer ${MDB_DIR}/${MDBFILE}   ${SCHEMA}.${DST_ACCT_TABLE}   ${DB_CONN}  10 import"

mdb_transfer ${MDB_DIR}/${MDBFILE} ${SCHEMA}.${DST_ACCT_TABLE} ${DB_CONN} 10 import

echo "the table SYS_RT_ACCOUNT dump end"

fi

 

########################SYS_RT_ACCOUNT end #############################