mysql mycat查询数据_Mycat(6):聊天消息表,按月分表java客户端跨月查询数据_MySQL...

本文详细介绍了如何在Mycat环境下进行跨月查询数据,通过示例展示了针对聊天消息表的插入与查询操作。同时,文章还演示了Java客户端如何连接Mycat并执行SQL语句,实现按月分表的数据检索,以及分页查询的逻辑处理,确保在不同月份间无缝切换查询。
摘要由CSDN通过智能技术生成

select 1

server.xml配置:druidparser

msg

msg

3,mysql 客户端测试

如果mycat启动正常,查看logs/wrapper.log没有异常,且数据库连接已经创建。

# mysql -umsg -pmsg -P8066 -h 127.0.0.1

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.8-mycat-1.3 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use msg;

mysql>

mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'java',now(),201501);

Query OK, 1 row affected (0.00 sec)

mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(2,1,'oracle',now(),201501);

Query OK, 1 row affected (0.01 sec)

mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,2,'ibm',now(),201501);

Query OK, 1 row affected (0.00 sec)

mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(2,2,'mysql',now(),201501);

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'zhangsan',now(),201502);

Query OK, 1 row affected (0.00 sec)

mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'lisi',now(),201503);

Query OK, 1 row affected (0.01 sec)

mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'wangwu',now(),201504);

Query OK, 1 row affected (0.00 sec)

mysql> select * from msg where gid = 1 and create_month = 201501;

+----+-----+---------+---------------------+--------------+

| id | gid | content | create_time | create_month |

+----+-----+---------+---------------------+--------------+

| 1 | 1 | java | 2015-07-24 13:21:41 | 201501 |

| 2 | 1 | oracle | 2015-07-24 13:21:41 | 201501 |

+----+-----+---------+---------------------+--------------+

2 rows in set (0.19 sec)

mysql> select * from msg where gid = 1 and create_month = 201502;

+----+-----+----------+---------------------+--------------+

| id | gid | content | create_time | create_month |

+----+-----+----------+---------------------+--------------+

| 1 | 1 | zhangsan | 2015-07-24 13:21:42 | 201502 |

+----+-----+----------+---------------------+--------------+

1 row in set (0.00 sec)

mysql> select * from msg where gid = 1 and create_month = 201503;

+----+-----+---------+---------------------+--------------+

| id | gid | content | create_time | create_month |

+----+-----+---------+---------------------+--------------+

| 1 | 1 | lisi | 2015-07-24 13:21:42 | 201503 |

+----+-----+---------+---------------------+--------------+

1 row in set (0.01 sec)

mysql> select * from msg where gid = 1 and create_month = 201504;

+----+-----+---------+---------------------+--------------+

| id | gid | content | create_time | create_month |

+----+-----+---------+---------------------+--------------+

| 1 | 1 | wangwu | 2015-07-24 13:21:43 | 201504 |

+----+-----+---------+---------------------+--------------+

1 row in set (0.13 sec)

mysql> select * from msg where gid = 2 and create_month = 201501;

+----+-----+---------+---------------------+--------------+

| id | gid | content | create_time | create_month |

+----+-----+---------+---------------------+--------------+

| 1 | 2 | ibm | 2015-07-24 13:21:41 | 201501 |

| 2 | 2 | mysql | 2015-07-24 13:21:41 | 201501 |

+----+-----+---------+---------------------+--------------+

2 rows in set (0.01 sec)

4,java客户端调用测试

import java.sql.*;

import java.sql.Date;

import java.util.*;

public class MycatTest {

private static Connection connect = null;

private static Statement statement = null;

private static PreparedStatement preparedStatement = null;

private static ResultSet resultSet = null;

public static void init() {

try {

Class.forName("com.mysql.jdbc.Driver");

connect = DriverManager

.getConnection("jdbc:mysql://192.168.100.1:8066/msg", "msg", "msg");

statement = connect.createStatement();

} catch (Exception e) {

e.printStackTrace();

}

}

public static void close() {

try {

if (resultSet != null) {

resultSet.close();

}

} catch (Exception e) {

}

try {

if (statement != null) {

statement.close();

}

} catch (Exception e) {

}

try {

if (connect != null) {

connect.close();

}

} catch (Exception e) {

}

}

public static void testInsert() {

//实际当中i为gid的自增id。跨按月分区自增。

for (int i = 1; i < 100; i++) {

try {

//特意设置28循环周期。

int j = (i / 28) + 1;

preparedStatement = connect

.prepareStatement("insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(?,?,?,?,?)");

//录入参数。

preparedStatement.setInt(1, i);

preparedStatement.setInt(2, 99);

preparedStatement.setString(3, "test content " + i);

//插入j时间

preparedStatement.setDate(4, new java.sql.Date(2015, j - 1, i));

//设置按月分区。

preparedStatement.setInt(5, 201500 + j);

preparedStatement.executeUpdate();

} catch (Exception e) {

e.printStackTrace();

}

}

}

static class Msg {

private int id;

private int gid;

private String content;

private java.util.Date createTime;

private int createMonth;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public int getGid() {

return gid;

}

public void setGid(int gid) {

this.gid = gid;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

public java.util.Date getCreateTime() {

return createTime;

}

public void setCreateTime(java.util.Date createTime) {

this.createTime = createTime;

}

public int getCreateMonth() {

return createMonth;

}

public void setCreateMonth(int createMonth) {

this.createMonth = createMonth;

}

@Override

public String toString() {

return "Msg{" +

"id=" + id +

", gid=" + gid +

", content='" + content + '\'' +

", createTime=" + createTime +

", createMonth=" + createMonth +

'}';

}

}

public static ListselectByGidMonth(int gid, int month, int id, int limit) {

List list = new ArrayList();

try {

//如果id == 0就是按照id倒叙查询。

if (id == 0) {

String sql = "select `id`,`gid`,`content`,`create_time`,`create_month` from msg where gid = ? and create_month = ? order by id desc limit ? ";

preparedStatement = connect

.prepareStatement(sql);

preparedStatement.setInt(1, gid);

preparedStatement.setInt(2, month);

preparedStatement.setInt(3, limit);

} else {//

String sql = "select `id`,`gid`,`content`,`create_time`,`create_month` from msg where gid = ? and create_month = ? and id < ? order by id desc limit ? ";

preparedStatement = connect

.prepareStatement(sql);

preparedStatement.setInt(1, gid);

preparedStatement.setInt(2, month);

preparedStatement.setInt(3, id);

preparedStatement.setInt(4, limit);

}

resultSet = preparedStatement.executeQuery();

int lastId = id;

while (resultSet.next()) {

int id2 = resultSet.getInt("id");

//设置最后查询id。

lastId = id2;

int gid2 = resultSet.getInt("gid");

String content = resultSet.getString("content");

java.util.Date create_time = resultSet.getDate("create_time");

int create_month = resultSet.getInt("create_month");

Msg msg = new Msg();

msg.setId(id2);

msg.setGid(gid2);

msg.setContent(content);

msg.setCreateTime(create_time);

msg.setCreateMonth(create_month);

//增加数据到list。

list.add(msg);

}

//非常重要的,如果id > 1,且当月没有查询到数据,查询前一个月的数据,直到id = 1 为止。

if (lastId > 1 && list.size() < limit && month >= 201501) {

//剩余数据

int remainSize = limit - list.size();

//使用递归进行查询。month-1 是简单操作,实际应该用Date返回前一个月。

ListremainList = selectByGidMonth(gid, month - 1, lastId, remainSize);

list.addAll(remainList);

}

} catch (Exception e) {

e.printStackTrace();

}

return list;

}

private static void testSelect() {

//假设分页是20 条记录。

int page = 20;

int lastId = 0;

Listlist = selectByGidMonth(99, 201504, lastId, page);

for (Msg msg : list) {

System.out.println(msg);

lastId = msg.getId();

}

System.out.println("###########################");

list = selectByGidMonth(99, 201503, lastId, page);

for (Msg msg : list) {

System.out.println(msg);

lastId = msg.getId();

}

System.out.println("###########################");

list = selectByGidMonth(99, 201503, lastId, page);

for (Msg msg : list) {

System.out.println(msg);

lastId = msg.getId();

}

System.out.println("###########################");

list = selectByGidMonth(99, 201502, lastId, page);

for (Msg msg : list) {

System.out.println(msg);

lastId = msg.getId();

}

System.out.println("###########################");

list = selectByGidMonth(99, 201501, lastId, page);

for (Msg msg : list) {

System.out.println(msg);

lastId = msg.getId();

}

}

public static void main(String[] args) {

init();

//testInsert();

testSelect();

close();

}

}

java客户端调用说明,首先msg表的id是按照gid连续自增的,如果id > 1,且当月没有查询到数据,查询前一个月的数据,直到id = 1 为止。

if (lastId > 1 && list.size() < limit && month >= 201501) {

//剩余数据

int remainSize = limit - list.size();

//使用递归进行查询。month-1 是简单操作,实际应该用Date返回前一个月。

ListremainList = selectByGidMonth(gid, month - 1, lastId, remainSize);

list.addAll(remainList);

}

使用递归函数往前一个月一个月查询数据,直到查询到id = 1 为止。查询结果如下,每次显示20条数据,插入的100 条 % 28 分别插入4个月数据。

查询结果可以跨月查询:

Msg{id=99, gid=99, content='test content 99', createTime=3915-07-08, createMonth=201504}

Msg{id=98, gid=99, content='test content 98', createTime=3915-07-07, createMonth=201504}

Msg{id=97, gid=99, content='test content 97', createTime=3915-07-06, createMonth=201504}

Msg{id=96, gid=99, content='test content 96', createTime=3915-07-05, createMonth=201504}

Msg{id=95, gid=99, content='test content 95', createTime=3915-07-04, createMonth=201504}

Msg{id=94, gid=99, content='test content 94', createTime=3915-07-03, createMonth=201504}

Msg{id=93, gid=99, content='test content 93', createTime=3915-07-02, createMonth=201504}

Msg{id=92, gid=99, content='test content 92', createTime=3915-07-01, createMonth=201504}

Msg{id=91, gid=99, content='test content 91', createTime=3915-06-30, createMonth=201504}

Msg{id=90, gid=99, content='test content 90', createTime=3915-06-29, createMonth=201504}

Msg{id=89, gid=99, content='test content 89', createTime=3915-06-28, createMonth=201504}

Msg{id=88, gid=99, content='test content 88', createTime=3915-06-27, createMonth=201504}

Msg{id=87, gid=99, content='test content 87', createTime=3915-06-26, createMonth=201504}

Msg{id=86, gid=99, content='test content 86', createTime=3915-06-25, createMonth=201504}

Msg{id=85, gid=99, content='test content 85', createTime=3915-06-24, createMonth=201504}

Msg{id=84, gid=99, content='test content 84', createTime=3915-06-23, createMonth=201504}

Msg{id=83, gid=99, content='test content 83', createTime=3915-05-22, createMonth=201503}

Msg{id=82, gid=99, content='test content 82', createTime=3915-05-21, createMonth=201503}

Msg{id=81, gid=99, content='test content 81', createTime=3915-05-20, createMonth=201503}

Msg{id=80, gid=99, content='test content 80', createTime=3915-05-19, createMonth=201503}

###########################

Msg{id=79, gid=99, content='test content 79', createTime=3915-05-18, createMonth=201503}

Msg{id=78, gid=99, content='test content 78', createTime=3915-05-17, createMonth=201503}

Msg{id=77, gid=99, content='test content 77', createTime=3915-05-16, createMonth=201503}

Msg{id=76, gid=99, content='test content 76', createTime=3915-05-15, createMonth=201503}

Msg{id=75, gid=99, content='test content 75', createTime=3915-05-14, createMonth=201503}

Msg{id=74, gid=99, content='test content 74', createTime=3915-05-13, createMonth=201503}

Msg{id=73, gid=99, content='test content 73', createTime=3915-05-12, createMonth=201503}

Msg{id=72, gid=99, content='test content 72', createTime=3915-05-11, createMonth=201503}

Msg{id=71, gid=99, content='test content 71', createTime=3915-05-10, createMonth=201503}

Msg{id=70, gid=99, content='test content 70', createTime=3915-05-09, createMonth=201503}

Msg{id=69, gid=99, content='test content 69', createTime=3915-05-08, createMonth=201503}

Msg{id=68, gid=99, content='test content 68', createTime=3915-05-07, createMonth=201503}

Msg{id=67, gid=99, content='test content 67', createTime=3915-05-06, createMonth=201503}

Msg{id=66, gid=99, content='test content 66', createTime=3915-05-05, createMonth=201503}

Msg{id=65, gid=99, content='test content 65', createTime=3915-05-04, createMonth=201503}

Msg{id=64, gid=99, content='test content 64', createTime=3915-05-03, createMonth=201503}

Msg{id=63, gid=99, content='test content 63', createTime=3915-05-02, createMonth=201503}

Msg{id=62, gid=99, content='test content 62', createTime=3915-05-01, createMonth=201503}

Msg{id=61, gid=99, content='test content 61', createTime=3915-04-30, createMonth=201503}

Msg{id=60, gid=99, content='test content 60', createTime=3915-04-29, createMonth=201503}

###########################

Msg{id=59, gid=99, content='test content 59', createTime=3915-04-28, createMonth=201503}

Msg{id=58, gid=99, content='test content 58', createTime=3915-04-27, createMonth=201503}

Msg{id=57, gid=99, content='test content 57', createTime=3915-04-26, createMonth=201503}

Msg{id=56, gid=99, content='test content 56', createTime=3915-04-25, createMonth=201503}

Msg{id=55, gid=99, content='test content 55', createTime=3915-03-27, createMonth=201502}

Msg{id=54, gid=99, content='test content 54', createTime=3915-03-26, createMonth=201502}

Msg{id=53, gid=99, content='test content 53', createTime=3915-03-25, createMonth=201502}

Msg{id=52, gid=99, content='test content 52', createTime=3915-03-24, createMonth=201502}

Msg{id=51, gid=99, content='test content 51', createTime=3915-03-23, createMonth=201502}

Msg{id=50, gid=99, content='test content 50', createTime=3915-03-22, createMonth=201502}

Msg{id=49, gid=99, content='test content 49', createTime=3915-03-21, createMonth=201502}

Msg{id=48, gid=99, content='test content 48', createTime=3915-03-20, createMonth=201502}

Msg{id=47, gid=99, content='test content 47', createTime=3915-03-19, createMonth=201502}

Msg{id=46, gid=99, content='test content 46', createTime=3915-03-18, createMonth=201502}

Msg{id=45, gid=99, content='test content 45', createTime=3915-03-17, createMonth=201502}

Msg{id=44, gid=99, content='test content 44', createTime=3915-03-16, createMonth=201502}

Msg{id=43, gid=99, content='test content 43', createTime=3915-03-15, createMonth=201502}

Msg{id=42, gid=99, content='test content 42', createTime=3915-03-14, createMonth=201502}

Msg{id=41, gid=99, content='test content 41', createTime=3915-03-13, createMonth=201502}

Msg{id=40, gid=99, content='test content 40', createTime=3915-03-12, createMonth=201502}

###########################

Msg{id=39, gid=99, content='test content 39', createTime=3915-03-11, createMonth=201502}

Msg{id=38, gid=99, content='test content 38', createTime=3915-03-10, createMonth=201502}

Msg{id=37, gid=99, content='test content 37', createTime=3915-03-09, createMonth=201502}

Msg{id=36, gid=99, content='test content 36', createTime=3915-03-08, createMonth=201502}

Msg{id=35, gid=99, content='test content 35', createTime=3915-03-07, createMonth=201502}

Msg{id=34, gid=99, content='test content 34', createTime=3915-03-06, createMonth=201502}

Msg{id=33, gid=99, content='test content 33', createTime=3915-03-05, createMonth=201502}

Msg{id=32, gid=99, content='test content 32', createTime=3915-03-04, createMonth=201502}

Msg{id=31, gid=99, content='test content 31', createTime=3915-03-03, createMonth=201502}

Msg{id=30, gid=99, content='test content 30', createTime=3915-03-02, createMonth=201502}

Msg{id=29, gid=99, content='test content 29', createTime=3915-03-01, createMonth=201502}

Msg{id=28, gid=99, content='test content 28', createTime=3915-02-28, createMonth=201502}

Msg{id=27, gid=99, content='test content 27', createTime=3915-01-27, createMonth=201501}

Msg{id=26, gid=99, content='test content 26', createTime=3915-01-26, createMonth=201501}

Msg{id=25, gid=99, content='test content 25', createTime=3915-01-25, createMonth=201501}

Msg{id=24, gid=99, content='test content 24', createTime=3915-01-24, createMonth=201501}

Msg{id=23, gid=99, content='test content 23', createTime=3915-01-23, createMonth=201501}

Msg{id=22, gid=99, content='test content 22', createTime=3915-01-22, createMonth=201501}

Msg{id=21, gid=99, content='test content 21', createTime=3915-01-21, createMonth=201501}

Msg{id=20, gid=99, content='test content 20', createTime=3915-01-20, createMonth=201501}

###########################

Msg{id=19, gid=99, content='test content 19', createTime=3915-01-19, createMonth=201501}

Msg{id=18, gid=99, content='test content 18', createTime=3915-01-18, createMonth=201501}

Msg{id=17, gid=99, content='test content 17', createTime=3915-01-17, createMonth=201501}

Msg{id=16, gid=99, content='test content 16', createTime=3915-01-16, createMonth=201501}

Msg{id=15, gid=99, content='test content 15', createTime=3915-01-15, createMonth=201501}

Msg{id=14, gid=99, content='test content 14', createTime=3915-01-14, createMonth=201501}

Msg{id=13, gid=99, content='test content 13', createTime=3915-01-13, createMonth=201501}

Msg{id=12, gid=99, content='test content 12', createTime=3915-01-12, createMonth=201501}

Msg{id=11, gid=99, content='test content 11', createTime=3915-01-11, createMonth=201501}

Msg{id=10, gid=99, content='test content 10', createTime=3915-01-10, createMonth=201501}

Msg{id=9, gid=99, content='test content 9', createTime=3915-01-09, createMonth=201501}

Msg{id=8, gid=99, content='test content 8', createTime=3915-01-08, createMonth=201501}

Msg{id=7, gid=99, content='test content 7', createTime=3915-01-07, createMonth=201501}

Msg{id=6, gid=99, content='test content 6', createTime=3915-01-06, createMonth=201501}

Msg{id=5, gid=99, content='test content 5', createTime=3915-01-05, createMonth=201501}

Msg{id=4, gid=99, content='test content 4', createTime=3915-01-04, createMonth=201501}

Msg{id=3, gid=99, content='test content 3', createTime=3915-01-03, createMonth=201501}

Msg{id=2, gid=99, content='test content 2', createTime=3915-01-02, createMonth=201501}

Msg{id=1, gid=99, content='test content 1', createTime=3915-01-01, createMonth=201501}

5,总结

mycat可以支持按月插入数据,但是查询起来要自己做好分月查询方案。

由于用户插入的数据有可能分散在多个月的数据表中,查询的时候需倒序一个月一个月的查询。

数据的存储可以按照年,500G数据放到一个磁盘,一年增加一个磁盘,新数据都写到新磁盘上面,保证数据随着时间增长只需要新增加数据库和磁盘即可,不需要进行数据迁移。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值