MySQL Shell登录、远程操作、批量操作

MySQL TimeStamp处理, MySQL读取文件批量操作, 自定义的MySQL命令

1. 登录和执行MySQL command的脚本

lz工作中有多个测试环境数据库，一个线上环境数据库, 经常需要登录各个环境进行操作. 为简化日常工作, 写一个shell脚本, 并且配置到环境变量中, 这样可以极大简化日常数据库操作.

  • 脚本如下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    #!/bin/bash

    #登录函数
    logindb(){
    	echo 登录MySQL@$1
    	if [[ "sand" == $1 ]]
    	then
    		mysql -hsand.com -P3306 -uxxx -pxxxxxx
    	else
    		mysql -h mysql.$1.com -uxxx -pxxxxxx 
    	fi
    }

    #执行命令函数
    execute(){
    	echo 执行MySQL命令"$2"@$1
    	if [[ "sandbox" == $1 ]]
    	then
    		mysql -hsand.com -P3306 -uxxx -pxxxxxx -e "${2}"
    	else
    		mysql -h mysql.$1.com -uxxx -pxxxxxx -e "${2}"
    	fi
    }


    #获取参数
    while getopts "c:h:" opt
    do
    	case $opt in
    	c)
    		command=$OPTARG
    		;;
    	h)	
    		host=$OPTARG
    		;;
    	?)
    		echo "Usage: logdb [-h host] [-c command]"
    		;;
    	esac
    done

    echo -n "*******************************************"

    #参数校验
    if [[ ! $host ]]
    then
    	echo "Usage: logdb [-h host] [-c command]"必须指定MySQL host！
    	exit 1 
    fi


    #根据command是否为空, 选择执行登录还是command
    if [[ $command ]]
    then
    	execute $host "$command"
    else
    	logindb $host
    fi

  • 使用方法:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    #将上述脚本配置到/usr/local/self/路径下面
    $ ls /usr/local/self/
    logdb

    #在/usr/local/bin路径下面创建一个soft link到上述脚本
    $ ls -l /usr/local/bin/logdb
    lrwxr-xr-x  1 admin  21  5 31 18:55 logdb@ -> /usr/local/self/logdb

    #然后就可以直接在终端上使用logdb命令了, 例如: 本地直接执行sql查询命令
    $ logdb -h sand -c "show databases"
    **********************************执行MySQL命令show databases@sandbox

    #或者直接登录到数据库host
    $ logdb -h sand
    **********************************登录MySQL@sandbox

2. MySQL Timestamp常用操作

2.1 计算时间差

1
select id,timestampdiff(second,start_time,end_time) from order;

2.2 按照日期查询

1
2
3
4
5
6
7
8
9
#1. 这种方法要不断执行date_format函数, 效率不高
select * from order where date_format(end_time,'%y-%m-%d')='2018-06-05'

#2. 或者to_days(end_time)
select * from order where to_days(end_time)=to_days('2018-06-05 12:00:00')


#3. 应该使用下面的方法
select * from order where end_time between  '2018-07-01 00:00:00' and '2018-07-01 23:59:59'

2.3 自定义参数查&查询时间统计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#!/bin/bash

start=$(date '+%s')

totalCount=$1
count=$2

sql="select order,id,starttime,endtime,timestampdiff(second,starttime,endtime) as totalTime\
	from order.orders where endtime between '2018-07-02 00:00:00' and '2018-07-02 23:59:59'\
	order by endtime asc limit $count offset"


host="xxxx"
port=3306
user="xxx"
password="xxxxxx"

for((i=0;i<$totalCount;i=i+$count))
do
	#拼接字符串
	querys=${sql}" "${i}
	mysql -h${host} -P${port} -u${user} -p${password} -e"${querys}"
done
end=$(date "+%s")

echo "StartTime is (秒): $start"
echo "  EndTime is (秒): $end"
echo "TotalTime is (秒): $((end-start))"

2.4 修改日期

1
update order set end_time=date_format(concat('2018-06-25 ',time(end_time)),'%Y-%m-%d %H:%i:%S') where id='33523250'

3. 读取文件批量操作

3.1 从文件中读取id, 然后组装查询SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#!/bin/bash

start=$(date '+%s')

#校验文件
if [ ! -f $1 ]
then
	echo $1 is not a file! Usage: Get.sh filename
	exit 1
fi

ids="("
#从文件读取所有id，拼装成一个string
for line in `cat $1`
do
	ids=${ids}"'"${line}"',"
done

host="mysql-host"
port=3306
user="mysql-user"
password="xxxxxxx"

#${ids%?}可以去掉最后一个多余的逗号
query="select id, type from order.orders where id in "${ids%?}");"

echo $query


mysql -h${host} -P${port} -u${user} -p${password} -e"${query}"

end=$(date "+%s")

echo "StartTime is (秒): $start"
echo "  EndTime is (秒): $end"
echo "TotalTime is (秒): $((end-start))"

4. Shell脚本传递参数:getopts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/bin/bash

#f后面带有一个":", 表示-f选项需要一个参数value, 后面没有":"表示不需要传递参数(给了也不会识别到)
#参考sort命令的说明，sort -k后面必须传递参数value, 相对而言, sort -n就无需给它传递参数了
#sort [-bcCdfghiRMmnrsuVz] [-k field1[,field2]] [-S memsize] [-T dir] [-t char] [-o output] [file ...]
while getopts "abf:v" opt
do
	case $opt in
		a)
		echo "参数选项a : -$opt=$OPTARG"
		;;
		b)
		echo "参数选项b : -$opt=$OPTARG"
		;;
		f)
		echo "参数选项f : -$opt=$OPTARG"
		;;
		v)
		echo "参数选项v : -$opt=$OPTARG"
		;;
		?)
		echo "默认参数选项"?" Invalid Option : $OPTARG"
		echo "Usage: args [-a] [-b] [f] [-v]"
	esac
done