分享MSSQL、MySql、Oracle的大数据批量导入
mhr18 2024-09-13 20:40 37 浏览 0 评论
1：MSSQL
SQL语法篇：
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW = first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)]
SQL示例：
bulk insert 表名 from 'D:\mydata.txt'
with
(fieldterminator=',',
rowterminator='\n',
check_constraints)
select * from 表名
由于C#提供了SqlBulkCopy，所以非DBA的我们，更多会通过程序来调用：
C#代码篇：
C#代码调用示例及细节，以下代码摘录自CYQ.Data：
using (SqlBulkCopy sbc = new SqlBulkCopy(con, (keepID ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.FireTriggers, sqlTran))
{
sbc.BatchSize = 100000;
sbc.DestinationTableName = SqlFormat.Keyword(mdt.TableName, DalType.MsSql);
sbc.BulkCopyTimeout = AppConfig.DB.CommandTimeout; foreach (MCellStruct column in mdt.Columns)
{
sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
sbc.WriteToServer(mdt);
}
有5个细节：
1：事务：
如果只是单个事务，构造函数可以是链接字符串。
如果需要和外部合成一个事务（比如先删除，再插入，这在同一个事务中）
就需要自己构造Connection对象和Transaction，在上下文中传递来处理。
2：插入是否引发触发器
通过SqlBulkCopyOptions.FireTriggers 引入
3：其它：批量数、超时时间、是否写入主键ID。
可能引发的数据库Down机的情况：
在历史的过程中，我遇到过的一个大坑是：
当数据的长度过长，数据的字段过短，产生数据二进制截断时，数据库服务竟然停掉了（也许是特例，也许不是）。
所以小心使用，尽力做好对外部数据做好数据长度验证。
2：MySql
关于MySql的批量，这是一段悲催的往事，有几个坑，直到今天，才发现并解决了。
SQL语法篇：
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'data.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...)]
示例篇：
LOAD DATA LOCAL INFILE 'C:\\Users\\cyq\\AppData\\Local\\Temp\\BulkCopy.csv' INTO TABLE `BulkCopy` CHARACTER SET utf8 FIELDS TERMINATED BY '$,