- #!/bin/bash
- user=""
- password=""
- database=""
- table=""
- rows=$(mysql -u$user -p$password $database -e "select DISTINCT EXTRACT(YEAR_MONTH from (ActionTime)) as 'month' from $table;")
- #echo $rows
- table_name=''
- for tag in ${rows[@]}
- do
- #echo "$tag"
- if [ $tag = "month" ];then
- continue
- fi
- table_name="$table_name""$table""_""$tag"" ";
- done
- echo "${table_name}"
- for table_i in ${table_name[@]}
- do
- mysql -u$user -p$password $database << EOF 2>/dev/null
- CREATE TABLE $table_i(
- 表结构
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- EOF
- [ $? -eq 0 ] && echo "Created table $table_i" || echo "Table $table_i already exist"
- temp=${table_i##*_}
- startdate=${temp:0:4}'-'${temp:4}'-01'
- enddate=${temp:0:4}'-'${temp:4}'-31'
- echo "startdate=${startdate}, enddate=${enddate}"
- total_rows=$(mysql -u$user -p$password $database -N -e "select count(*) from $table where ActionTime BETWEEN '$startdate' and '$enddate';")
- echo '总行数='"${total_rows}"
- page_size=1000
- page_num=0
- mod=`expr $total_rows % $page_size`
- echo '取摸='"${mod}"
- if [ $mod -gt 1 ];then
- page_num=`expr $total_rows / $page_size + 1`
- else
- page_num=`expr $total_rows / $page_size`
- fi
- echo '总页数='"${page_num}"
- add_size=0
- for((i=0;i<$page_num;i++))
- do
- mysql -r$root -p$password $database << EOF 2>/dev/null
- INSERT INTO $table_i SELECT * FROM PageView WHERE $table.ActionTime BETWEEN '$startdate' AND '$enddate' LIMIT $add_size,$page_size;
- EOF
- add_size=`expr $add_size + $page_size`
- done
- done
- echo "sharding $table successful!"
- #该片段来自于http://www.codesnippet.cn/detail/2101201511580.html
来源: http://www.codesnippet.cn/detail/2101201511580.html