linux shell脚本执行sql语句建表建库
1. 创建sql脚本
创建contract_ddl.sql
-- 创建数据库contract_user
CREATE DATABASE `contract_user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 创建合同表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 '创建人账号id',
`create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '更新人账号id',
`update_time` timestamp(0) NOT NULL DEFAULT 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;
创建template_ddl.sql
-- 创建数据库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 '创建人账号id',
`create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '更新人账号id',
`update_time` timestamp(0) NOT NULL DEFAULT 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;
2. 创建shll脚本
#!/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;"
# 执行sql脚本,这里会有告警,不影响执行,提示直接输入密码不安全,去掉-p后的spwd,执行时输入安全
# 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..."