Skip to content

添加

Jump to bottom
2881099 edited this page Nov 5, 2020 · 60 revisions

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 { Id = a + 1, Title = $"newtitle{a}", Clicks = a * 100 });

1、单条插入

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

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

方法1：(原始)

long id = fsql.Insert(blog).ExecuteIdentity();
blog.Id = id;

方法2：(依赖 FreeSql.Repository)

var repo = fsql.GetRepository<Blog>();
repo.Insert(blog);

内部会将插入后的自增值填充给 blog.Id

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 个参数。请减少参数的数目，然后重新发送该请求。

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

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

数量 参数量
MySql 5000 3000
PostgreSQL 5000 3000
SqlServer 1000 2100
Oracle 500 999
Sqlite 5000 999

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

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

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

3、ExecuteSqlBulkCopy、ExecutePgCopy、ExecuteMySqlBulkCopy

Bulk Copy 操作以扩展方法的形式实现，针对 SqlServer/PostgreSQL/MySql 数据库，可用的包：FreeSql.Provider.SqlServer/FreeSql.Provider.PostgreSQL/FreeSql.Provider.MySqlConnector。

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

18W 1W 5K 2K 1K 500 100 50
MySql 5.5 ExecuteAffrows 38,481 2,234 1,136 284 239 167 66 30
MySql 5.5 ExecuteMySqlBulkCopy 28,405 1,142 657 451 435 592 47 22
SqlServer Express ExecuteAffrows 402,355 24,847 11,465 4,971 2,437 915 138 88
SqlServer Express ExecuteSqlBulkCopy 21,065 578 326 139 105 79 60 48
PostgreSQL 10 ExecuteAffrows 46,756 3,294 2,269 1,019 374 209 51 37
PostgreSQL 10 ExecutePgCopy 10,090 583 337 136 88 61 30 25

18W 解释：插入18万行记录，表格中的数字是执行时间（单位ms）

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

18W 1W 5K 2K 1K 500 100 50
MySql 5.5 ExecuteAffrows 11,171 866 366 80 83 50 24 34
MySql 5.5 ExecuteMySqlBulkCopy 6,504 399 257 116 87 100 16 16
SqlServer Express ExecuteAffrows 47,204 2,275 1,108 488 279 123 35 16
SqlServer Express ExecuteSqlBulkCopy 4,248 127 71 30 48 14 11 10
PostgreSQL 10 ExecuteAffrows 9,786 568 336 157 102 34 9 6
PostgreSQL 10 ExecutePgCopy 4,081 167 93 39 21 12 4 2

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

4、插入指定的列

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)

5、忽略列

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)

6、列插入优先级

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

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

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

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

7、导入表数据

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

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

8、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)

9、MySql 特有功能 On Duplicate Key Update

请移步查看详情

10、PostgreSQL 特有功能 On Conflict Do Update

请移步查看详情

参考资料

API

方法 返回值 参数 描述
AppendData <this> T1 | IEnumerable<T1> 追加准备插入的实体
InsertIdentity <this> 无 指明插入自增列
InsertColumns <this> Lambda 只插入的列
IgnoreColumns <this> Lambda 忽略的列
CommandTimeout <this> int 命令超时设置(秒)
WithTransaction <this> DbTransaction 设置事务对象
WithConnection <this> DbConnection 设置连接对象
ToSql string 返回即将执行的SQL语句
OnDuplicateKeyUpdate OnDuplicateKeyUpdate<T1> 无 MySql 特有的功能，On Duplicate Key Update
OnConflictDoUpdate OnConflictDoUpdate<T1> 无 PostgreSQL 特有的功能，On Conflict Do Update
ExecuteAffrows long 执行SQL语句，返回影响的行数
ExecuteIdentity long 执行SQL语句，返回自增值
ExecuteInserted List<T1> 执行SQL语句，返回插入后的记录
ExecuteSqlBulkCopy void SqlServer 特有的功能，执行 SqlBulkCopy 批量插入的封装
ExecutePgCopy void PostgreSQL 特有的功能，执行 Copy 批量导入数据

Basic

使用指南

学习与交流

信息

Clone this wiki locally