跳至主要內容

插入

nicye大约 5 分钟约 1565 字

插入

IFreeSql fsql; //如何创建请移步入门文档

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)

2、返回自增

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

方法 1:(原始)

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

方法 2:(依赖 FreeSql.Repository)

IBaseRepository<Topic> repo = fsql.GetRepository<Topic>();  //可以从 IOC 容器中获取
repo.Insert(items[0]);

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

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

3、批量插入

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)

批量插入建议关闭参数化功能,使用 .NoneParameter() 提升执行效率。

当插入大批量数据时,内部分批执行,规则如下:

数量参数量
MySql50003000
PostgreSQL50003000
SqlServer10002100
Oracle500999
Sqlite5000999

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

4、高性能 BulkCopy

程序包扩展方法说明
FreeSql.Provider.SqlServerExecuteSqlBulkCopy
FreeSql.Provider.MySqlConnectorExecuteMySqlBulkCopy
FreeSql.Provider.OracleExecuteOracleBulkCopy
FreeSql.Provider.PostgreSQLExecutePgCopy
FreeSql.Provider.DamengExecuteDmBulkCopy达梦
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

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

5、动态表名

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

6、插入指定列

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)

7、忽略列

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)

8、字典插入

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

fsql.InsertDict(dic).AsTable("table1").ExecuteAffrows();
//提示:List<Dictionary<string, object>> 为批量插入

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 TestInfo
{
    [Column(IsIdentity = true)]
    public int id { get; set; }
    public string title { get; set; }
    public DateTime time { get; set; }
}

var item = new TestInfo { id = 100, title = "title-100", time = DateTime.Parse("2000-01-01") };
fsql.Insert(item)
    .NoneParameter()
    .OnDuplicateKeyUpdate().ToSql();
//INSERT INTO `TestInfo`(`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 `TestInfo`(`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 一样);

12、PostgreSQL On Conflict Do Update

FreeSql.Provider.PostgreSQL 支持 PostgreSQL 9.5+ 特有的功能 On Conflict(id) Do Update,使用方法 MySql OnDuplicateKeyUpdate 大致相同。

fsql.Insert(items)
    .IgnoreColumns(a => a.time)
    .NoneParameter()
    .OnConflictDoUpdate().ToSql();
//INSERT INTO ""TestInfo""(""id"", ""title"") VALUES(200, 'title-200'), (201, 'title-201'), (202, 'title-202')
//ON CONFLICT(""id"") DO UPDATE SET
//""title"" = EXCLUDED.""title"",
//""time"" = CASE EXCLUDED.""id""
//WHEN 200 THEN '2000-01-01 00:00:00.000000'
//WHEN 201 THEN '2000-01-01 00:00:00.000000'
//WHEN 202 THEN '2000-01-01 00:00:00.000000' END::timestamp

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 批量导入数据