添加

2881099 edited this page 2023-12-14 19:45:30 +08:00

Table of Contents

• 1、单条插入
• 2、批量插入
• 3、BulkCopy
• 4、动态表名
• 5、插入指定的列
• 6、忽略列
• 7、列插入优先级
• 8、字典插入
• 9、导入表数据
• 10、MySql 特有功能 Insert Ignore Into
• 11、MySql 特有功能 On Duplicate Key Update
• 12、PostgreSQL 特有功能 On Conflict Do Update
• 参考资料
• API

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

中文 | English

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

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

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

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

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

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

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

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

3、BulkCopy

程序包	扩展方法	说明
FreeSql.Provider.SqlServer	ExecuteSqlBulkCopy
FreeSql.Provider.MySqlConnector	ExecuteMySqlBulkCopy
FreeSql.Provider.Oracle	ExecuteOracleBulkCopy
FreeSql.Provider.Dameng	ExecuteDmBulkCopy	达梦
FreeSql.Provider.PostgreSQL	ExecutePgCopy
FreeSql.Provider.KingbaseES	ExecuteKdbCopy	人大金仓

批量插入测试参考(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、动态表名

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

注意：因为 Clicks、CreateTime 没有被选择，所以使用目标实体属性 [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

请移步查看详情

12、PostgreSQL 特有功能 On Conflict Do Update

请移步查看详情

参考资料

• 《数据库事务》
• 《学习FreeSql之一：删除数据》
• 《学习FreeSql之二：修改数据》
• 《学习FreeSql之三：查询数据》
• 《仓储层Repository》
• 《过滤器、全局过滤器》
• 《分表、分库》
• 《租户》

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	设置连接对象
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 批量插入的封装，引入 FreeSql.Provider.SqlServer
ExecutePgCopy	void		PostgreSQL 特有的功能，执行 Copy 批量导入数据，引入 FreeSql.Provider.PostgreSQL
ExecuteMySqlBulkCopy	void		MySql 特有的功能，执行 BulkCopy 批量导入数据，引入 FreeSql.Provider.MysqlConnector
ExecuteOracleBulkCopy	void		Oracle 特有的功能，执行 BulkCopy 批量导入数据，引入 FreeSql.Provider.Oracle
ExecuteDmBulkCopy	void		达梦 特有的功能，执行 BulkCopy 批量导入数据，引入 FreeSql.Provider.Dameng

Basic

• 入门 Getting Started
• 安装 How to Install
• 添加 Insert Data
  • 单条 Single
  • 批量 Batch
• 删除 Delete Data
• 修改 Update Data
  • 更新实体 Entity
  • 更新指定列 Specified Columns
• 添加或修改 Insert or Update ✨
• 查询 Query Data
  • 分页 Pagination
  • 单表 Single Table
  • 多表 Multi Tables
  • 分组聚合 Group Aggregation
  • 嵌套查询 Nested Query ✨
  • 联合查询
  • 返回数据 Return Data ✨
  • 延时加载 Lazy Loading
  • 贪婪加载 Greed Loading ✨
  • LINQ 扩展 LinqToSql
  • 树型查询 Parent-Child Relp. ✨
• 仓储层 Repository Layer
  • 工作单元 Unit of Work
  • 联级保存 Cascade Saving
  • 联级删除 Cascade Deletion
  • 工作单元管理器 ✨
  • 聚合根（实验室）
• CodeFirst
  • 实体特性✨
  • FluentApi
  • 自定义特性
  • 类型映射
  • 导航属性 Entity Relationship ✨
  • 迁移结构
• DbFirst
• 表达式函数
  • 字符串
  • 日期/时间
  • 其他
  • 自定义函数
• 事务
• 过滤器
• ADO
• AOP✨
• 读写分离
• 分表分库
• 租户
• 性能
• 动态操作 Dynamic Operations
• 你不知道的功能✨
• API参考

使用指南

• Dapper比较
• EntityFramework比较

学习与交流

• 常见问题
• 提交Issues

信息

• 服务支持
• 更新日志