Shell script batch execution of SQL script implementation under specified path

-- Create a databasecontract_user
CREATE DATABASE `contract_user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- Create a contract tablecontract
DROP TABLE IF EXISTS `contract`;
CREATE TABLE `contract`  (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID primary key',
  `name` varchar(64) NOT NULL COMMENT 'Contract Name',
  `code` varchar(64) NOT NULL COMMENT 'Contract Number',

  `deleted` tinyint NOT NULL DEFAULT 0 COMMENT 'Did you delete 0 Not deleted 1 Delete Default is 0',
  `create_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Creator account id',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
  `update_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Update account id',
  `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP    COMMENT 'Update time',
    
  PRIMARY KEY (`id`) USING BTREE,
  index `idx_code_name`(`code`,`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'Contract Table' ROW_FORMAT = Dynamic;

-- Create a databasetemplate_user
CREATE DATABASE `template_user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- Template Settings Tabletemplate
DROP TABLE IF EXISTS `template`;
CREATE TABLE `template`  (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID primary key',
  `name` bigint(20) NOT NULL COMMENT 'Template name',
  `code` bigint(20) NOT NULL COMMENT 'Template encoding',

  `deleted` tinyint NOT NULL DEFAULT 0 COMMENT 'Did you delete 0 Not deleted 1 Delete Default is 0',
  `create_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Creator account id',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
  `update_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Update account id',
  `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP    COMMENT 'Update time',
    
  PRIMARY KEY (`id`) USING BTREE,
  index `idx_code_name`(`code`, `name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'Template Table' ROW_FORMAT = Dynamic;

#!/bin/sh

start_date=`date '+%Y%m%d-%H%M%S'`
echo $start_date ${USER} "execute ddl start..."

# mysql
shost=127.0.0.1
sport=3306
suser=root
spwd=123456

# path, put SQL into ddlsqlpath="source /home/ddl/"
sqlsource="${sqlpath}contract_ddl.sql;${sqlpath}template_ddl.sql;"

# Execute the SQL script, there will be an alarm here, which will not affect execution. It is not safe to enter the password directly. Remove the spwd after -p, and enter it safe when executing.# Warning: Using a password on the command line interface can be insecure.
mysql -h$shost -p$sport -u$suser -p$spwd -e"$sqlsource"

# end
end_date=`date '+%Y%m%d-%H%M%S'`
echo $end_date ${USER} "execute ddl end..."

#!/bin/bash

#execute all script in specified directory

MYDATE=$(date +%F'-'%T'-'%w)

MYSQL_PATH=/tmp/scripts #Specified directory
LOG_FILE=/tmp/scripts/exec_${MYDATE}.log

confirm=

db_name=

db_pass=

for file in ${MYSQL_PATH}/*; do

    if [ -f "$file" ]; then

        postfix=$(echo $file | awk -F'.' '{print "."$NF}')

        if [ $postfix = ".sql" ]; then

            if [ ! $db_name ]; then #If no database is specified
                read -p "Please enter the database name:" db_name

                read -p "The data name you entered is [$db_name]. Please enter --yes--: " confirm

            fi

            if [ "$confirm" = "yes" ] && [ -n $confirm ]; then

                if [ ! $db_pass ]; then #If there is no password set
                    stty -echo #Password input protection to turn off display
                    read -p "Please enter the database password:" db_pass

                    echo -e "\n"

                    stty echo

                fi

                mysql -uroot -p$db_pass -P3306 --default-character-set=utf8 ${db_name} <$file >&

                echo $file

                echo -e "\n===========$file=============\n" >>${LOG_FILE}

                cat  >>${LOG_FILE}   #Output execution log
                error=$(grep ERROR ) #Read error log information
                if [ -n "$error" ]; then #If there is an error, exit the program
                    echo $error

                    exit

                fi

            else

                echo "You have cancelled the operation!"

                exit

            fi

        fi

    fi

done