跳至主要內容

新增

nicye大约 6 分钟约 1806 字

新增

FreeSql 提供单条和批量插入数据的方法,在特定的数据库执行还可以返回插入后的记录。

var connectionString = "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;" +
    "Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=10";

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
    .UseAutoSyncStructure(true) //自动同步实体结构到数据库
    .Build(); //请务必定义成 Singleton 单例模式

class Topic {
    [Column(IsIdentity = true, IsPrimary = true)]
    public int Id { get; set; }
    public int Clicks { get; set; }
    public string Title { get; set; }
    public DateTime CreateTime { get; set; }
}

var items = new List<Topic>();
for (var a = 0; a < 10; a++) items.Add(new Topic { Title = $"newtitle{a}", Clicks = a * 100 });

1、单条插入

var t1 = fsql.Insert(items[0]).ExecuteAffrows();
//INSERT INTO `Topic`(`Clicks`, `Title`, `CreateTime`)
//VALUES(?Clicks0, ?Title0, ?CreateTime0)

如果表有自增列,插入数据后应该要返回 id。

方法 1:(原始)

long id = fsql.Insert(items[0]).ExecuteIdentity();
items[0].Id = id;

方法 2:(依赖 FreeSql.Repository)

var repo = fsql.GetRepository<Topic>();
repo.Insert(items[0]);

内部会将插入后的自增值填充给 items[0].Id (支持批量插入回填)

DbFirst 模式序列:[Column(IsIdentity = true, InsertValueSql = "seqname.nextval")]

2、批量插入

var t2 = fsql.Insert(items).ExecuteAffrows();
//INSERT INTO `Topic`(`Clicks`, `Title`, `CreateTime`)
//VALUES(?Clicks0, ?Title0, ?CreateTime0), (?Clicks1, ?Title1, ?CreateTime1),
//(?Clicks2, ?Title2, ?CreateTime2), (?Clicks3, ?Title3, ?CreateTime3),
//(?Clicks4, ?Title4, ?CreateTime4), (?Clicks5, ?Title5, ?CreateTime5),
//(?Clicks6, ?Title6, ?CreateTime6), (?Clicks7, ?Title7, ?CreateTime7),
//(?Clicks8, ?Title8, ?CreateTime8), (?Clicks9, ?Title9, ?CreateTime9)

解决了 SqlServer 批量添加容易导致的错误:传入的请求具有过多的参数。该服务器支持最多 2100 个参数。请减少参数的数目,然后重新发送该请求。

原理为拆成多个包用事务执行;

当插入大批量数据时,内部采用分割分批执行的逻辑进行。分割规则如下:

数量参数量
MySql50003000
PostgreSQL50003000
SqlServer10002100
Oracle500999
Sqlite5000999

数量:为每批分割的大小,如批量插入 10000 条数据,在 mysql 执行时会分割为两批。
参数量:为每批分割的参数量大小,如批量插入 10000 条数据,每行需要使用 5 个参数化,在 mysql 执行时会分割为每批 3000 / 5。

分割执行后,当外部未提供事务时,内部自开事务,实现插入完整性。也可以通过 BatchOptions 设置合适的值。

FreeSql 适配了每一种数据类型参数化,和不参数化的使用。批量插入建议关闭参数化功能,使用 .NoneParameter() 进行执行。

3、BulkCopy

程序包扩展方法说明
FreeSql.Provider.SqlServerExecuteSqlBulkCopy
FreeSql.Provider.MySqlConnectorExecuteMySqlBulkCopy
FreeSql.Provider.OracleExecuteOracleBulkCopy
FreeSql.Provider.DamengExecuteDmBulkCopy达梦
FreeSql.Provider.PostgreSQLExecutePgCopy
FreeSql.Provider.KingbaseESExecuteKdbCopy人大金仓

批量插入测试参考(52 个字段)

18W1W5K2K1K50010050
MySql 5.5 ExecuteAffrows38,4812,2341,1362842391676630
MySql 5.5 ExecuteMySqlBulkCopy28,4051,1426574514355924722
SqlServer Express ExecuteAffrows402,35524,84711,4654,9712,43791513888
SqlServer Express ExecuteSqlBulkCopy21,065578326139105796048
PostgreSQL 10 ExecuteAffrows46,7563,2942,2691,0193742095137
PostgreSQL 10 ExecutePgCopy10,09058333713688613025

18W 解释:插入 18 万行记录,表格中的数字是执行时间(单位 ms)

批量插入测试参考(10 个字段)

18W1W5K2K1K50010050
MySql 5.5 ExecuteAffrows11,1718663668083502434
MySql 5.5 ExecuteMySqlBulkCopy6,504399257116871001616
SqlServer Express ExecuteAffrows47,2042,2751,1084882791233516
SqlServer Express ExecuteSqlBulkCopy4,248127713048141110
PostgreSQL 10 ExecuteAffrows9,7865683361571023496
PostgreSQL 10 ExecutePgCopy4,0811679339211242

测试结果,是在相同操作系统下进行的,并且都有预热

4、动态表名

fsql.Insert(items).AsTable("Topic_201903").ExecuteAffrows(); //对 Topic_201903 表插入

5、插入指定的列

var t3 = fsql.Insert(items).InsertColumns(a => a.Title).ExecuteAffrows();
//INSERT INTO `Topic`(`Title`)
//VALUES(?Title0), (?Title1), (?Title2), (?Title3), (?Title4),
//(?Title5), (?Title6), (?Title7), (?Title8), (?Title9)

var t4 = fsql.Insert(items).InsertColumns(a =>new { a.Title, a.Clicks }).ExecuteAffrows();
//INSERT INTO `Topic`(`Clicks`, `Title`)
//VALUES(?Clicks0, ?Title0), (?Clicks1, ?Title1), (?Clicks2, ?Title2),
//(?Clicks3, ?Title3), (?Clicks4, ?Title4), (?Clicks5, ?Title5),
//(?Clicks6, ?Title6), (?Clicks7, ?Title7), (?Clicks8, ?Title8),
//(?Clicks9, ?Title9)

6、忽略列

var t5 = fsql.Insert(items).IgnoreColumns(a => a.CreateTime).ExecuteAffrows();
//INSERT INTO `Topic`(`Clicks`, `Title`)
//VALUES(?Clicks0, ?Title0), (?Clicks1, ?Title1), (?Clicks2, ?Title2),
//(?Clicks3, ?Title3), (?Clicks4, ?Title4), (?Clicks5, ?Title5),
//(?Clicks6, ?Title6), (?Clicks7, ?Title7), (?Clicks8, ?Title8),
//(?Clicks9, ?Title9)

var t6 = fsql.Insert(items).IgnoreColumns(a => new { a.Title, a.CreateTime }).ExecuteAffrows();
///INSERT INTO `Topic`(`Clicks`)
//VALUES(?Clicks0), (?Clicks1), (?Clicks2), (?Clicks3), (?Clicks4),
//(?Clicks5), (?Clicks6), (?Clicks7), (?Clicks8), (?Clicks9)

7、列插入优先级

全部列 < 指定列(InsertColumns) < 忽略列(IgnoreColumns)

在没有使用 InsertColumns/IgnoreColumns 的情况下,实体所有列将被插入数据库;

在使用 InsertColumns,没有使用 IgnoreColumns 的情况下,只有指定的列插入数据库;

在使用 IgnoreColumns 的情况下,只有未被指定的列插入数据库;

8、字典插入

var dic = new Dictionary<string, object>();
dic.Add("id", 1);
dic.Add("name", "xxxx");

fsql.InsertDict(dic).AsTable("table1").ExecuteAffrows();

9、导入表数据

int affrows = fsql.Select<Topic>()
  .Limit(10)
  .InsertInto(null, a => new Topic2
  {
    Title = a.Title
  });
INSERT INTO `Topic2`(`Title`, `Clicks`, `CreateTime`)
SELECT a.`Title`, 0, '0001-01-01 00:00:00'
FROM `Topic` a
limit 10

注意:因为 ClicksCreateTime 没有被选择,所以使用目标实体属性[Column(InsertValueSql = xx)] 设置的值,或者使用目标实体属性的 c#默认值。

10、MySql 特有功能 Insert Ignore Into

fsql.Insert<Topic>().MySqlIgnoreInto().AppendData(items).ExecuteAffrows();
///INSERT IGNORE INTO `Topic`(`Clicks`)
//VALUES(?Clicks0), (?Clicks1), (?Clicks2), (?Clicks3), (?Clicks4),
//(?Clicks5), (?Clicks6), (?Clicks7), (?Clicks8), (?Clicks9)

11、MySql 特有功能 On Duplicate Key Update

FreeSql.Provider.MySql 和 FreeSql.Provider.MySqlConnector 支持 MySql 特有的功能,On Duplicate Key Update。

这个功能也可以实现插入或更新数据,并且支持批量操作。

class TestOnDuplicateKeyUpdateInfo {
    [Column(IsIdentity = true)]
    public int id { get; set; }
    public string title { get; set; }
    public DateTime time { get; set; }
}

var item = new TestOnDuplicateKeyUpdateInfo { id = 100, title = "title-100", time = DateTime.Parse("2000-01-01") };
fsql.Insert(item)
    .NoneParameter()
    .OnDuplicateKeyUpdate().ToSql();
//INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(100, 'title-100', '2000-01-01 00:00:00.000')
//ON DUPLICATE KEY UPDATE
//`title` = VALUES(`title`), 
//`time` = VALUES(`time`)

OnDuplicateKeyUpdate() 之后可以调用的方法:

方法名描述
IgnoreColumns忽略更新的列,机制和 IUpdate.IgnoreColumns 一样
UpdateColumns指定更新的列,机制和 IUpdate.UpdateColumns 一样
Set手工指定更新的列,与 IUpdate.Set 功能一样
SetRaw作为 Set 方法的补充,可传入 SQL 字符串
ToSql返回即将执行的 SQL 语句
ExecuteAffrows执行,返回影响的行数

IInsert 与 OnDuplicateKeyUpdate 都有 IgnoreColumns、UpdateColumns 方法。

当插入实体/集合实体的时候,忽略了 time 列,代码如下:

fsql.Insert(item)
    .IgnoreColumns(a => a.time)
    .NoneParameter()
    .OnDuplicateKeyUpdate().ToSql();
//INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`) VALUES(200, 'title-200')
//ON DUPLICATE KEY UPDATE
//`title` = VALUES(`title`), 
//`time` = '2000-01-01 00:00:00.000'

我们发现,UPDATE time 部分变成了常量,而不是 VALUES(`time`),机制如下:

当 insert 部分中存在的列,在 update 中将以 VALUES(`字段`) 的形式设置;

当 insert 部分中不存在的列,在 update 中将为常量形式设置,当操作实体数组的时候,此常量为 case when ... end 执行(与 IUpdate 一样);

API

方法返回值参数描述
AppendData<this>T1 | IEnumerable<T1>追加准备插入的实体
InsertIdentity<this>指明插入自增列
InsertColumns<this>Lambda只插入的列
IgnoreColumns<this>Lambda忽略的列
IgnoreInsertValueSql<this>Lambda忽略的设置过 InsertValueSql 的列
CommandTimeout<this>int命令超时设置(秒)
WithTransaction<this>DbTransaction设置事务对象
WithConnection<this>DbConnection设置连接对象
ToSqlstring返回即将执行的 SQL 语句
OnDuplicateKeyUpdateOnDuplicateKeyUpdate<T1>MySql 特有的功能,On Duplicate Key Update
OnConflictDoUpdateOnConflictDoUpdate<T1>PostgreSQL 特有的功能,On Conflict Do Update
ExecuteAffrowslong执行 SQL 语句,返回影响的行数
ExecuteIdentitylong执行 SQL 语句,返回自增值
ExecuteInsertedList<T1>执行 SQL 语句,返回插入后的记录
ExecuteSqlBulkCopyvoidSqlServer 特有的功能,执行 SqlBulkCopy 批量插入的封装
ExecutePgCopyvoidPostgreSQL 特有的功能,执行 Copy 批量导入数据