-- 創(chuàng)建數(shù)據(jù)庫(kù)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 '合同編號(hào)',

? `deleted` tinyint NOT NULL DEFAULT 0 COMMENT '是否刪除 0 未刪除 1 刪除 默認(rèn)是0',
? `create_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '創(chuàng)建人賬號(hào)id',
? `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
? `update_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '更新人賬號(hào)id',
? `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP?? ?COMMENT '更新時(shí)間',
?? ?
? 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)建數(shù)據(jù)庫(kù)template_user
CREATE DATABASE `template_user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 模板設(shè)置表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 刪除 默認(rèn)是0',
? `create_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '創(chuàng)建人賬號(hào)id',
? `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
? `update_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '更新人賬號(hào)id',
? `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP?? ?COMMENT '更新時(shí)間',
?? ?
? 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腳本，這里會(huì)有告警，不影響執(zhí)行，提示直接輸入密碼不安全，去掉-p后的spwd，執(zhí)行時(shí)輸入安全
# 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 #如果沒有指定數(shù)據(jù)庫(kù)

?? ??? ??? ??? ?read -p "請(qǐng)輸入數(shù)據(jù)庫(kù)名：" db_name

?? ??? ??? ??? ?read -p "你輸入的數(shù)據(jù)名是【$db_name】，確認(rèn)繼續(xù)請(qǐng)輸入--yes--: " confirm

?? ??? ??? ?fi

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

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

?? ??? ??? ??? ??? ?stty -echo #密碼輸入保護(hù)關(guān)閉顯示

?? ??? ??? ??? ??? ?read -p "請(qǐng)輸入數(shù)據(jù)庫(kù)密碼：" 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) #讀取錯(cuò)誤日志信息

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

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

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

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

?? ??? ??? ?else

?? ??? ??? ??? ?echo "您已經(jīng)取消操作!"

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

?? ??? ??? ?fi

?? ??? ?fi

?? ?fi

done