mysql 切表_Mysql大表的水平切分

#!/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

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!"