Mysql大表的水平切分

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

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

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!