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

一、需求

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

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

二、问题

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

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

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

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

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

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

1、mysql -h192.168.20.65 -uroot -p1q2w3e -P8066
2、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("/**==================================================**/");
} 
}