用cobar搭建分布式数据库

来源：互联网发布：网络直播平台搭建编辑：程序博客网时间：2024/05/01 20:13

周末针对最新的项目需求进行cobar的搭建并针对实际状况做了demo演示

一、需求

1、大数据量，邮件发送记录需要记录，一年可能累计4亿的数据

2、需要按照邮箱进行邮件发送明细的查询以及发送记录的查询

二、问题

1、单库分表分区已经解决不了存储以及查询问题

2、只能进行一致性hash的策略进行数据存储以及查询，放弃了一些便利性查询统计

三、实施方案，数据库采用mysql，使用cobar针对分区库和主库进行整合

整合步骤如图，看不清可以下载附件看

四、相关配置文件以及工具文件如下

1、操作指南：edm-cobar操作指南.txt

1、mysql -h192.168.20.65 -uroot -p1q2w3e -P80662、show databases;3、use edm;4、show tables;note:1、cobar server作为代理，安装在20.65上，并且以root/1q2w3e为口令，8066为服务端口提供了数据库代理服务具体路径/usr/local/cobar***2、我们的数据库可以以这个代理服务器为访问端口，按照email这个字符串进行水平的hash路由到各个水平节点上3、对于不需要水平拆分的表，我们也可以直接通过20.65提供的这个edm服务访问warning：1、直接用sqlyog软件访问这个代理数据库可能会报错，因为获取数据问题。不过我们可以直接通过cmd来查询。2、我们的数据校验，可以通过直接访问各个节点进行数据校验

2、实施策略图片：EDM分布式数据库策略之Cobar.jpeg

3、cobar的相关配置文件：rule.xml,schema.xml,server.xml

<?xml version="1.0" encoding="UTF-8"?><!-- - Copyright 1999-2012 Alibaba Group. -   - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at -   -      http://www.apache.org/licenses/LICENSE-2.0 -   - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License.--><!DOCTYPE cobar:schema SYSTEM "schema.dtd"><cobar:schema xmlns:cobar="http://cobar.alibaba.com/">  <!-- schema定义 -->  <schema name="edm" dataNode="dnEdm">    <table name="email_info" dataNode="dn_shardx$0-7,dn_shardy$0-7" rule="email_rule" /><table name="email_info_detail" dataNode="dn_shardx$0-7,dn_shardy$0-7" rule="email_rule" />  </schema>  <!-- 数据节点定义，数据节点由数据源和其他一些参数组织而成。-->  <dataNode name="dnEdm">    <property name="dataSource">      <dataSourceRef>db_edmx[0]</dataSourceRef>    </property>  </dataNode>  <dataNode name="dn_shardx">    <property name="dataSource">      <dataSourceRef>db_edmx$1-8</dataSourceRef>    </property>  </dataNode>  <dataNode name="dn_shardy">    <property name="dataSource">      <dataSourceRef>db_edmy$0-7</dataSourceRef>    </property>  </dataNode>  <!-- 数据源定义，数据源是一个具体的后端数据连接的表示。-->  <dataSource name="db_edmx" type="mysql">    <property name="location">  <!--edm  总库-->  <location>192.168.20.182:3306/edm_hd</location>      <location>192.168.20.182:3306/edmshard$1-8</location>    </property>    <property name="user">root</property>    <property name="password">your_password</property>    <property name="sqlMode">STRICT_TRANS_TABLES</property>  </dataSource>  <dataSource name="db_edmy" type="mysql">    <property name="location">      <location>192.168.119.158:3306/edmshard$9-16</location>    </property>    <property name="user">root</property>    <property name="password">your_password</property>    <property name="sqlMode">STRICT_TRANS_TABLES</property>  </dataSource></cobar:schema>

<!DOCTYPE cobar:rule SYSTEM "rule.dtd"><cobar:rule xmlns:cobar="http://cobar.alibaba.com/">  <!-- 路由规则定义，定义什么表，什么字段，采用什么路由算法 -->  <tableRule name="email_rule">    <rule>      <columns>email</columns>      <algorithm><![CDATA[ efunc(${email}) ]]></algorithm>    </rule>  </tableRule>  <!-- 路由函数定义 -->  <function name="efunc" class="com.alibaba.cobar.route.function.PartitionByString">    <property name="partitionCount">16</property>    <property name="partitionLength">64</property>  </function></cobar:rule>

<?xml version="1.0" encoding="UTF-8"?><!-- - Copyright 1999-2012 Alibaba Group. -   - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at -   -      http://www.apache.org/licenses/LICENSE-2.0 -   - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License.--><!DOCTYPE cobar:server SYSTEM "server.dtd"><cobar:server xmlns:cobar="http://cobar.alibaba.com/">    <!-- 系统参数定义，服务端口、管理端口，处理器个数、线程池等。 -->  <!--  <system>    <property name="serverPort">8066</property>    <property name="managerPort">9066</property>    <property name="initExecutor">16</property>    <property name="timerExecutor">4</property>    <property name="managerExecutor">4</property>    <property name="processors">4</property>    <property name="processorHandler">8</property>    <property name="processorExecutor">8</property>    <property name="clusterHeartbeatUser">_HEARTBEAT_USER_</property>    <property name="clusterHeartbeatPass">_HEARTBEAT_PASS_</property>  </system>  -->  <!-- 用户访问定义，用户名、密码、schema等信息。 -->  <user name="root">    <property name="password">1q2w3e</property>    <property name="schemas">edm</property>  </user>  <!--  <user name="root">    <property name="password"></property>  </user>  -->  <!-- 集群列表定义，指定集群节点的主机和权重，用于集群间的心跳和客户端负载均衡。 -->  <!--   <cluster>    <node name="cobar1">      <property name="host">127.0.0.1</property>      <property name="weight">1</property>    </node>  </cluster>   -->     <!-- 隔离区定义，可以限定某个主机上只允许某个用户登录。 -->  <!--  <quarantine>    <host name="1.2.3.4">      <property name="user">test</property>    </host>  </quarantine>  --></cobar:server>

4、测试数据文件：edm_demo.sql

use edm;/**=====================在cobar server的schema(edm)的root账号下，直接清理数据=============================**/delete from edm_users;delete from email_info;delete from email_info_detail;/**======================在cobar server的schema(edm)的root账号下，直接写入非分区库edm_hd中的edm_users 100条数据============================**/use edm;insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd0','edm_hd0@hc360.com','psw0','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd1','edm_hd1@hc360.com','psw1','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd2','edm_hd2@hc360.com','psw2','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd3','edm_hd3@hc360.com','psw3','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd4','edm_hd4@hc360.com','psw4','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd5','edm_hd5@hc360.com','psw5','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd6','edm_hd6@hc360.com','psw6','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd7','edm_hd7@hc360.com','psw7','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd8','edm_hd8@hc360.com','psw8','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd9','edm_hd9@hc360.com','psw9','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd10','edm_hd10@hc360.com','psw10','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd11','edm_hd11@hc360.com','psw11','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd12','edm_hd12@hc360.com','psw12','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd13','edm_hd13@hc360.com','psw13','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd14','edm_hd14@hc360.com','psw14','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd15','edm_hd15@hc360.com','psw15','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd16','edm_hd16@hc360.com','psw16','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd17','edm_hd17@hc360.com','psw17','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd18','edm_hd18@hc360.com','psw18','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd19','edm_hd19@hc360.com','psw19','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd20','edm_hd20@hc360.com','psw20','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd21','edm_hd21@hc360.com','psw21','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd22','edm_hd22@hc360.com','psw22','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd23','edm_hd23@hc360.com','psw23','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd24','edm_hd24@hc360.com','psw24','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd25','edm_hd25@hc360.com','psw25','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd26','edm_hd26@hc360.com','psw26','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd27','edm_hd27@hc360.com','psw27','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd28','edm_hd28@hc360.com','psw28','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd29','edm_hd29@hc360.com','psw29','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd30','edm_hd30@hc360.com','psw30','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd31','edm_hd31@hc360.com','psw31','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd32','edm_hd32@hc360.com','psw32','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd33','edm_hd33@hc360.com','psw33','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd34','edm_hd34@hc360.com','psw34','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd35','edm_hd35@hc360.com','psw35','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd36','edm_hd36@hc360.com','psw36','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd37','edm_hd37@hc360.com','psw37','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd38','edm_hd38@hc360.com','psw38','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd39','edm_hd39@hc360.com','psw39','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd40','edm_hd40@hc360.com','psw40','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd41','edm_hd41@hc360.com','psw41','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd42','edm_hd42@hc360.com','psw42','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd43','edm_hd43@hc360.com','psw43','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd44','edm_hd44@hc360.com','psw44','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd45','edm_hd45@hc360.com','psw45','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd46','edm_hd46@hc360.com','psw46','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd47','edm_hd47@hc360.com','psw47','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd48','edm_hd48@hc360.com','psw48','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd49','edm_hd49@hc360.com','psw49','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd50','edm_hd50@hc360.com','psw50','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd51','edm_hd51@hc360.com','psw51','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd52','edm_hd52@hc360.com','psw52','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd53','edm_hd53@hc360.com','psw53','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd54','edm_hd54@hc360.com','psw54','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd55','edm_hd55@hc360.com','psw55','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd56','edm_hd56@hc360.com','psw56','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd57','edm_hd57@hc360.com','psw57','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd58','edm_hd58@hc360.com','psw58','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd59','edm_hd59@hc360.com','psw59','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd60','edm_hd60@hc360.com','psw60','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd61','edm_hd61@hc360.com','psw61','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd62','edm_hd62@hc360.com','psw62','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd63','edm_hd63@hc360.com','psw63','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd64','edm_hd64@hc360.com','psw64','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd65','edm_hd65@hc360.com','psw65','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd66','edm_hd66@hc360.com','psw66','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd67','edm_hd67@hc360.com','psw67','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd68','edm_hd68@hc360.com','psw68','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd69','edm_hd69@hc360.com','psw69','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd70','edm_hd70@hc360.com','psw70','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd71','edm_hd71@hc360.com','psw71','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd72','edm_hd72@hc360.com','psw72','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd73','edm_hd73@hc360.com','psw73','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd74','edm_hd74@hc360.com','psw74','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd75','edm_hd75@hc360.com','psw75','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd76','edm_hd76@hc360.com','psw76','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd77','edm_hd77@hc360.com','psw77','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd78','edm_hd78@hc360.com','psw78','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd79','edm_hd79@hc360.com','psw79','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd80','edm_hd80@hc360.com','psw80','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd81','edm_hd81@hc360.com','psw81','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd82','edm_hd82@hc360.com','psw82','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd83','edm_hd83@hc360.com','psw83','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd84','edm_hd84@hc360.com','psw84','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd85','edm_hd85@hc360.com','psw85','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd86','edm_hd86@hc360.com','psw86','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd87','edm_hd87@hc360.com','psw87','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd88','edm_hd88@hc360.com','psw88','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd89','edm_hd89@hc360.com','psw89','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd90','edm_hd90@hc360.com','psw90','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd91','edm_hd91@hc360.com','psw91','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd92','edm_hd92@hc360.com','psw92','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd93','edm_hd93@hc360.com','psw93','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd94','edm_hd94@hc360.com','psw94','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd95','edm_hd95@hc360.com','psw95','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd96','edm_hd96@hc360.com','psw96','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd97','edm_hd97@hc360.com','psw97','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd98','edm_hd98@hc360.com','psw98','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));insert into edm_users(name,email,password,grouptype,dateline) values ('edm_hd99','edm_hd99@hc360.com','psw99','1',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));/**=====================在cobar server的schema(edm)的root账号下，向水平分区的email_info写入100条数据=============================**/insert  into email_info(email,num,note) values ('hdedm0@hc360.com',0,'aa');insert  into email_info(email,num,note) values ('hdedm1@hc360.com',1,'aa');insert  into email_info(email,num,note) values ('hdedm2@hc360.com',2,'aa');insert  into email_info(email,num,note) values ('hdedm3@hc360.com',3,'aa');insert  into email_info(email,num,note) values ('hdedm4@hc360.com',4,'aa');insert  into email_info(email,num,note) values ('hdedm5@hc360.com',5,'aa');insert  into email_info(email,num,note) values ('hdedm6@hc360.com',6,'aa');insert  into email_info(email,num,note) values ('hdedm7@hc360.com',7,'aa');insert  into email_info(email,num,note) values ('hdedm8@hc360.com',8,'aa');insert  into email_info(email,num,note) values ('hdedm9@hc360.com',9,'aa');insert  into email_info(email,num,note) values ('hdedm10@hc360.com',10,'aa');insert  into email_info(email,num,note) values ('hdedm11@hc360.com',11,'aa');insert  into email_info(email,num,note) values ('hdedm12@hc360.com',12,'aa');insert  into email_info(email,num,note) values ('hdedm13@hc360.com',13,'aa');insert  into email_info(email,num,note) values ('hdedm14@hc360.com',14,'aa');insert  into email_info(email,num,note) values ('hdedm15@hc360.com',15,'aa');insert  into email_info(email,num,note) values ('hdedm16@hc360.com',16,'aa');insert  into email_info(email,num,note) values ('hdedm17@hc360.com',17,'aa');insert  into email_info(email,num,note) values ('hdedm18@hc360.com',18,'aa');insert  into email_info(email,num,note) values ('hdedm19@hc360.com',19,'aa');insert  into email_info(email,num,note) values ('hdedm20@hc360.com',20,'aa');insert  into email_info(email,num,note) values ('hdedm21@hc360.com',21,'aa');insert  into email_info(email,num,note) values ('hdedm22@hc360.com',22,'aa');insert  into email_info(email,num,note) values ('hdedm23@hc360.com',23,'aa');insert  into email_info(email,num,note) values ('hdedm24@hc360.com',24,'aa');insert  into email_info(email,num,note) values ('hdedm25@hc360.com',25,'aa');insert  into email_info(email,num,note) values ('hdedm26@hc360.com',26,'aa');insert  into email_info(email,num,note) values ('hdedm27@hc360.com',27,'aa');insert  into email_info(email,num,note) values ('hdedm28@hc360.com',28,'aa');insert  into email_info(email,num,note) values ('hdedm29@hc360.com',29,'aa');insert  into email_info(email,num,note) values ('hdedm30@hc360.com',30,'aa');insert  into email_info(email,num,note) values ('hdedm31@hc360.com',31,'aa');insert  into email_info(email,num,note) values ('hdedm32@hc360.com',32,'aa');insert  into email_info(email,num,note) values ('hdedm33@hc360.com',33,'aa');insert  into email_info(email,num,note) values ('hdedm34@hc360.com',34,'aa');insert  into email_info(email,num,note) values ('hdedm35@hc360.com',35,'aa');insert  into email_info(email,num,note) values ('hdedm36@hc360.com',36,'aa');insert  into email_info(email,num,note) values ('hdedm37@hc360.com',37,'aa');insert  into email_info(email,num,note) values ('hdedm38@hc360.com',38,'aa');insert  into email_info(email,num,note) values ('hdedm39@hc360.com',39,'aa');insert  into email_info(email,num,note) values ('hdedm40@hc360.com',40,'aa');insert  into email_info(email,num,note) values ('hdedm41@hc360.com',41,'aa');insert  into email_info(email,num,note) values ('hdedm42@hc360.com',42,'aa');insert  into email_info(email,num,note) values ('hdedm43@hc360.com',43,'aa');insert  into email_info(email,num,note) values ('hdedm44@hc360.com',44,'aa');insert  into email_info(email,num,note) values ('hdedm45@hc360.com',45,'aa');insert  into email_info(email,num,note) values ('hdedm46@hc360.com',46,'aa');insert  into email_info(email,num,note) values ('hdedm47@hc360.com',47,'aa');insert  into email_info(email,num,note) values ('hdedm48@hc360.com',48,'aa');insert  into email_info(email,num,note) values ('hdedm49@hc360.com',49,'aa');insert  into email_info(email,num,note) values ('hdedm50@hc360.com',50,'aa');insert  into email_info(email,num,note) values ('hdedm51@hc360.com',51,'aa');insert  into email_info(email,num,note) values ('hdedm52@hc360.com',52,'aa');insert  into email_info(email,num,note) values ('hdedm53@hc360.com',53,'aa');insert  into email_info(email,num,note) values ('hdedm54@hc360.com',54,'aa');insert  into email_info(email,num,note) values ('hdedm55@hc360.com',55,'aa');insert  into email_info(email,num,note) values ('hdedm56@hc360.com',56,'aa');insert  into email_info(email,num,note) values ('hdedm57@hc360.com',57,'aa');insert  into email_info(email,num,note) values ('hdedm58@hc360.com',58,'aa');insert  into email_info(email,num,note) values ('hdedm59@hc360.com',59,'aa');insert  into email_info(email,num,note) values ('hdedm60@hc360.com',60,'aa');insert  into email_info(email,num,note) values ('hdedm61@hc360.com',61,'aa');insert  into email_info(email,num,note) values ('hdedm62@hc360.com',62,'aa');insert  into email_info(email,num,note) values ('hdedm63@hc360.com',63,'aa');insert  into email_info(email,num,note) values ('hdedm64@hc360.com',64,'aa');insert  into email_info(email,num,note) values ('hdedm65@hc360.com',65,'aa');insert  into email_info(email,num,note) values ('hdedm66@hc360.com',66,'aa');insert  into email_info(email,num,note) values ('hdedm67@hc360.com',67,'aa');insert  into email_info(email,num,note) values ('hdedm68@hc360.com',68,'aa');insert  into email_info(email,num,note) values ('hdedm69@hc360.com',69,'aa');insert  into email_info(email,num,note) values ('hdedm70@hc360.com',70,'aa');insert  into email_info(email,num,note) values ('hdedm71@hc360.com',71,'aa');insert  into email_info(email,num,note) values ('hdedm72@hc360.com',72,'aa');insert  into email_info(email,num,note) values ('hdedm73@hc360.com',73,'aa');insert  into email_info(email,num,note) values ('hdedm74@hc360.com',74,'aa');insert  into email_info(email,num,note) values ('hdedm75@hc360.com',75,'aa');insert  into email_info(email,num,note) values ('hdedm76@hc360.com',76,'aa');insert  into email_info(email,num,note) values ('hdedm77@hc360.com',77,'aa');insert  into email_info(email,num,note) values ('hdedm78@hc360.com',78,'aa');insert  into email_info(email,num,note) values ('hdedm79@hc360.com',79,'aa');insert  into email_info(email,num,note) values ('hdedm80@hc360.com',80,'aa');insert  into email_info(email,num,note) values ('hdedm81@hc360.com',81,'aa');insert  into email_info(email,num,note) values ('hdedm82@hc360.com',82,'aa');insert  into email_info(email,num,note) values ('hdedm83@hc360.com',83,'aa');insert  into email_info(email,num,note) values ('hdedm84@hc360.com',84,'aa');insert  into email_info(email,num,note) values ('hdedm85@hc360.com',85,'aa');insert  into email_info(email,num,note) values ('hdedm86@hc360.com',86,'aa');insert  into email_info(email,num,note) values ('hdedm87@hc360.com',87,'aa');insert  into email_info(email,num,note) values ('hdedm88@hc360.com',88,'aa');insert  into email_info(email,num,note) values ('hdedm89@hc360.com',89,'aa');insert  into email_info(email,num,note) values ('hdedm90@hc360.com',90,'aa');insert  into email_info(email,num,note) values ('hdedm91@hc360.com',91,'aa');insert  into email_info(email,num,note) values ('hdedm92@hc360.com',92,'aa');insert  into email_info(email,num,note) values ('hdedm93@hc360.com',93,'aa');insert  into email_info(email,num,note) values ('hdedm94@hc360.com',94,'aa');insert  into email_info(email,num,note) values ('hdedm95@hc360.com',95,'aa');insert  into email_info(email,num,note) values ('hdedm96@hc360.com',96,'aa');insert  into email_info(email,num,note) values ('hdedm97@hc360.com',97,'aa');insert  into email_info(email,num,note) values ('hdedm98@hc360.com',98,'aa');insert  into email_info(email,num,note) values ('hdedm99@hc360.com',99,'aa');/**====================在cobar server的schema(edm)的root账号下，对水平分区库email_info_detail写入100条数  据==============================**/insert  into email_info_detail(email,num_a,num_b) values ('hdedm0@hc360.com',0,0);insert  into email_info_detail(email,num_a,num_b) values ('hdedm1@hc360.com',1,-1);insert  into email_info_detail(email,num_a,num_b) values ('hdedm2@hc360.com',2,-2);insert  into email_info_detail(email,num_a,num_b) values ('hdedm3@hc360.com',3,-3);insert  into email_info_detail(email,num_a,num_b) values ('hdedm4@hc360.com',4,-4);insert  into email_info_detail(email,num_a,num_b) values ('hdedm5@hc360.com',5,-5);insert  into email_info_detail(email,num_a,num_b) values ('hdedm6@hc360.com',6,-6);insert  into email_info_detail(email,num_a,num_b) values ('hdedm7@hc360.com',7,-7);insert  into email_info_detail(email,num_a,num_b) values ('hdedm8@hc360.com',8,-8);insert  into email_info_detail(email,num_a,num_b) values ('hdedm9@hc360.com',9,-9);insert  into email_info_detail(email,num_a,num_b) values ('hdedm10@hc360.com',10,-10);insert  into email_info_detail(email,num_a,num_b) values ('hdedm11@hc360.com',11,-11);insert  into email_info_detail(email,num_a,num_b) values ('hdedm12@hc360.com',12,-12);insert  into email_info_detail(email,num_a,num_b) values ('hdedm13@hc360.com',13,-13);insert  into email_info_detail(email,num_a,num_b) values ('hdedm14@hc360.com',14,-14);insert  into email_info_detail(email,num_a,num_b) values ('hdedm15@hc360.com',15,-15);insert  into email_info_detail(email,num_a,num_b) values ('hdedm16@hc360.com',16,-16);insert  into email_info_detail(email,num_a,num_b) values ('hdedm17@hc360.com',17,-17);insert  into email_info_detail(email,num_a,num_b) values ('hdedm18@hc360.com',18,-18);insert  into email_info_detail(email,num_a,num_b) values ('hdedm19@hc360.com',19,-19);insert  into email_info_detail(email,num_a,num_b) values ('hdedm20@hc360.com',20,-20);insert  into email_info_detail(email,num_a,num_b) values ('hdedm21@hc360.com',21,-21);insert  into email_info_detail(email,num_a,num_b) values ('hdedm22@hc360.com',22,-22);insert  into email_info_detail(email,num_a,num_b) values ('hdedm23@hc360.com',23,-23);insert  into email_info_detail(email,num_a,num_b) values ('hdedm24@hc360.com',24,-24);insert  into email_info_detail(email,num_a,num_b) values ('hdedm25@hc360.com',25,-25);insert  into email_info_detail(email,num_a,num_b) values ('hdedm26@hc360.com',26,-26);insert  into email_info_detail(email,num_a,num_b) values ('hdedm27@hc360.com',27,-27);insert  into email_info_detail(email,num_a,num_b) values ('hdedm28@hc360.com',28,-28);insert  into email_info_detail(email,num_a,num_b) values ('hdedm29@hc360.com',29,-29);insert  into email_info_detail(email,num_a,num_b) values ('hdedm30@hc360.com',30,-30);insert  into email_info_detail(email,num_a,num_b) values ('hdedm31@hc360.com',31,-31);insert  into email_info_detail(email,num_a,num_b) values ('hdedm32@hc360.com',32,-32);insert  into email_info_detail(email,num_a,num_b) values ('hdedm33@hc360.com',33,-33);insert  into email_info_detail(email,num_a,num_b) values ('hdedm34@hc360.com',34,-34);insert  into email_info_detail(email,num_a,num_b) values ('hdedm35@hc360.com',35,-35);insert  into email_info_detail(email,num_a,num_b) values ('hdedm36@hc360.com',36,-36);insert  into email_info_detail(email,num_a,num_b) values ('hdedm37@hc360.com',37,-37);insert  into email_info_detail(email,num_a,num_b) values ('hdedm38@hc360.com',38,-38);insert  into email_info_detail(email,num_a,num_b) values ('hdedm39@hc360.com',39,-39);insert  into email_info_detail(email,num_a,num_b) values ('hdedm40@hc360.com',40,-40);insert  into email_info_detail(email,num_a,num_b) values ('hdedm41@hc360.com',41,-41);insert  into email_info_detail(email,num_a,num_b) values ('hdedm42@hc360.com',42,-42);insert  into email_info_detail(email,num_a,num_b) values ('hdedm43@hc360.com',43,-43);insert  into email_info_detail(email,num_a,num_b) values ('hdedm44@hc360.com',44,-44);insert  into email_info_detail(email,num_a,num_b) values ('hdedm45@hc360.com',45,-45);insert  into email_info_detail(email,num_a,num_b) values ('hdedm46@hc360.com',46,-46);insert  into email_info_detail(email,num_a,num_b) values ('hdedm47@hc360.com',47,-47);insert  into email_info_detail(email,num_a,num_b) values ('hdedm48@hc360.com',48,-48);insert  into email_info_detail(email,num_a,num_b) values ('hdedm49@hc360.com',49,-49);insert  into email_info_detail(email,num_a,num_b) values ('hdedm50@hc360.com',50,-50);insert  into email_info_detail(email,num_a,num_b) values ('hdedm51@hc360.com',51,-51);insert  into email_info_detail(email,num_a,num_b) values ('hdedm52@hc360.com',52,-52);insert  into email_info_detail(email,num_a,num_b) values ('hdedm53@hc360.com',53,-53);insert  into email_info_detail(email,num_a,num_b) values ('hdedm54@hc360.com',54,-54);insert  into email_info_detail(email,num_a,num_b) values ('hdedm55@hc360.com',55,-55);insert  into email_info_detail(email,num_a,num_b) values ('hdedm56@hc360.com',56,-56);insert  into email_info_detail(email,num_a,num_b) values ('hdedm57@hc360.com',57,-57);insert  into email_info_detail(email,num_a,num_b) values ('hdedm58@hc360.com',58,-58);insert  into email_info_detail(email,num_a,num_b) values ('hdedm59@hc360.com',59,-59);insert  into email_info_detail(email,num_a,num_b) values ('hdedm60@hc360.com',60,-60);insert  into email_info_detail(email,num_a,num_b) values ('hdedm61@hc360.com',61,-61);insert  into email_info_detail(email,num_a,num_b) values ('hdedm62@hc360.com',62,-62);insert  into email_info_detail(email,num_a,num_b) values ('hdedm63@hc360.com',63,-63);insert  into email_info_detail(email,num_a,num_b) values ('hdedm64@hc360.com',64,-64);insert  into email_info_detail(email,num_a,num_b) values ('hdedm65@hc360.com',65,-65);insert  into email_info_detail(email,num_a,num_b) values ('hdedm66@hc360.com',66,-66);insert  into email_info_detail(email,num_a,num_b) values ('hdedm67@hc360.com',67,-67);insert  into email_info_detail(email,num_a,num_b) values ('hdedm68@hc360.com',68,-68);insert  into email_info_detail(email,num_a,num_b) values ('hdedm69@hc360.com',69,-69);insert  into email_info_detail(email,num_a,num_b) values ('hdedm70@hc360.com',70,-70);insert  into email_info_detail(email,num_a,num_b) values ('hdedm71@hc360.com',71,-71);insert  into email_info_detail(email,num_a,num_b) values ('hdedm72@hc360.com',72,-72);insert  into email_info_detail(email,num_a,num_b) values ('hdedm73@hc360.com',73,-73);insert  into email_info_detail(email,num_a,num_b) values ('hdedm74@hc360.com',74,-74);insert  into email_info_detail(email,num_a,num_b) values ('hdedm75@hc360.com',75,-75);insert  into email_info_detail(email,num_a,num_b) values ('hdedm76@hc360.com',76,-76);insert  into email_info_detail(email,num_a,num_b) values ('hdedm77@hc360.com',77,-77);insert  into email_info_detail(email,num_a,num_b) values ('hdedm78@hc360.com',78,-78);insert  into email_info_detail(email,num_a,num_b) values ('hdedm79@hc360.com',79,-79);insert  into email_info_detail(email,num_a,num_b) values ('hdedm80@hc360.com',80,-80);insert  into email_info_detail(email,num_a,num_b) values ('hdedm81@hc360.com',81,-81);insert  into email_info_detail(email,num_a,num_b) values ('hdedm82@hc360.com',82,-82);insert  into email_info_detail(email,num_a,num_b) values ('hdedm83@hc360.com',83,-83);insert  into email_info_detail(email,num_a,num_b) values ('hdedm84@hc360.com',84,-84);insert  into email_info_detail(email,num_a,num_b) values ('hdedm85@hc360.com',85,-85);insert  into email_info_detail(email,num_a,num_b) values ('hdedm86@hc360.com',86,-86);insert  into email_info_detail(email,num_a,num_b) values ('hdedm87@hc360.com',87,-87);insert  into email_info_detail(email,num_a,num_b) values ('hdedm88@hc360.com',88,-88);insert  into email_info_detail(email,num_a,num_b) values ('hdedm89@hc360.com',89,-89);insert  into email_info_detail(email,num_a,num_b) values ('hdedm90@hc360.com',90,-90);insert  into email_info_detail(email,num_a,num_b) values ('hdedm91@hc360.com',91,-91);insert  into email_info_detail(email,num_a,num_b) values ('hdedm92@hc360.com',92,-92);insert  into email_info_detail(email,num_a,num_b) values ('hdedm93@hc360.com',93,-93);insert  into email_info_detail(email,num_a,num_b) values ('hdedm94@hc360.com',94,-94);insert  into email_info_detail(email,num_a,num_b) values ('hdedm95@hc360.com',95,-95);insert  into email_info_detail(email,num_a,num_b) values ('hdedm96@hc360.com',96,-96);insert  into email_info_detail(email,num_a,num_b) values ('hdedm97@hc360.com',97,-97);insert  into email_info_detail(email,num_a,num_b) values ('hdedm98@hc360.com',98,-98);insert  into email_info_detail(email,num_a,num_b) values ('hdedm99@hc360.com',99,-99);/**======================在cobar server的schema(edm)的root账号下，直接修改水平分区表email_info数据============================**/update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm1@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm2@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm3@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm4@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm5@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm6@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm7@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm8@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm9@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm10@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm11@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm12@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm13@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm14@hc360.com';update email_info ei set ei.num=88888, ei.create_time=now() where ei.email='hdedm15@hc360.com';/**=======================在cobar server的schema(edm)的root账号下，直接修改水平分区表email_info_detail数据===========================**/update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm1@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm2@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm3@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm4@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm5@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm6@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm7@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm8@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm9@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm10@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm11@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm12@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm13@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm14@hc360.com';update email_info_detail eid set eid.num_a=88888, eid.create_time=now() where eid.email='hdedm15@hc360.com';/**=====================创建演示分区数据库，注意不要放到一个数据库上=============================**/create database if not exists `edmshard1`;USE `edmshard1`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard2`;USE `edmshard2`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard3`;USE `edmshard3`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard4`;USE `edmshard4`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard5`;USE `edmshard5`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard6`;USE `edmshard6`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard7`;USE `edmshard7`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard8`;USE `edmshard8`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard9`;USE `edmshard9`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard10`;USE `edmshard10`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard11`;USE `edmshard11`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard12`;USE `edmshard12`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard13`;USE `edmshard13`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard14`;USE `edmshard14`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard15`;USE `edmshard15`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/create database if not exists `edmshard16`;USE `edmshard16`;DROP TABLE IF EXISTS `email_info`;CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;DROP TABLE IF EXISTS `email_info_detail`;CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/**==================================================**/

5、生成测试sql的java类：GenShardSql.java 你可以自己生成测试数据以及测试库了

package com.gen.sql;public class GenShardSql {/** * @param args */public static void main(String[] args) {// TODO Auto-generated method stub//通过cobar进行数据展示String a="use edm;";System.out.println(a);System.out.println("/**==================================================**/");GenShardSql gss=new GenShardSql();//清理主库以及分区库gss.clearDate();System.out.println("/**==================================================**/");//给主库增加数据gss.println_edm_hd(100);System.out.println("/**==================================================**/");//给分区库增加数据gss.println_email_info(100);System.out.println("/**==================================================**/");gss.println_email_info_detail(100);System.out.println("/**==================================================**/");//修改数据gss.modifyEmailData(1, 16, 88888);//初始化分区库gss.initdb(1, 16);}public void clearDate(){//清理主库String del="delete from edm_users;";System.out.println(del);//清理分区库del="delete from email_info;";System.out.println(del);//清理分区库del="delete from email_info_detail;";System.out.println(del);}public void println_email_info(int num){String a="";for(int i=0;i<num;i++){a="insert  into email_info(email,num,note) values (\'hdedm"+i+"@hc360.com\',"+i+",\'aa\');";System.out.println(a);}}public void println_email_info_detail(int num){String b="";for(int i=0;i<num;i++){b="insert  into email_info_detail(email,num_a,num_b) values ('hdedm"+i+"@hc360.com',"+i+","+-i+");";System.out.println(b);}}public void println_edm_hd(int num){String a="use edm;";System.out.println(a);String b="";for(int i=0;i<num;i++){ b="insert into edm_users(name,email,password,grouptype,dateline) values (\'edm_hd"+i+"\',\'edm_hd"+i+"@hc360.com\',\'psw"+i+"\',\'1\',UNIX_TIMESTAMP(CURRENT_TIMESTAMP));"; System.out.println(b);}}/**初始化分区DB*/public void initdb(int start,int end){for(int i=start;i<=end;i++){String create="create database if not exists `edmshard"+i+"`;";String use="USE `edmshard"+i+"`;";String drop1="DROP TABLE IF EXISTS `email_info`;";String c_t1="CREATE TABLE `email_info` (  `email` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `num` int(10) DEFAULT NULL,  `note` varchar(500) CHARACTER SET utf8 DEFAULT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";String drop2="DROP TABLE IF EXISTS `email_info_detail`;";String c_t2="CREATE TABLE `email_info_detail` (  `email` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,  `num_a` int(10) DEFAULT 0,  `num_b` int(10) DEFAULT 0,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";System.out.println(create);System.out.println(use);System.out.println(drop1);System.out.println(c_t1);System.out.println(drop2);System.out.println(c_t2);System.out.println("/**==================================================**/");}}/** * 根据邮箱更改某个分区的数据 * */public void modifyEmailData(int start,int end,int val){String ms="";for(int i=start;i<end;i++){ms="update email_info ei set ei.num="+val+", ei.create_time=now() where ei.email=\'hdedm"+i+"@hc360.com\';";System.out.println(ms);}System.out.println("/**==================================================**/");for(int i=start;i<end;i++){ms="update email_info_detail eid set eid.num_a="+val+", eid.create_time=now() where eid.email=\'hdedm"+i+"@hc360.com\';";System.out.println(ms);}System.out.println("/**==================================================**/");} }