v3.2 新功能【自动分表】讨论与反馈 #1066

2881099 · 2022-04-09T08:15:30Z

2881099
Apr 9, 2022
Maintainer

v3.2 发布了新功能【自动分表】，这个链接用于讨论建议，与使用反馈。

【自动分表】不同于之前的 CURD.AsTable 方法，目前第一期完成按【时间】自动分表（不支持分库）。

欢迎积极参与测试、反馈，请优先使用源代码进行测试，方便反馈定位问题，谢谢。

1、定义特性

[Table(Name = "as_table_log_{yyyyMM}", AsTable = "createtime=2022-1-1(1 month)")]
class AsTableLog
{
    public Guid id { get; set; }
    public string msg { get; set; }
    public DateTime createtime { get; set; }
}

从 2022-1-1 开始至当前时间，每月创建一个分表，按 createtime 字段分表

若最大日期大于当前时间，可手工扩容分表：

fsql.CodeFirst.GetTableByEntity(typeof(AsTableLog))
    .AsTableImpl
    .GetTableNameByColumnValue(DateTime.Parse("2023-7-1"), autoExpand: true);

示范	说明
AsTable = "createtime=2022-1-1(1 year)"	一年一个分表
AsTable = "createtime=2022-1-1(2 year)"	两年一个分表
AsTable = "createtime=2022-1-1(1 month)"	一月一个分表
AsTable = "createtime=2022-1-1(3 month)"	三月一个分表
AsTable = "createtime=2022-1-1(1 day)"	一天一个分表
AsTable = "createtime=2022-1-1(7 day)"	七天一个分表
AsTable = "createtime=2022-1-1(12 hour)"	12小时一个分表

第一个表12个月，后面的表按1个月：

AsTable = "createtime=2022-1-1(12,1 month)"

第一个表非时间命名：

fsql.CodeFirst.GetTableByEntity(typeof(AsTableLog)).AsTableImpl.SetTableName(0, "自定义表名")

每个月1日10点分表：

[Table(Name = "as_table_log_{yyyyMMddHH}", AsTable = "createtime=2022-1-1 10(1 month)")]

未设置时间条件时，只命中最新的 3个分表：

fsql.CodeFirst.GetTableByEntity(typeof(AsTableLog)).AsTableImpl.SetDefaultAllTables(value => value.Take(3).ToArray());

2、创建分表

当开启自动迁移时，分表会自动创建。

3、插入数据时，根据对应属性值，自动对应分表

var items = new[]
{
    new AsTableLog{ msg = "msg01", createtime = DateTime.Parse("2022-1-1 13:00:11") },
    new AsTableLog{ msg = "msg02", createtime = DateTime.Parse("2022-1-2 14:00:12") },
    new AsTableLog{ msg = "msg03", createtime = DateTime.Parse("2022-2-2 15:00:13") },
    new AsTableLog{ msg = "msg04", createtime = DateTime.Parse("2022-2-8 15:00:13") },
    new AsTableLog{ msg = "msg05", createtime = DateTime.Parse("2022-3-8 15:00:13") },
    new AsTableLog{ msg = "msg06", createtime = DateTime.Parse("2022-4-8 15:00:13") },
    new AsTableLog{ msg = "msg07", createtime = DateTime.Parse("2022-6-8 15:00:13") },
    new AsTableLog{ msg = "msg07", createtime = DateTime.Parse("2022-7-1") }
};
var insert = fsql.Insert(testitems).NoneParameter();
var sql = insert.ToSql();
var affrows = insert.ExecuteAffrows(); //8

打印 sql：

INSERT INTO "as_table_log_202201"("id", "msg", "createtime") 
VALUES('6251ab15-de39-2c44-00cd-f3e2107234ea', 'msg01', '2022-01-01 13:00:11'), ('6251ab15-de39-2c44-00cd-f3e3539107ec', 'msg02', '2022-01-02 14:00:12')

;

INSERT INTO "as_table_log_202202"("id", "msg", "createtime") 
VALUES('6251ab15-de39-2c44-00cd-f3e4091b14d1', 'msg03', '2022-02-02 15:00:13'), ('6251ab15-de39-2c44-00cd-f3e57979e2c9', 'msg04', '2022-02-08 15:00:13')

;

INSERT INTO "as_table_log_202203"("id", "msg", "createtime") 
VALUES('6251ab15-de39-2c44-00cd-f3e66ee710f6', 'msg05', '2022-03-08 15:00:13')

;

INSERT INTO "as_table_log_202204"("id", "msg", "createtime") 
VALUES('6251ab15-de39-2c44-00cd-f3e772ebeae2', 'msg06', '2022-04-08 15:00:13')

;

INSERT INTO "as_table_log_202206"("id", "msg", "createtime") 
VALUES('6251ab15-de39-2c44-00cd-f3e87a3b06f7', 'msg07', '2022-06-08 15:00:13')

;

INSERT INTO "as_table_log_202207"("id", "msg", "createtime") 
VALUES('6251ab15-de39-2c44-00cd-f3e94bbd0908', 'msg07', '2022-07-01 00:00:00')

4、删除数据

根据 id 删除一行数据，可能要每个分表都行进删除操作，FreeSql 可以根据 sql where 缩小执行范围

var delete = fsql.Delete<AsTableLog>()
    .Where(a => a.id == id 
      && a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")));
var sql = delete.ToSql();
var affrows = delete.ExecuteAffrows();

打印 sql：

DELETE FROM "as_table_log_202205" WHERE ("id" = @exp_0 AND "createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')

;

DELETE FROM "as_table_log_202204" WHERE ("id" = @exp_0 AND "createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')

;

DELETE FROM "as_table_log_202203" WHERE ("id" = @exp_0 AND "createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')

分表太多了，当 删除/更新/查询 未设置时间条件时，只命中最新的 3个分表：

fsql.CodeFirst.GetTableByEntity(typeof(AsTableLog)).AsTableImpl.SetDefaultAllTables(value => value.Take(3).ToArray());

5 、更新数据

可根据实体对象对应分表，或者逆向解析 sql where 文本，缩小执行范围

用例1，更新单个实体

var update = fsql.Update<AsTableLog>().SetSource(testitems[0]);
var sql = update.ToSql();
var affrows = update.ExecuteAffrows();

打印 sql：

UPDATE "as_table_log_202201" SET "msg" = 'msg01', "createtime" = '2022-01-01 13:00:11' 
WHERE ("id" = '6251ab15-de39-2c44-00cd-f3e2107234ea')

用例2，批量更新实体

var update = fsql.Update<AsTableLog>().SetSource(testitems);
var sql = update.ToSql();
var affrows = update.ExecuteAffrows();

打印 sql：

UPDATE "as_table_log_202201" SET "msg" = CASE "id" 
WHEN '6251ab15-de39-2c44-00cd-f3e2107234ea' THEN 'msg01' 
WHEN '6251ab15-de39-2c44-00cd-f3e3539107ec' THEN 'msg02' END, "createtime" = CASE "id" 
WHEN '6251ab15-de39-2c44-00cd-f3e2107234ea' THEN '2022-01-01 13:00:11' 
WHEN '6251ab15-de39-2c44-00cd-f3e3539107ec' THEN '2022-01-02 14:00:12' END 
WHERE ("id" IN ('6251ab15-de39-2c44-00cd-f3e2107234ea','6251ab15-de39-2c44-00cd-f3e3539107ec'))

;

UPDATE "as_table_log_202202" SET "msg" = CASE "id" 
WHEN '6251ab15-de39-2c44-00cd-f3e4091b14d1' THEN 'msg03' 
WHEN '6251ab15-de39-2c44-00cd-f3e57979e2c9' THEN 'msg04' END, "createtime" = CASE "id" 
WHEN '6251ab15-de39-2c44-00cd-f3e4091b14d1' THEN '2022-02-02 15:00:13' 
WHEN '6251ab15-de39-2c44-00cd-f3e57979e2c9' THEN '2022-02-08 15:00:13' END 
WHERE ("id" IN ('6251ab15-de39-2c44-00cd-f3e4091b14d1','6251ab15-de39-2c44-00cd-f3e57979e2c9'))

;

UPDATE "as_table_log_202203" SET "msg" = 'msg05', "createtime" = '2022-03-08 15:00:13' 
WHERE ("id" = '6251ab15-de39-2c44-00cd-f3e66ee710f6')

;

UPDATE "as_table_log_202204" SET "msg" = 'msg06', "createtime" = '2022-04-08 15:00:13' 
WHERE ("id" = '6251ab15-de39-2c44-00cd-f3e772ebeae2')

;

UPDATE "as_table_log_202206" SET "msg" = 'msg07', "createtime" = '2022-06-08 15:00:13' 
WHERE ("id" = '6251ab15-de39-2c44-00cd-f3e87a3b06f7')

;

UPDATE "as_table_log_202207" SET "msg" = 'msg07', "createtime" = '2022-07-01 00:00:00' 
WHERE ("id" = '6251ab15-de39-2c44-00cd-f3e94bbd0908')

用例3，直接更新字段（不传递实体）

var update = fsql.Update<AsTableLog>(Guid.NewGuid()).Set(a => a.msg == "newmsg");
var sql = update.ToSql();
var affrows = update.ExecuteAffrows();

此时会在所有分表，都执行一次 SQL

打印 sql：

UPDATE "as_table_log_202207" SET "msg" = 'newmsg' 
WHERE ("id" = 'cfc9d52a-e4a3-4f0a-83af-c7f113c89fbd')

;

UPDATE "as_table_log_202206" SET "msg" = 'newmsg' 
WHERE ("id" = 'cfc9d52a-e4a3-4f0a-83af-c7f113c89fbd')

;

UPDATE "as_table_log_202205" SET "msg" = 'newmsg' 
WHERE ("id" = 'cfc9d52a-e4a3-4f0a-83af-c7f113c89fbd')

;

UPDATE "as_table_log_202204" SET "msg" = 'newmsg' 
WHERE ("id" = 'cfc9d52a-e4a3-4f0a-83af-c7f113c89fbd')

;

UPDATE "as_table_log_202203" SET "msg" = 'newmsg' 
WHERE ("id" = 'cfc9d52a-e4a3-4f0a-83af-c7f113c89fbd')

;

UPDATE "as_table_log_202202" SET "msg" = 'newmsg' 
WHERE ("id" = 'cfc9d52a-e4a3-4f0a-83af-c7f113c89fbd')

;

UPDATE "as_table_log_202201" SET "msg" = 'newmsg' 
WHERE ("id" = 'cfc9d52a-e4a3-4f0a-83af-c7f113c89fbd')

用例4，直接更新字段，传入 where 时间缩小执行范围（不传递实体）

var update = fsql.Update<AsTableLog>(Guid.NewGuid())
    .Set(a => a.msg == "newmsg")
    .Where(a => a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")));
var sql = update.ToSql();
var affrows = update.ExecuteAffrows();

没有传入实体对象，此时根据 sql where 解析分表，缩小执行范围

打印 sql：

UPDATE "as_table_log_202205" SET "msg" = 'newmsg' 
WHERE ("id" = '4c3ccf9d-9a5c-4de9-9479-995c65880a85') AND ("createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')

;

UPDATE "as_table_log_202204" SET "msg" = 'newmsg' 
WHERE ("id" = '4c3ccf9d-9a5c-4de9-9479-995c65880a85') AND ("createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')

;

UPDATE "as_table_log_202203" SET "msg" = 'newmsg' 
WHERE ("id" = '4c3ccf9d-9a5c-4de9-9479-995c65880a85') AND ("createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')

6、查询数据，根据 sql where 缩小执行范围

var select = fsql.Select<AsTableLog>()
    .Where(a => a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")));
var sql = select.ToSql();
var list = select.ToList();

SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202205" a 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')) ftb
 
UNION ALL
 
SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202204" a 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')) ftb
 
UNION ALL
 
SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202203" a 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')) ftb

分表 join 普通表：

var select = fsql.Select<AsTableLog, Sys_owner>()
    .InnerJoin((a, b) => a.id == b.Id)
    .Where((a, b) => a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")))
    .OrderBy((a, b) => a.createtime);
var sql = select.ToSql();
var list = select.ToList();

SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202205" a 
INNER JOIN “sys_owner” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb
 
UNION ALL
 
SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202204" a 
INNER JOIN “sys_owner” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb
 
UNION ALL
 
SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202203" a 
INNER JOIN “sys_owner” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb

普通表 join 分表：

var select = fsql.Select<Sys_owner, AsTableLog>()
    .InnerJoin((a, b) => a.Id == b.id)
    .Where((a, b) => b.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")))
    .OrderBy((a, b) => b.createtime);
var sql = select.ToSql();
var list = select.ToList();

SELECT  * from (SELECT a."id", a."reguserid" 
FROM "sys_owner" a 
INNER JOIN “as_table_log_202205” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb
 
UNION ALL
 
SELECT  * from (SELECT a."id", a."reguserid" 
FROM "sys_owner" a 
INNER JOIN “as_table_log_202204” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb
 
UNION ALL
 
SELECT  * from (SELECT a."id", a."reguserid" 
FROM "sys_owner" a 
INNER JOIN “as_table_log_202203” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb

分表 join 分表：

var select = fsql.Select<AsTableLog, AsTableLogExt>()
    .InnerJoin((a, b) => a.id == b.id)
    .Where((a, b) => a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")))
    .OrderBy((a, b) => a.createtime);
var sql = select.ToSql();
var list = select.ToList();

SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202205" a 
INNER JOIN “as_table_logext_202205” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb
 
UNION ALL
 
SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202204" a 
INNER JOIN “as_table_logext_202204” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb
 
UNION ALL
 
SELECT  * from (SELECT a."id", a."msg", a."createtime" 
FROM "as_table_log_202203" a 
INNER JOIN “as_table_logext_202203” b ON a."id" = b."id" 
WHERE (a."createtime" between '2022-03-01 00:00:00' and '2022-05-01 00:00:00')
ORDER BY a.”createtime“) ftb

嵌套查询：

var query2 = fsql.Select<AsTableLogExt>()
    .Where(a => a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")));

var select = fsql.Select<AsTableLog>()
    .Where(a => a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")))
    .WithTempQuery(a => a) //将 AsTableLog 独立
    .FromQuery(query2)
    .InnerJoin((a, b) => a.id == b.id)
    .OrderBy((a, b) => a.createtime);
var sql = select.ToSql();
var list = select.ToList();

SELECT * 
FROM ( 
    SELECT  * from (SELECT a."id", a."msg", a."createtime", a."click" 
    FROM "as_table_log_202205" a 
    WHERE (a."createtime" between '2022-03-01 00:00:00.000' and '2023-05-01 00:00:00.000')) ftb
 
    UNION ALL
 
    SELECT  * from (SELECT a."id", a."msg", a."createtime", a."click" 
    FROM "as_table_log_202204" a 
    WHERE (a."createtime" between '2022-03-01 00:00:00.000' and '2023-05-01 00:00:00.000')) ftb
 
    UNION ALL
 
    SELECT  * from (SELECT a."id", a."msg", a."createtime", a."click" 
    FROM "as_table_log_202203" a 
    WHERE (a."createtime" between '2022-03-01 00:00:00.000' and '2023-05-01 00:00:00.000')) ftb ) a 
INNER JOIN ( 
    SELECT  * from (SELECT a."id", a."ext" 
    FROM "as_table_logext_202205" a 
    WHERE (a."createtime" between '2022-03-01 00:00:00.000' and '2023-05-01 00:00:00.000')) ftb
 
    UNION ALL
 
    SELECT  * from (SELECT a."id", a."ext" 
    FROM "as_table_logext_202204" a 
    WHERE (a."createtime" between '2022-03-01 00:00:00.000' and '2023-05-01 00:00:00.000')) ftb
 
    UNION ALL
 
    SELECT  * from (SELECT a."id", a."ext" 
    FROM "as_table_logext_202203" a 
    WHERE (a."createtime" between '2022-03-01 00:00:00.000' and '2023-05-01 00:00:00.000')) ftb) b ON a."id" = b."id" 
ORDER BY a."createtime"

xuejmnet · 2022-04-09T08:53:57Z

xuejmnet
Apr 9, 2022

0.0说来惭愧本来联系叶老板打算做freesql的分片支持结果工作上很忙时间一直抽不出来,不过这边提一点意见

分片规则我感觉应该提取出来作为用户自行实现会比较好,因为有可能存在历史数据的偏斜，也可能说用户的分片需要自定义
分片聚合才用union all确实是分表下最简单并且相对性能不是非常差，但是是否应该需要提供stream聚合来提高性能

1 reply

2881099 Apr 9, 2022
Maintainer Author

目前先完成常用按日期分表的场景，第二期再考虑其他类型，比如从按主键部分特点分表。
stream聚合指什么？

xuejmnet · 2022-04-09T09:00:57Z

xuejmnet
Apr 9, 2022

目前框架提供的自动分表其实就我个人经验而言并不是一个功能,严格意义上来说应该是两个功能

1.框架提供路由
2.框架提供动态分片(创建表)

以上两点其实就是自动分表的本质,并且如果框架提供了以上两种功能应该说就可以实现所有的分片路由规则，至于是否自动分表其实就是动态分片在程序里面的应用而已,自动并不是真正意义的自动,而是框架提供了动态添加路由,刚好根据时间又可以解析出分片所需的后缀仅此而已

2 replies

2881099 Apr 9, 2022
Maintainer Author

因为 FreeSql 功能太复杂（实体和无实体操作都支持，并且都是常用的），其他 ORM 传递实体对象的就简单很多。

目前适合提供常用的场景，尽量轻量方便，简单配置开箱即用，由于功能复杂直接开放路由使用者很难对接。

xuejmnet Apr 9, 2022

开箱即用这个我觉得不是什么问题，无非是是否提供默认理由，stream聚合其实就是datareader每次read的时候通过优先级队列对多个并行语句进行优先级返回

2881099 · 2022-04-09T09:21:42Z

2881099
Apr 9, 2022
Maintainer Author

[Table(Name = "as_table_log_{yyyyMM}", AsTable = "createtime=2022-1-1(1 month)")]
class AsTableLog
{
    public Guid id { get; set; }
    public string msg { get; set; }
    public DateTime createtime { get; set; }
}

我想再集成一个功能，id 是 FreeSql 创建，并且它的值包含 createtime，那么

根据 id 查询时，可以直接解析出 createtime 值，同时也可以直接定位到对应的分表

13 replies

hajiuxbz Apr 10, 2022

是否做做个接口让实体继承
iastable
string[] xxx(entity)
计算逻辑
这样只需要缓存这个方法每次查询或者插入通过它就可以得到想要查的表名

2881099 Apr 10, 2022
Maintainer Author

是否做做个接口让实体继承 iastable string[] xxx(entity) 计算逻辑这样只需要缓存这个方法每次查询或者插入通过它就可以得到想要查的表名

插入好解决，因为必传实体entity，查询没有实体。

hajiuxbz Apr 10, 2022

查询不是有表达式吗而且查询条件属性都是来源实体呀数据应该可以是可以映射到实体呀

hajiuxbz Apr 10, 2022

哦确实没我想简单了。。条件是有or and关系的抱歉

xuejmnet Apr 10, 2022

哦确实没我想简单了。。条件是有or and关系的抱歉

表达式除了and 和or还有>,>=,<,<=,=,!=目前来说我的解决方案是最优的接口返回Expression<Func<string,bool>> GetRouteTail(object shardingValue,ShardingOperate operate,string propertyName)这个接口

MrZol · 2022-04-12T08:34:55Z

MrZol
Apr 12, 2022

能否提供db-first场景下的按时间日期分表

1 reply

2881099 Apr 12, 2022
Maintainer Author

请提供下完整思路

jin337359649 · 2022-05-16T07:18:24Z

jin337359649
May 16, 2022

当采用子查询的写法时，子表的时间过滤有问题。

主表和子表都是按照月份分表，从2022-1-1开始，主表成功按照时间筛选只查了一张表，子表只查了5月那张表，但是UNION ALL了5次

查询的表达式

            //  beginTime = 2022-05-09 
            var source = Orm.Select<GxpAuditTrailEntity>()
                .Where(o => Orm.Select<GxpAuditTrailItemEntity>().Any(s => s.EntityId == dto.EntityId && s.EntityName == dto.EntityName 
                && s.AuditId == o.Id && s.CreatedTime > beginTime))
                .Where(e => e.OrganCode == organCode && e.CreatedTime > beginTime);

生成的sql

SELECT count(1) as1
FROM `gxp_audit_trail_202205` a
WHERE (exists(SELECT  * from (SELECT 1
    FROM `gxp_audit_trail_item_202205` s
    WHERE (s.`EntityId` = 0 AND s.`EntityName`  IS  NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
    limit 0,1) ftb

    UNION ALL

    SELECT  * from (SELECT 1
    FROM `gxp_audit_trail_item_202205` s
    WHERE (s.`EntityId` = 0 AND s.`EntityName`  IS  NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
    limit 0,1) ftb

    UNION ALL

    SELECT  * from (SELECT 1
    FROM `gxp_audit_trail_item_202205` s
    WHERE (s.`EntityId` = 0 AND s.`EntityName`  IS  NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
    limit 0,1) ftb

    UNION ALL

    SELECT  * from (SELECT 1
    FROM `gxp_audit_trail_item_202205` s
    WHERE (s.`EntityId` = 0 AND s.`EntityName`  IS  NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
    limit 0,1) ftb

    UNION ALL

    SELECT  * from (SELECT 1
    FROM `gxp_audit_trail_item_202205` s
    WHERE (s.`EntityId` = 0 AND s.`EntityName`  IS  NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
    limit 0,1) ftb)) AND (a.`OrganCode` = '#000964' AND a.`CreatedTime` > '2022-05-09 15:05:16.959')

2 replies

2881099 May 16, 2022
Maintainer Author

自动分表多表查询时比较麻烦，建议改成手工指定分表名：

Orm.Select<GxpAuditTrailEntity>()
     .AsTable((_, old) => $"{old}_202205")
                .Where(o => Orm.Select<GxpAuditTrailItemEntity>().Any(s => s.EntityId == dto.EntityId && s.EntityName == dto.EntityName 
                && s.AuditId == o.Id && s.CreatedTime > beginTime))
                .Where(e => e.OrganCode == organCode && e.CreatedTime > beginTime);

jin337359649 May 17, 2022

感谢，这样可以解决
还有一个问题，如果在条件表达式中，使用时间的Date，会导致无法识别表的过滤，看了下源码可能是正则匹配的问题。提取出变量可以正常识别。


            //无法识别
            var source1 = Orm.Select<GxpAuditTrailItemEntity>()
                .Where(p => p.CreatedTime.Between(dto.CreatedTime.Date, dto.CreatedTime.Date.AddDays(1)));

            //正常识别
            var startTime = dto.CreatedTime.Date;
            var endTime = dto.CreatedTime.Date.AddDays(1);
            var source = Orm.Select<GxpAuditTrailItemEntity>()
                .Where(p => p.CreatedTime.Between(startTime, endTime));

2881099 · 2022-05-25T10:48:36Z

2881099
May 25, 2022
Maintainer Author

自定义分表暂时可以这样：

第一步：class DateTimeAsTableImpl : IAsTable 把这个类复制出去，改成你想要的

第二步：fsql Build 之后执行下面代码

var tb = fsql.CodeFirst.GetTableByEntity(typeof(分表的实体类));
var atm = Regex.Match(tb.AsTable, @"([\w_\d]+)\s*=\s*(\d\d\d\d)\s*\-\s*(\d\d?)\s*\-\s*(\d\d?)\s*\((\d+)\s*(year|month|day|hour)\)", RegexOptions.IgnoreCase);
var astableImpl = new DateTimeAsTableImpl(Name, DateTime.Parse($"{atm.Groups[2].Value}-{atm.Groups[3].Value}-{atm.Groups[4].Value}"), dt =>
{
    switch (atm6)
    {
        case "year": return dt.AddYears(atm5);
        case "month": return dt.AddMonths(atm5);
        case "day": return dt.AddDays(atm5);
        case "hour": return dt.AddHours(atm5);
    }
    throw new NotImplementedException();
});
tb.GetType().GetProperty("AsTableImpl").SetValue(astableImpl, new object[0]);

3 replies

xuejmnet May 25, 2022

对了叶老板话说分表你这个按时间应该支持大于小于之类的操作的吧，我这边问下比如按月分表如果小于某个月的初始天那么这个月是否会返回，原则上应该是不返回

2881099 May 25, 2022
Maintainer Author

是的不返回，现在有人就碰到这个问题了，他需要返回并且表名是不带日期的原始名字。比如 log，而不是 log2022

2881099 Dec 11, 2023
Maintainer Author

v3.2.806

第一个表12个月，后面的表按1个月：

AsTable = "createtime=2022-1-1(12,1 month)"

第一个表非时间命名：

fsql.CodeFirst.GetTableByEntity(typeof(AsTableLog)).AsTableImpl.SetTableName(0, "自定义表名")

sukney · 2022-06-22T14:50:00Z

sukney
Jun 22, 2022

在不开启自动迁移的情况下，有没有办法手工创建分表，oracle当数据量大的时候自动迁移有时候会卡住。

3 replies

2881099 Jun 22, 2022
Maintainer Author

fsql.Codefirst.SyncStructure 方法可以手动建表

2881099 Jun 22, 2022
Maintainer Author

fsql.Codefirst.SyncStructure 方法可以手动建表

var tb = fsql.CodeFirst.GetTableByEntity(typeof(T));

tb.AsTableImpl.GetTableByXxx 可以得到表名

2881099 Jun 23, 2022
Maintainer Author

如果启动时创建表：

var tb = fsql.CodeFirst.GetTableByEntity(typeof(T));
string[] names = tb.AsTableImpl.GetTableNamesByColumnValueRange(DateTime.Parse("2022-1-1"), DateTime.Parse("2022-7-1"));
foreach (var name in names)
    fsql.CodeFirst.SyncStructure(typeof(T), name);


//string name = tb.AsTableImpl.GetTableNameByColumnValue(DateTime.Parse("2022-7-1")); 
//获取指定日期对应的表名

d4ilys · 2022-08-02T05:50:30Z

d4ilys
Aug 2, 2022

叶老板现在自动分表不支持分页查询吗

1 reply

2881099 Aug 2, 2022
Maintainer Author

分页可能会比较慢，试试和 WithTempQuery 结合

fsql.Select<T>()
    .Where(时间...)
    .WithTempQuery(a => new { item = a })
    .Page(10, 10)
    .ToList();

sukney · 2022-11-20T02:23:02Z

sukney
Nov 20, 2022

开启自动迁移就可以

…

---原始邮件--- 发件人: "Alex ***@***.***> 发送时间: 2022年11月20日(周日) 上午8:48 收件人: ***@***.***>; 抄送: ***@***.******@***.***>; 主题: Re: [dotnetcore/FreeSql] v3.2 新功能【自动分表】讨论与反馈 (Discussion #1066) 现阶段是每次都会执行自动迁移脚本吧? 有人生产用了吗 — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: ***@***.***>

0 replies

dazhu6666 · 2022-11-29T03:24:59Z

dazhu6666
Nov 29, 2022

我目前的情况
1.A程序，主要负责写入数据，预期插入的时候自动建表，目前我使用自动分表实现
2.B程序，主要负责处理数据，各种不同的查询条件，例如日期查询，预期是，按需查询对应日期的所在的表，处理数据后，写数据能自动分表
3.C程序，主要负责Api查询，纯查询，预期按日期查询对应的表，无需建表，但是我发现不使用astable不好使，用了禁用自动迁移又会报错

问，这三个程序。怎么用freesql为最佳实践

2 replies

2881099 Nov 30, 2022
Maintainer Author

分表间隔一致会比较好操作，如果没有规律就需要用 AsTable 手工指定表名进行 crud 操作啦。

2881099 Dec 11, 2023
Maintainer Author

v3.2.806

第一个表12个月，后面的表按1个月：

AsTable = "createtime=2022-1-1(12,1 month)"

第一个表非时间命名：

fsql.CodeFirst.GetTableByEntity(typeof(AsTableLog)).AsTableImpl.SetTableName(0, "自定义表名")

Ed2022Ward · 2024-01-05T03:51:14Z

Ed2022Ward
Jan 5, 2024

采用按周自动分表，数据库是Mysql，数据库配置已优化，现在是定时（每隔2秒）拆入几条数据，每张表数据量在几十万条，正常插入几条数据耗时不到100ms，一天过程中可能出现十几次次插入数据耗时几秒或者十几秒，这种情况要从哪些方面着手？

1 reply

2881099 Jan 5, 2024
Maintainer Author

把耗时的前后 SQL 监视下来分析

Ed2022Ward · 2024-01-05T05:40:14Z

Ed2022Ward
Jan 5, 2024

您好，     SQL语句里面就是insert.....，看不出什么异常，另外之前看了mysql的bin日志只是记录现场，分析不出是什么原因。

…

------------------ 原始邮件 ------------------ 发件人: "dotnetcore/FreeSql" ***@***.***>; 发送时间: 2024年1月5日(星期五) 中午12:12 ***@***.***>; ***@***.******@***.***>; 主题: Re: [dotnetcore/FreeSql] v3.2 新功能【自动分表】讨论与反馈 (Discussion #1066) 把耗时的前后 SQL 监视下来分析 — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: ***@***.***>

0 replies

densen2014 · 2024-03-19T22:20:58Z

densen2014
Mar 19, 2024
Collaborator

使用分页+WithTempQuery+OrderBy出现问题

SELECT TOP 40 *
FROM (
    SELECT  * from (SELECT a.[ID], a.[OrderID], a.[Createtime], a.[Name]
    FROM [A_Log_202403] a
    ORDER BY a.[Createtime]) ftb

    UNION ALL

    SELECT  * from (SELECT a.[ID], a.[OrderID], a.[Createtime], a.[Name]
    FROM [A_Log_202402] a
    ORDER BY a.[Createtime]) ftb

    UNION ALL

    SELECT  * from (SELECT a.[ID], a.[OrderID], a.[Createtime], a.[Name]
    FROM [A_Log_202401] a
    ORDER BY a.[Createtime]) ftb

    UNION ALL

    SELECT  * from (SELECT a.[ID], a.[OrderID], a.[Createtime], a.[Name]
    FROM [A_Log_202312] a
    ORDER BY a.[Createtime]) ftb

    UNION ALL

    SELECT  * from (SELECT a.[ID], a.[OrderID], a.[Createtime], a.[Name]
    FROM [A_Log] a
    ORDER BY a.[Createtime]) ftb ) a

FreeSqlDataService Error: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

1 reply

densen2014 Mar 20, 2024
Collaborator

解决:
先WithTempQuery , 再排序, 和分页

heringlee · 2024-06-14T07:58:17Z

heringlee
Jun 14, 2024

2 replies

densen2014 Jun 14, 2024
Collaborator

say something

2881099 Jun 14, 2024
Maintainer Author

分表场景的索引可以这样：[Index("{tablename}_idx_01", "phone")]

EVA-SS · 2025-03-26T02:08:27Z

EVA-SS
Mar 26, 2025

时间戳可以分表吗毫秒级的时间戳

0 replies

This comment has been minimized.

Sign in to view

v3.2 新功能【自动分表】讨论与反馈 #1066

Uh oh!

Uh oh!

2881099 Apr 9, 2022 Maintainer

1、定义特性

2、创建分表

3、插入数据时，根据对应属性值，自动对应分表

4、删除数据

5 、更新数据

6、查询数据，根据 sql where 缩小执行范围

Replies: 16 comments · 32 replies

Uh oh!

Uh oh!

2881099 Apr 9, 2022 Maintainer Author

Uh oh!

Uh oh!

Uh oh!

2881099 Apr 9, 2022 Maintainer Author

Uh oh!

Uh oh!

Uh oh!

2881099 Apr 9, 2022 Maintainer Author

Uh oh!

Uh oh!

2881099 Apr 10, 2022 Maintainer Author

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

2881099 Apr 12, 2022 Maintainer Author

Uh oh!

当采用子查询的写法时，子表的时间过滤有问题。

查询的表达式

生成的sql

Uh oh!

2881099 May 16, 2022 Maintainer Author

Uh oh!

Uh oh!

2881099 May 25, 2022 Maintainer Author

Uh oh!

Uh oh!

2881099 May 25, 2022 Maintainer Author

Uh oh!

2881099 Dec 11, 2023 Maintainer Author

Uh oh!

Uh oh!

2881099 Jun 22, 2022 Maintainer Author

Uh oh!

2881099 Jun 22, 2022 Maintainer Author

Uh oh!

Uh oh!

2881099 Jun 23, 2022 Maintainer Author

Uh oh!

Uh oh!

Uh oh!

2881099 Aug 2, 2022 Maintainer Author

Uh oh!

2881099
Apr 9, 2022
Maintainer

Replies: 16 comments 32 replies

2881099 Apr 9, 2022
Maintainer Author

2881099 Apr 9, 2022
Maintainer Author

2881099
Apr 9, 2022
Maintainer Author

2881099 Apr 10, 2022
Maintainer Author

2881099 Apr 12, 2022
Maintainer Author

2881099 May 16, 2022
Maintainer Author

2881099
May 25, 2022
Maintainer Author

2881099 May 25, 2022
Maintainer Author

2881099 Dec 11, 2023
Maintainer Author

2881099 Jun 22, 2022
Maintainer Author

2881099 Jun 22, 2022
Maintainer Author

2881099 Jun 23, 2022
Maintainer Author

2881099 Aug 2, 2022
Maintainer Author