-- 創(chuàng)建數據庫contract_user
CREATE DATABASE `contract_user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 創(chuàng)建合同表contract
DROP TABLE IF EXISTS `contract`;
CREATE TABLE `contract` ?(
? `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID主鍵',
? `name` varchar(64) NOT NULL COMMENT '合同名稱',
? `code` varchar(64) NOT NULL COMMENT '合同編號',

? `deleted` tinyint NOT NULL DEFAULT 0 COMMENT '是否刪除 0 未刪除 1 刪除 默認是0',
? `create_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '創(chuàng)建人賬號id',
? `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
? `update_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '更新人賬號id',
? `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP?? ?COMMENT '更新時間',
?? ?
? PRIMARY KEY (`id`) USING BTREE,
? index `idx_code_name`(`code`,`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '合同表' ROW_FORMAT = Dynamic;

-- 創(chuàng)建數據庫template_user
CREATE DATABASE `template_user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 模板設置表template
DROP TABLE IF EXISTS `template`;
CREATE TABLE `template` ?(
? `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID主鍵',
? `name` bigint(20) NOT NULL COMMENT '模板名稱',
? `code` bigint(20) NOT NULL COMMENT '模板編碼',

? `deleted` tinyint NOT NULL DEFAULT 0 COMMENT '是否刪除 0 未刪除 1 刪除 默認是0',
? `create_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '創(chuàng)建人賬號id',
? `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
? `update_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '更新人賬號id',
? `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP?? ?COMMENT '更新時間',
?? ?
? PRIMARY KEY (`id`) USING BTREE,
? index `idx_code_name`(`code`, `name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '模板表' 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，sql放入ddl下
sqlpath="source /home/ddl/"
sqlsource="${sqlpath}contract_ddl.sql;${sqlpath}template_ddl.sql;"

# 執(zhí)行sql腳本，這里會有告警，不影響執(zhí)行，提示直接輸入密碼不安全，去掉-p后的spwd，執(zhí)行時輸入安全
# 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 #指定的目錄

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 #如果沒有指定數據庫

?? ??? ??? ??? ?read -p "請輸入數據庫名：" db_name

?? ??? ??? ??? ?read -p "你輸入的數據名是【$db_name】，確認繼續(xù)請輸入--yes--: " confirm

?? ??? ??? ?fi

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

?? ??? ??? ??? ?if [ ! $db_pass ]; then #如果沒有設置密碼

?? ??? ??? ??? ??? ?stty -echo #密碼輸入保護關閉顯示

?? ??? ??? ??? ??? ?read -p "請輸入數據庫密碼：" db_pass

?? ??? ??? ??? ??? ?echo -e "\n"

?? ??? ??? ??? ??? ?stty echo

?? ??? ??? ??? ?fi

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

?? ??? ??? ??? ?echo $file

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

?? ??? ??? ??? ?cat error.log >>${LOG_FILE} ? #輸出執(zhí)行日志

?? ??? ??? ??? ?error=$(grep ERROR error.log) #讀取錯誤日志信息

?? ??? ??? ??? ?if [ -n "$error" ]; then #如果有錯誤就退出程序

?? ??? ??? ??? ??? ?echo $error

?? ??? ??? ??? ??? ?exit

?? ??? ??? ??? ?fi

?? ??? ??? ?else

?? ??? ??? ??? ?echo "您已經取消操作!"

?? ??? ??? ??? ?exit

?? ??? ??? ?fi

?? ??? ?fi

?? ?fi

done