多次插入mysql [英] multiple insert into mysql

问题描述

我有2个字符串数组和1个大小为100的整数数组.我需要将它们(100行)插入表中.

Hi,

I''m having 2 string array and 1 integer array of size 100. I need to insert them (100 rows) into table.

query= insert into storage (box_no,item_name,amount) values x,y,z

for(i=0;i<100;i++)

x=arr1[i]
y=arr2[i]
z=arr3[i]

我如何优化查询，可能正在使用command.parameter.addwithvalue

谢谢

注意:不确定问题是否与MySQL有关(有标签)，因为原始问题是msql(可能是mssql的错字).

How can I optimize the query, may be using command.parameter.addwithvalue

Thanks

Note: Not sure if the question is regarding MySQL (Tags are there), because original question was for msql (maybe a typo for mssql).

推荐答案

我假设您正在使用MySQL，因为在您最初的问题中您没有提到MySQL.但是我看到标记已添加为MySQL和VB.NET

如果您只想在循环中插入100行，那么在进行优化时，您不会发现太大的差异.但是，如果要批量插入很多行(超过10,000条)，最好的方法是创建数据适配器，然后填充数据集，在数据集上设置与实际表相同的主键，然后将行添加到数据集中(在内存)，然后启动datadapter.update()并传递数据集.这样，数据适配器将最有效地插入记录(与要批量更新时相同).

但是在您的情况下，您不必担心性能，因为它只有100行(您不会看到毫秒级的改进).但您应通过将值与参数一起传递来避免任何sql注入攻击.为了安全起见，请不要使用内联sql.

(例如

I am assuming that you are using MySQL, because in your original question you didnt mentioned MySQL. But I see tags added as MySQL and VB.NET

if there are only 100 rows you want to insert in a loop, you will not see much of a difference when you optimize. but if you want to insert lot of rows (more than 10,000) in bulk, the best way is to create a data adapter then fill a dataset, set the primary key on dataset same as actual table, then add rows into the dataset (in memory), and then fire datadapter.update() and pass the dataset. this way the data adapter will insert the records most effeciently (same when you want to update in bulk).

but in your case you should not worry too much about the performance as it''s only 100 rows (you will not see that milisecond improvement). but you should avoid any sql injection attacks by passing the values with parameter. for security never use inline sql.

(like

string strQuery = "Select * From Users Where Password = '" + strPassword + "' And UserName = '" + strUserName + "'

).

始终使用参数(或调用存储过程)

").

always use parameter, (or call stored procedure)

Dim da As MySqlDataAdapter = New MySqlDataAdapter()
Dim cmd As MySqlCommand
Dim parm As MySqlParameter
' Create the SelectCommand.
cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
da.SelectCommand = cmd
' Create the InsertCommand. put this in a loop
cmd = New MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id, ?name)", conn)
cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" )
cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" )
da.InsertCommand = cmd

您可以在此链接上找到有关数据适配器的更多信息 [ ^ ].

you can find more information about data adapter on this link[^].

这篇关于多次插入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！